In this tutorial let us look into how to use Join Query in EF Core to load 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 EF Core by using the join operator & DefaultIfEmpty method. Also left join with where clause.
Database:
The Database for this tutorial is taken from the chinook database.Source Code:
The source code of this project available in GitHub. It also contains the script of the database
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 from our last tutorial. 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.
Table of Contents
Using EF Core Join
Query Syntax
The joins Queries are easier with the Query Syntax.
The following query joins Track and MediaType table using the Join query operator. The Join operator uses the Equals Keyword to compare the two or more columns. In this example, we use the column MediaTypeId. The query looks very similar to the normal database SQL Join Queries.
The query begins with outer table i.e Track. We are using o as range variable, you can also use the name of table also.
from o in db.TrackUse the join operator to join the inner table.
join i in db.MediaTypeMention the condition on which you want to join them. Note that we use the equals & not == or =. Also we can only compare for equality. Other comparison are not yet supported.
on o.MediaTypeId equals i.MediaTypeIdFinally to select the columns we make use of the projection queries. You can select the properties using the range variable.
private void joinTwoTablesExample1()
{
//Query Syntax
using (ChinookContext db = new ChinookContext())
{
var Track = (from o in db.Track
join i in db.MediaType
on o.MediaTypeId equals i.MediaTypeId
select new
{
Name = o.Name,
Composer = o.Composer,
MediaType = i.Name
}).Take(5);
foreach (var t in Track)
{
Console.WriteLine("{0} {1} {2}", t.Name, t.Composer, t.MediaType);
}
}
Console.WriteLine("Press any key to continue");
Console.ReadKey();
}The above query translates into the following SQL Query. You will notice that it creates an SQL INNER JOIN.
//SQL Query
SELECT TOP(@__p_0) [t].[Name], [t].[Composer], [m].[Name] AS [MediaType]
FROM [Track] AS [t]
INNER JOIN [MediaType] AS [m] ON [t].[MediaTypeId] = [m].[MediaTypeId]Method Syntax
The method query syntax uses the join method. join method is an extension method, which takes the following syntax.
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
)IEnumerable<TOuter> outer
The first sequence to join. In our case it is Track table.
var Track = db.TrackIEnumerable<TInner> inner
The sequence to join to the first sequence. Here we use MediaType table, which we are going to join to the Track table.
.Join(db.MediaType,Func<TOuter, TKey> outerKeySelector
A function to extract the join key from the first sequence (Track table). Here we use a lambda expression l => l.MediaTypeId. We use the MediaTypeId of the Track table is to join the two tables
o => o.MediaTypeId,Func<TInner, TKey> innerKeySelector
A function to extract the join key from the second sequence (MediaType table). This is similar to the outerKeySelector, but the specify the key to be used from the second table. In our case is MediaTypeId field from MediaTypetable.
i => i.MediaTypeId,Func<TOuter, TInner, TResult>
A function to create a result element from two matching elements. Here the two matching elements are TOuter which is our Track table ( o) & TInner which is MediaType table (i).
We use the projection to an anonymous type to return the result.
(o, i) => new
{
Name = o.Name,
Composer = o.Composer,
MediaType = i.Name
}The final query is as follows
private void joinTwoTablesExample1()
{
//Method Syntax
using (ChinookContext db = new ChinookContext())
{
var Track = db.Track
.Join(db.MediaType,
o => o.MediaTypeId,
i => i.MediaTypeId,
(o, i) =>
new
{
Name = o.Name,
Composer = o.Composer,
MediaType = i.Name
}
).Take(5);
foreach (var t in Track)
{
Console.WriteLine("{0} {1} {2}", t.Name, t.Composer, t.MediaType);
}
}
Console.WriteLine("Press any key to continue");
Console.ReadKey();
}
//SQL Query
SELECT TOP(@__p_0) [t].[Name], [t].[Composer], [m].[Name] AS [MediaType]
FROM [Track] AS [t]
INNER JOIN [MediaType] AS [m] ON [t].[MediaTypeId] = [m].[MediaTypeId]Here is an another example, where we use the join clause to join customer & employee table. Both in method & query syntax.
private void joinTwoTablesExample2()
{
//Method Syntax
using (ChinookContext db = new ChinookContext())
{
var Track = db.Customer
.Join(db.Employee,
f => f.SupportRepId,
s => s.EmployeeId,
(f, s) =>
new
{
CustomerName = f.FirstName,
CustomerState = f.State,
EmployeeName = s.FirstName,
EmployeeState = s.State,
}
).Take(5);
foreach (var t in Track)
{
Console.WriteLine("{0} {1} {2} {3}", t.CustomerName, t.CustomerState, t.EmployeeName, t.EmployeeState);
}
}
Console.WriteLine("Press any key to continue");
Console.ReadKey();
//Query Syntax
using (ChinookContext db = new ChinookContext())
{
var Track = (from f in db.Customer
join s in db.Employee
on f.SupportRepId equals s.EmployeeId
select new
{
CustomerName = f.FirstName,
CustomerState = f.State,
EmployeeName = s.FirstName,
EmployeeState = s.State,
}).Take(5);
foreach (var t in Track)
{
Console.WriteLine("{0} {1} {2} {3}", t.CustomerName, t.CustomerState, t.EmployeeName, t.EmployeeState);
}
}
Console.WriteLine("Press any key to continue");
Console.ReadKey();
} The SQL Query of the above method
//SELECT TOP(@__p_0) [c].[FirstName] AS[CustomerName], [c].[State] AS[CustomerState], [e].[FirstName] AS[EmployeeName], [e].[State]
//AS[EmployeeState]
//FROM[Customer] AS[c]
//INNER JOIN[Employee] AS[e] ON[c].[SupportRepId] = [e].[EmployeeId]LINQ Join on Multiple Columns
To join two tables on more than one column (join by using composite keys), we need to define an anonymous type with the values we want to compare
Query Syntax
For Example in query example inst
var result = (from m1 in db.model1
join m2 in db.model2
on new { m1.field1 , m1.field2 }
equals new { m2.field1 , m2.field2 }
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
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
on new { p1=m1.field1 , p2=m1.field2 } equals new {p1=m2.fld1, p2=m2.fld2 }Example (Query Syntax)
var Track = db.Customer
.Join(db.Employee,
f => new { f1 = f.SupportRepId.Value, f2 = f.State },
s => new { f1 = s.EmployeeId, f2 = s.State },
(f, s) =>
new
{
CustomerName = f.FirstName,
CustomerState = f.State,
EmployeeName = s.FirstName,
EmployeeState = s.State,
}
).Take(5);Example Method Syntax
var Track = (from f in db.Customer
join s in db.Employee
on new { f1 = f.SupportRepId.Value, f2 = f.State } equals new { f1 = s.EmployeeId, f2 = s.State }
select new
{
CustomerName = f.FirstName,
CustomerState = f.State,
EmployeeName = s.FirstName,
EmployeeState = s.State,
}).Take(5);Note that we use f.SupportRepId.Value instead of f.SupportRepId. i.e because data type of SupportRepId is int? (Nullable int). While that of EmployeeId is int. The join fails if the data type does not match. Hence we use f.SupportRepId.Value to convert int? to int
Joining three or more Tables
The following queries demonstrate the use of Join queries between three or more tables. The query below queries for all invoices of track Bohemian Rhapsody with their qty & amount. This query involves joining three tables Track, InvoiceLine & Invoice
Query Syntax
private void joinThreeTablesQuerySyntax()
{
using (ChinookContext db = new ChinookContext())
{
var Track = (from t in db.Track
join il in db.InvoiceLine
on t.TrackId equals il.TrackId
join i in db.Invoice
on il.InvoiceId equals i.InvoiceId
where t.Name == "Bohemian Rhapsody"
select (new
{
TrackName = t.Name,
TrackId = t.TrackId,
InvoiceId = i.InvoiceId,
InvoiceDate = i.InvoiceDate,
Quantity = il.Quantity,
UnitPrice = il.UnitPrice
})
).ToList();
foreach (var r in Track)
{
Console.WriteLine("{0} {1} {2} {3}", r.TrackName, r.InvoiceDate, r.Quantity, r.UnitPrice);
}
}
}SELECT[t].[Name] AS[TrackName], [t].[TrackId], [i0].[InvoiceId], [i0].[InvoiceDate], [i].[Quantity], [i].[UnitPrice]
FROM[Track] AS[t]
INNER JOIN[InvoiceLine] AS[i] ON[t].[TrackId] = [i].[TrackId]
INNER JOIN[Invoice] AS[i0] ON[i].[InvoiceId] = [i0].[InvoiceId]
WHERE[t].[Name] = N'Bohemian Rhapsody'Method Syntax
The method syntax achieves this by chaining the join method.
The first join joins the outer table Tack with inner table InvoiceLine on TrackId and uses the projection to create an anonymous object.
For the second join, the previous result (anonymous object) acts as the outer table. The inner table is Invoice. Use the projection again to create another anonymous object.
You can keep repeat the join again for more tables. Finally use the ToList method execute the query.
private void joinThreeTablesMethodSyntax()
{
//Method Syntax
using (ChinookContext db = new ChinookContext())
{
var Track = db.Track
.Join(db.InvoiceLine,
f => f.TrackId, s => s.TrackId,
(f, s) => new { TrackName = f.Name, TrackId = f.TrackId, InvoiceId = s.InvoiceId, Quantity = s.Quantity, UnitPrice = s.UnitPrice }
)
.Join(db.Invoice,
f => f.InvoiceId, s => s.InvoiceId,
(f, s) => new { TrackName = f.TrackName, TrackId = f.TrackId, InvoiceId = f.InvoiceId, InvoiceDate = s.InvoiceDate, Quantity = f.Quantity, UnitPrice = f.UnitPrice }
).Where(r => r.TrackName == "Bohemian Rhapsody")
.ToList();
foreach (var r in Track)
{
Console.WriteLine("{0} {1} {2} {3}", r.TrackName, r.InvoiceDate, r.Quantity, r.UnitPrice);
}
}
}The SQL Query is as follows
SELECT[t].[Name] AS[TrackName], [t].[TrackId], [i].[InvoiceId], [i0].[InvoiceDate], [i].[Quantity], [i].[UnitPrice]
FROM[Track] AS[t]
INNER JOIN[InvoiceLine] AS[i] ON[t].[TrackId] = [i].[TrackId]
INNER JOIN[Invoice] AS[i0] ON[i].[InvoiceId] = [i0].[InvoiceId]
WHERE[t].[Name] = N'Bohemian Rhapsody'The query can also be written as shown below. Notice the difference in projection of the first join and this one.
var Track = db.Track
.Join(db.InvoiceLine,
f => f.TrackId, s => s.TrackId,
(Track, InvoiceLine) => new { Track, InvoiceLine } //Projecting entire row from both tables
)
.Join(db.Invoice,
f => f.InvoiceLine.InvoiceId, s => s.InvoiceId,
(f, s) => new { TrackName = f.Track.Name, TrackId = f.Track.TrackId, InvoiceId = f.InvoiceLine.InvoiceId, InvoiceDate = s.InvoiceDate, Quantity = f.InvoiceLine.Quantity, UnitPrice = f.InvoiceLine.UnitPrice }
)
.Where(r => r.TrackName == "Bohemian Rhapsody")
.ToList();Left Join
The EF Core converts to above joins into an INNER JOIN. But the other most used join is a SQL left join. To use a left join we use the methodDefaultIfEmpty in the Query Syntax.
To implement left join, first we start of with a normal join of two tables. Use the into j1 to push the results of this join into a temp variable j1
var model = (from t in db.Track
join il in db.InvoiceLine on t.TrackId equals il.TrackId into j1Now assume j1 is another table and start another join as shown below. Here make use of the method DefaultIfEmpty, which instructs the EF Core to use Left Join. Use the into j2 to push the results of this join into a temp variable j2
from j in j1.DefaultIfEmpty()
join i in db.Invoice on j.InvoiceId equals i.InvoiceId into j2You can continue with this for more joins.
Finally in the last from statement, use the projection to select the properties of the output. Remember that range variables from join clause (i.e il & i) will not be available. Only the range variable in the from clause are available i.e (t,j,r)
public void joinLeftQuerySyntaxExample1()
{
//Console.WriteLine("******************* Query Syntax ******************* ");
using (ChinookContext db = new ChinookContext())
{
var model = (from t in db.Track
join il in db.InvoiceLine on t.TrackId equals il.TrackId into j1
from j in j1.DefaultIfEmpty()
join i in db.Invoice on j.InvoiceId equals i.InvoiceId into j2
from r in j2.DefaultIfEmpty()
select new
{
TrackName = t.Name,
TrackId = t.TrackId,
InvoiceId = r.InvoiceId,
InvoiceDate = r.InvoiceDate,
Quantity = j.Quantity,
UnitPrice = j.UnitPrice
})
.Where(r => r.TrackName == "Bohemian Rhapsody")
.ToList();
foreach (var item in model)
{
Console.WriteLine("{0} {1} {2} {3}", item.TrackName, item.InvoiceDate, item.Quantity, item.UnitPrice);
}
}
Console.WriteLine("Press any key to continue /Query Syntax 1");
Console.ReadKey();
}The above code results in the following SQL statement.
SELECT [t].[Name] AS [TrackName], [t].[TrackId], [i0].[InvoiceId], [i0].[InvoiceDate], [i].[Quantity], [i].[UnitPrice]
FROM [Track] AS [t]
LEFT JOIN [InvoiceLine] AS [i] ON [t].[TrackId] = [i].[TrackId]
LEFT JOIN [Invoice] AS [i0] ON [i].[InvoiceId] = [i0].[InvoiceId]
WHERE [t].[Name] = N'Bohemian Rhapsody'Note that if you omit DefaultIfEmpty, then the SQL Server will perform an inner join. You can make use of it to create a query with left and inner joins
Using Where Clause
You can also make use of the following query, where we have use the join condition in where clause and used DefaultIfEmpty , But you must check the final query before using it.
using (ChinookContext db = new ChinookContext())
{
var model = (from t in db.Track
from il in db.InvoiceLine.Where(il => il.TrackId == t.TrackId).DefaultIfEmpty()
from i in db.Invoice.Where(i => i.InvoiceId == il.InvoiceId).DefaultIfEmpty()
select new
{
TrackName = t.Name,
TrackId = t.TrackId,
InvoiceId = i.InvoiceId,
InvoiceDate = i.InvoiceDate,
Quantity = il.Quantity,
UnitPrice = il.UnitPrice
})
.Where(r => r.TrackName == "Bohemian Rhapsody")
.ToList();
foreach (var item in model)
{
Console.WriteLine("{0} {1} {2} {3}", item.TrackName, item.InvoiceDate, item.Quantity, item.UnitPrice);
}
}References
Summary
Performing joins is one of the common tasks you perform. In EF Core we use the join operator to achieve this. The join performs an inner join. You can also perform left join DefaultIfEmpty
Thank you very much!
Hey thank you so much for taking your time and posting this. I needed to get some syntax right and your post really helped.
Legend!
Not cool. The examples are too simple. For instance, I have issue with creating Linq query between several tables with nested projections and collections… And I cannot find proper example anywhere to figure out if it is the bug of EfCore5 or something wrong with my code.
Thanks!! and this query..
SELECT ..
FROM table_a A
INNER JOIN table_b B ON A.key_B=B.key_B
LEFT JOIN table_c C ON B.key_C=C.key_C
Thanks. You could have added method syntax for left join also