Lambda Expressions and Inner Joins

Consider Northwind database represented by Entity Framework:


Notice that to get an employee’s region, you have to traverse the territory table. In TSQL, you would probably write something like this:

Select * from Employees as e 
inner join EmployeeTerritories as et on e.EmployeeID = et.EmployeeID 
inner join Territories as t on et.TerritoryID = t.TerritoryID 
inner join Region as r on t.RegionID = r.RegionID 
where r.RegionDescription = 'Eastern' 

(Note that EntityFramework does not show the EmployeeTerritory crosswalk table)

Using Lambdas, you have to get to region via territories, but that property is not available from the employee class:


Enter the "Any" keyword – you can chain classes using the "Any" keyword just like you are doing inner joins in TSQL (or join in Linq):

For example, all employees in the eastern region is written like so:

var result = entities.Employees.Where(e => e.Territories.Any(t => t.Region.RegionDescription == "Eastern")); 

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: