Join Query In Entity Framework

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

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

SQL Query

Method Syntax

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

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.

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.

The final query is as shown below

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

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

Method Syntax

The Method Syntax is as follows

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

SQL Query

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.

Now the above projected result will become outer table for the next join. i,e SalesPersons.

Repeat it for all the other joins.

SQL Query

The same query, but we are projecting all the fields, which changes the shape of the projection.

Join Using Navigational Property

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.

The above code creates the following SQL Query. The EF efficiently decides INNER JOIN & LEFT JOIN based the navigational property.

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

And start a new query from the j1 and add DefaultIfEmpty. If you omit this SQL will perform an inner join

Here is the complete query.

SQL Query

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.

7 thoughts on “Join Query In Entity Framework”

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

  2. 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]

  3. 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?

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

Leave a Comment

Your email address will not be published. Required fields are marked *

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

Scroll to Top