EF Core Join Query

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.

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.

Use the join operator to join the inner table.

Mention 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.

Finally to select the columns we make use of the projection queries. You can select the properties using the range variable.

Best Entity Framework Core Books
The Best EF Core Books, which helps you to get started with EF Core  

The above query translates into the following SQL Query. You will notice that it creates an SQL INNER JOIN.

Method Syntax

The method query syntax uses the join method. join method is an extension method, which takes the following syntax.

IEnumerable<TOuter> outer

The first sequence to join. In our case it is Track table.

IEnumerable<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.

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

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.

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.

The final query is as follows

Here is an another example, where we use the join clause to join customer & employee table. Both in method & query syntax.

The SQL Query of the above method

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

In the above we are creating the anonymous type to compare the fields

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

Example (Query Syntax)

Example Method Syntax

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

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.

The SQL Query is as follows

The query can also be written as shown below. Notice the difference in projection of the first join and this one.

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

Now 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

You 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)

The above code results in the following SQL statement.

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.

References

  1. DefaultIfEmpty

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

Leave a Comment

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Scroll to Top