In this tutorial let us look into how to use Join Query in Entity Framework to load the data from two, three or more tables. The LINQ join operator allows us to join multiple tables on one or more columns (multiple columns). By default, they perform the inner join of the tables. We also learn how to perform left joins in Entity Framework by using the join operator & DefaultIfEmpty method.
It is always advisable to use navigational properties to query the related data. You can refer to the article Querying Related data using Navigational Properties on how to do it. You should use the Join Query operators only if the tables do not have any navigational properties defined on them or you want to fine-tune the generated queries for performance benefits.
Click here If you are looking for join query in EF Core
We continue to use the AdventureWorks Database for our model. If you missed the previous tutorials you can go through those from the following the LINQ to Entities tutorial
Table of Contents
Using Join
The following query joins Person and EmailAddresses table using the join Query operator. The Join operator uses the Equals Keyword to compare the specified properties
The query begins with from p in db.People which is the outer table in our join.
We then use the join keyword to join the inner table. (join e in db.EmailAddresses)
The on keyword is used to specify the join condition. The first part of the condition should always from the outer table (i.e People). We use the equals keyword to compare the conditions. Also, we can only compare for equality. Other comparisons are not supported.
Finally to select the columns we make use of the projection queries in Entity Framework. You can select the properties using the range variable (p & e)
Query Syntax
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 | using (AdventureWorks db = new AdventureWorks()) { var person = (from p in db.People join e in db.EmailAddresses on p.BusinessEntityID equals e.BusinessEntityID where p.FirstName == "KEN" select new { ID = p.BusinessEntityID, FirstName = p.FirstName, MiddleName = p.MiddleName, LastName = p.LastName, EmailID = e.EmailAddress1 }).ToList(); foreach (var p in person) { Console.WriteLine("{0} {1} {2} {3} {4}", p.ID, p.FirstName, p.MiddleName, p.LastName, p.EmailID); } } |
SQL Query
1 2 3 4 5 6 7 8 9 10 11 | SELECT [Extent1].[BusinessEntityID] AS [BusinessEntityID], [Extent1].[FirstName] AS [FirstName], [Extent1].[MiddleName] AS [MiddleName], [Extent1].[LastName] AS [LastName], [Extent2].[EmailAddress] AS [EmailAddress] FROM [Person].[Person] AS [Extent1] INNER JOIN [Person].[EmailAddress] AS [Extent2] ON [Extent1].[BusinessEntityID] = [Extent2].[BusinessEntityID] WHERE N'KEN' = [Extent1].[FirstName] |
Method Syntax
The method query syntax uses the Join method. Join method is an extension method, which takes the following syntax.
1 2 3 4 5 6 7 8 9 | public static IEnumerable<TResult> Join<TOuter, TInner, TKey, TResult>( this IEnumerable<TOuter> outer, IEnumerable<TInner> inner, Func<TOuter, TKey> outerKeySelector, Func<TInner, TKey> innerKeySelector, Func<TOuter, TInner, TResult> resultSelector ) |
First start with the outer table db.People
Next, use the join extension method and specify the inner table as the first argument to the join method. .Join(db.EmailAddresses,
The next two arguments specify the condition on which you want to join the tables. The first object is from the outer table and the second argument from the inner table. Both use lambda expression. We use the BusinessEntityID to join the tables. Note that the data type of the both properties must match.
1 2 3 4 | p => p.BusinessEntityID, e => e.BusinessEntityID, |
Finally, we need to select how the final result looks like. The resultSelector takes the two arguments, The outer table (person) as the argument & the inner table (EmailAddresses) as the second argument. Using them you can project the result to a new anonymous type and return it.
1 2 3 4 5 6 7 8 | (p, e) => new { FirstName = p.FirstName, MiddleName = p.MiddleName, LastName = p.LastName, EmailID = e.EmailAddress1 } |
The final query is as shown below
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 | using (AdventureWorks db = new AdventureWorks()) { var person = db.People .Join(db.EmailAddresses, p => p.BusinessEntityID, e => e.BusinessEntityID, (p, e) => new { FirstName = p.FirstName, MiddleName = p.MiddleName, LastName = p.LastName, EmailID = e.EmailAddress1 } ).Take(5); foreach (var p in person) { Console.WriteLine("{0} {1} {2} Email ID : {3}", p.FirstName, p.MiddleName, p.LastName, p.EmailID); } } |
LINQ Join on Multiple Columns
To use join on more than one columns (Join by using composite keys), we need to define an anonymous type with the values we want to compare
Query Syntax
1 2 3 4 5 6 7 8 9 10 11 12 13 | var result = (from m1 in db.model1 join m2 in db.model2 on new { m1.field1 , m1.field2 } equals new { m2.field1, m2.field2 } where m1.FirstName == "KEN" select new { field1 = m1.field1, field2 = m1.field2, someField = m2.someField }).ToList(); |
In the above we are creating the anonymous type to compare the fields
1 2 3 | on new { m1.field1 , m1.field2 } equals new {m2.field1, m2.field2 } |
The above works only if the data types and the names of the properties in the anonymous types match
If the property names do not match, then you should name the properties of the anonymous type as shown below
1 2 3 | on new { p1=m1.field1 , p2=m1.field2 } equals new {p1=m2.fld1, p2=m2.fld2 } |
Method Syntax
The Method Syntax is as follows
1 2 3 4 5 6 7 8 9 10 11 12 | var result = db.Model1 //Outer Table .Join(db.Model2, //Inner Table to join p => new { p1 = p.Field1, p2 = p.Field2 } , //Condition from outer table e => new { p1 = e.Fld1, p2 = e.Fld2 }, //Condition from inner table (p, e) => new { //Result Field1 = p.Fld1, Field2 = p.Fld2, someField = e.someField } ).ToList(); |
Joining more than one Table
The following queries demonstrate the use of Join queries between multiple tables. The query below queries for all Employees, who are salesPersons and belonging to the Region with code CA.
Query Syntax
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 | using (AdventureWorks db = new AdventureWorks()) { var person = (from e in db.Employees join p in db.People on e.BusinessEntityID equals p.BusinessEntityID join s in db.SalesPersons on e.BusinessEntityID equals s.BusinessEntityID join t in db.SalesTerritories on s.TerritoryID equals t.TerritoryID where t.CountryRegionCode == "CA" select new { ID = e.BusinessEntityID, FirstName = p.FirstName, MiddleName = p.MiddleName, LastName = p.LastName, Region = t.CountryRegionCode }).ToList(); foreach (var p in person) { Console.WriteLine("{0} {1} {2} {3} {4}", p.ID, p.FirstName, p.MiddleName, p.LastName, p.Region); } } |
SQL Query
1 2 3 4 5 6 7 8 9 10 11 12 | SELECT [Extent1].[BusinessEntityID] AS [BusinessEntityID], [Extent2].[FirstName] AS [FirstName], [Extent2].[MiddleName] AS [MiddleName], [Extent2].[LastName] AS [LastName], [Extent3].[CountryRegionCode] AS [CountryRegionCode] FROM [Sales].[SalesPerson] AS [Extent1] INNER JOIN [Person].[Person] AS [Extent2] ON [Extent1].[BusinessEntityID] = [Extent2].[BusinessEntityID] INNER JOIN [Sales].[SalesTerritory] AS [Extent3] ON [Extent1].[TerritoryID] = [Extent3].[TerritoryID] WHERE N'CA' = [Extent3].[CountryRegionCode] |
Method Syntax
The method syntax achieves this by chaining the join method to the result of the previous join method.
First join the first two tables. Employees is the outer table and People is the inner table. Project the properties you want to in the output. Also include those properties, which you want to use in the join condition further down the query.
1 2 3 4 5 6 7 8 9 10 11 12 13 | db.Employees .Join(db.People, emp => emp.BusinessEntityID, per => per.BusinessEntityID, (emp, per) => new { emp.BusinessEntityID, per.FirstName, per.MiddleName, per.LastName }) |
Now the above projected result will become outer table for the next join. i,e SalesPersons.
1 2 3 4 5 6 7 8 9 10 11 | .Join(db.SalesPersons, o => o.BusinessEntityID, sal => sal.BusinessEntityID, (o, sal) => new { o.BusinessEntityID, o.FirstName, o.MiddleName, o.LastName, sal.TerritoryID }) |
Repeat it for all the other joins.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 | using (AdventureWorks db = new AdventureWorks()) { db.Database.Log = Console.Write; var person = db.Employees .Join(db.People, emp => emp.BusinessEntityID, per => per.BusinessEntityID, (emp, per) => new { emp.BusinessEntityID, per.FirstName, per.MiddleName, per.LastName }) .Join(db.SalesPersons, o => o.BusinessEntityID, sal => sal.BusinessEntityID, (o, sal) => new { o.BusinessEntityID, o.FirstName, o.MiddleName, o.LastName, sal.TerritoryID }) .Join(db.SalesTerritories, o => o.TerritoryID, ter => ter.TerritoryID, (o, ter) => new { o.BusinessEntityID, o.FirstName, o.MiddleName, o.LastName, o.TerritoryID, ter.CountryRegionCode }) .Where(r => r.CountryRegionCode == "CA") .Select(r => new { ID = r.BusinessEntityID, FirstName = r.FirstName, MiddleName = r.MiddleName, LastName = r.LastName, Region = r.CountryRegionCode }).ToList(); foreach (var p in person) { Console.WriteLine("{0} {1} {2} {3} {4}", p.ID, p.FirstName, p.MiddleName, p.LastName, p.Region); } } |
SQL Query
1 2 3 4 5 6 7 8 9 10 11 12 13 | SELECT [Extent1].[BusinessEntityID] AS [BusinessEntityID], [Extent2].[BusinessEntityID] AS [BusinessEntityID1], [Extent2].[FirstName] AS [FirstName], [Extent2].[MiddleName] AS [MiddleName], [Extent2].[LastName] AS [LastName], [Extent3].[CountryRegionCode] AS [CountryRegionCode] FROM [Sales].[SalesPerson] AS [Extent1] INNER JOIN [Person].[Person] AS [Extent2] ON [Extent1].[BusinessEntityID] = [Extent2].[BusinessEntityID] INNER JOIN [Sales].[SalesTerritory] AS [Extent3] ON [Extent1].[TerritoryID] = [Extent3].[TerritoryID] WHERE N'CA' = [Extent3].[CountryRegionCode] |
The same query, but we are projecting all the fields, which changes the shape of the projection.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 | using (AdventureWorks db = new AdventureWorks()) { var person = db.Employees .Join(db.People, emp=> emp.BusinessEntityID, per=> per.BusinessEntityID, (emp,per) => new {emp,per}) //Projecting all the fields .Join(db.SalesPersons, o => o.emp.BusinessEntityID, sal=> sal.BusinessEntityID , (emp1,sal) => new {emp1,sal}) .Join(db.SalesTerritories, o=> o.sal.TerritoryID, ter=>ter.TerritoryID, (emp2,ter) => new {emp2,ter}) .Where(z => z.ter.CountryRegionCode=="CA") .Select(z => new { ID = z.emp2.emp1.per.BusinessEntityID, FirstName=z.emp2.emp1.per.FirstName, MiddleName=z.emp2.emp1.per.MiddleName, LastName=z.emp2.emp1.per.LastName, Region=z.ter.CountryRegionCode }).ToList(); foreach (var p in person) { Console.WriteLine("{0} {1} {2} {3} {4}", p.ID, p.FirstName, p.MiddleName, p.LastName, p.Region); } } |
At the beginning of the tutorial, we said that most of the join methods can be performed using the Navigation property. The Following Query retrieves the same result as the queries above. Note that the query is very simple and uses the navigational property to retrieve the data.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 | using (AdventureWorks db = new AdventureWorks()) { var person = (from p in db.Employees where p.SalesPerson.SalesTerritory.CountryRegionCode == "CA" select new { ID = p.BusinessEntityID, FirstName = p.Person.FirstName, MiddleName = p.Person.MiddleName, LastName = p.Person.LastName, Region = p.SalesPerson.SalesTerritory.CountryRegionCode }).ToList(); foreach (var p in person) { Console.WriteLine("{0} {1} {2} {3} {4}", p.ID, p.FirstName, p.MiddleName, p.LastName, p.Region); } } |
The above code creates the following SQL Query. The EF efficiently decides INNER JOIN & LEFT JOIN based the navigational property.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | SELECT [Extent1].[BusinessEntityID] AS [BusinessEntityID], [Extent4].[FirstName] AS [FirstName], [Extent4].[MiddleName] AS [MiddleName], [Extent4].[LastName] AS [LastName], [Extent6].[CountryRegionCode] AS [CountryRegionCode] FROM [HumanResources].[Employee] AS [Extent1] LEFT OUTER JOIN [Sales].[SalesPerson] AS [Extent2] ON [Extent1].[BusinessEntityID] = [Extent2].[BusinessEntityID] INNER JOIN [Sales].[SalesTerritory] AS [Extent3] ON [Extent2].[TerritoryID] = [Extent3].[TerritoryID] INNER JOIN [Person].[Person] AS [Extent4] ON [Extent1].[BusinessEntityID] = [Extent4].[BusinessEntityID] LEFT OUTER JOIN [Sales].[SalesPerson] AS [Extent5] ON [Extent1].[BusinessEntityID] = [Extent5].[BusinessEntityID] LEFT OUTER JOIN [Sales].[SalesTerritory] AS [Extent6] ON [Extent5].[TerritoryID] = [Extent6].[TerritoryID] WHERE N'CA' = [Extent3].[CountryRegionCode] |
Left Join
The EF usually does an inner join when we join two tables. To convert it into Left join use the into clause to create variable to hold the result and use the DefaultIfEmpty method as shown below
1 2 3 | on d.BusinessEntityID equals e.BusinessEntityID into j1 |
And start a new query from the j1 and add DefaultIfEmpty. If you omit this SQL will perform an inner join
1 2 3 | from r in j1.DefaultIfEmpty() |
Here is the complete query.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 | using (AdventureWorks db = new AdventureWorks()) { db.Database.Log = Console.Write; var person = (from d in db.People join e in db.EmailAddresses on d.BusinessEntityID equals e.BusinessEntityID into j1 from r in j1.DefaultIfEmpty() select new { FirstName = d.FirstName, MiddleName = d.MiddleName, LastName = d.LastName, Email = r.EmailAddress1 }) .Take(5) .ToList(); foreach (var p in person) { Console.WriteLine("{0} {1} {2} {3}", p.FirstName, p.MiddleName, p.LastName, p.Email); } } |
SQL Query
1 2 3 4 5 6 7 8 9 10 | SELECT TOP (5) [Extent1].[BusinessEntityID] AS [BusinessEntityID], [Extent1].[FirstName] AS [FirstName], [Extent1].[MiddleName] AS [MiddleName], [Extent1].[LastName] AS [LastName], [Extent2].[EmailAddress] AS [EmailAddress] FROM [Person].[Person] AS [Extent1] LEFT OUTER JOIN [Person].[EmailAddress] AS [Extent2] ON [Extent1].[BusinessEntityID] = [Extent2].[BusinessEntityID] |
Summary
The join query in Entity Framework performs inner join. We can use it to join multiple tables and using multiple conditions. The join condition supports only equality It does not support any other operators. We can also perform left join using the DefaultIfEmpty method along with the join. You can also perform the join using the navigation properties.
I do not understand how you can access list and its objects like this
var person = (from p in db.Employees
where p.SalesPerson.SalesTerritory.CountryRegionCode == “CA”
if salePerson is a list,
and salesTerritory is also a list ??
is it for one to one relationship , what you handled???
is there any place (github etc.) where complete example we can see?
Thanks in advance.
thanks !!! it is very helpful.
For the left join , sqlserver willnot generate leftjoin. Instead call separately for Person and EmailAddress
SELECT TOP (5)
[Extent1].[BusinessEntityID] AS [BusinessEntityID],
[Extent1].[FirstName] AS [FirstName],
[Extent1].[MiddleName] AS [MiddleName],
[Extent1].[LastName] AS [LastName],
[Extent2].[EmailAddress] AS [EmailAddress]
FROM [Person].[Person] AS [Extent1]
LEFT OUTER JOIN [Person].[EmailAddress] AS [Extent2] ON [Extent1].[BusinessEntityID] = [Extent2].[BusinessEntityID]
Its Working . Thanks
This helped me a lot to understand how things workings
hi.. i am using something similar but running into a strange issue:
this is your query:
var name = form.fieldValue(“name”)
var age = form.value(“age”)
var result = (from m1 in db.model1
join m2 in db.model2
on new { m1.field1 , m1.field2 } equals new {
m2.field1, m2.field2 }
where m1.FirstName == name
select new
{
field1 = m1.field1,
field2 = m1.field2,
someField = m2.someField
}).ToList();
what if I have a search where first name is KEN and age is 38.
Or where firstname is anything but age must be 38
or firstname is ken but age is anything.
How can I achieve this?
This was a big help! I was struggling with the syntax in the select method to return fields across multiple joined tables. This made it very clear.