Lambda Expressions and Inner Joins

Consider Northwind database represented by Entity Framework:

082812_1923_LambdaExpre1

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:

082812_1923_LambdaExpre2

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")); 
Advertisements

Leave a Reply

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

WordPress.com Logo

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

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

%d bloggers like this: