Using Entity framework join (EF join) to join two or more tables

: 18811

Last Updated : 07/10/2021

Posted By :- vikas_jk

In this article, I have explained how you can implement inner join on two tables or more tables using entity framework (ef) join in ASP.NET MVC and C# linq and lambda expression. Before we proceed, in this article, I will be using AdentureWorks database, so If you want to try this at your local pc, you can download and restore AdventueWorks database.

Step 1: Let's create a new project in Visual Studio by navigating to File->New-> Project-> Select Web from left-pane and ASP.NET Web-application from right-pane -> click "OK" and then select "MVC" template, Visual studio will generate mvc template files and folders.

Step 2: Now let's create the Entity Model, right click on your projects's "Models" folder -> select "Add" -> select "Ado.NET entity model" ->name it "AdventureWorks2012" -> Select "EF designer from database" & Click Next -> Select "New Connection" from next steps and then login into your database and connect to it, click OK

Click "Next" -> Select "Tables", click "Finish", ADO.NET entity modal is added in your project and we are connect to database using ADO.NET and Entity framework.

Step 3: Suppose we want to implement Join on two tables Person and EmailAddresses table using the join Query operator. The Join operator uses the Equals Keyword to compare the specified properties.

The following query Joins the Person table with EmailAddress table on BusinessEntityID Key. The query looks very similar to the normal database SQL Join Queries.

Go to your HomeController.cs and inside your Index ActionMethod, create the join code using LINQ

public ActionResult Index()
        {
            var person = new List<Person>();
            using (AdventureWorks2012Entities db = new AdventureWorks2012Entities())
            {
                                   
                person =     (from p  in db.People // get person table as p
                              join e in db.EmailAddresses // implement join as e in EmailAddresses table
                              on p.BusinessEntityID equals e.BusinessEntityID //implement join on rows where p.BusinessEntityID == e.BusinessEntityID
                              where p.FirstName == "KEN" // now select person where people's FirstName ==KEN
                              select p).ToList();

               
            }
            return View(person);
        }

and Inside Index.cshtml code

@model IEnumerable<JoinUsingEF.Models.Person>
@{
    ViewBag.Title = "Home Page";
}
<table class="table table-condensed table-hover">
    <thead>
        <tr>
            <td>First Name</td>
            <td>Last Name</td>
            
        </tr>
    </thead>
    <tbody>
        @foreach(var per in Model)
        {
        <tr>
            <td>@per.FirstName</td>
            <td>@per.LastName</td>
            
        </tr>
        }
     </tbody>
</table>

Build and execute your project, you will get Output as below

Another way to get same results is using JOIN in Lambda expressions, Join Lambda has syntax as shown below

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 Person table. Note that join is the extension method.
IEnumerable<TInner> inner - The sequence to join to the first sequence. Here we use EmailAddresses, Which we are going to join to the Person Table.
Func<TOuter, TKey> outerKeySelector - A function to extract the join key from each element of the first sequence. Here we use lambda expression p => p.BusinessEntityID. The Key BusinessEntityID of the Person table is used to Join the two tables
Func<TInner, TKey> innerKeySelector - A function to extract the join key from each element of the second sequence. This is similar to the outerKeySelector, but the specify the key to be used from the second table. In our case is BusinessEntityID field from EmailAddresses table.
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 Person table ( p) & TInner which is Emailaddress table (e). We will select result as Person, we can also create a anonymous type for example, new { FirstName = p.FirstName, LastName = p.LastName, EmailID = e.EmailAddress1 } as the result.

Here is the C# code for it, in lambda expression considering same the same output

using (AdventureWorks2012Entities db = new AdventureWorks2012Entities())
            {
                person = db.People
                            .Join(db.EmailAddresses,
                                  p => p.BusinessEntityID,
                                  e => e.BusinessEntityID,
                                  (p, e) => p
                                  ).Take(5).ToList();
                foreach (var p in person)
                {
                    Console.WriteLine("{0} {1} {2}", p.FirstName, p.MiddleName, p.LastName);
                }
            }

Here is the image to show results while debugging

Join More than two tables using EF join

The following queries demonstrates the use of Join queries between multiple tables. The query below queries for all EmailAddresses, with salesPersons, people table, belonging to the Region with code CA.

            using (AdventureWorks2012Entities db = new AdventureWorks2012Entities())
            {
                var person = (from e in db.EmailAddresses
                              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 == "US"
                              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);
                }
            }

Here is the gif image of data while debugging the code using Visual Studio

Similarly, to do this using .Join of Lambda expressions here is the code

 using (AdventureWorks2012Entities db = new AdventureWorks2012Entities())
            {
                var person = (from e in db.EmailAddresses
                              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 == "US"
                              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);
                }
            }

Output is same as above.

So, as you can see from the above code, you can use EF join using lambda expressions, it is easier to use linq and lambda expression.

You may also like to read:

Converting String to Enum OR Enum to String in C#

Enabling CORS in IIS (Various possible methods)

Buy us a coffee

Become a Patron Q Follow our Quora space

Comment

Comment's

ravi

@model IEnumerable<JoinUsingEF.Models.Person>

the above statement it is taking only Person table columns but is not taking EmailAddresses columns how can i resolve this issue

4/23/2019 4:24:37 AM

Add Comment

vikas_jk

Hello Ravi,

If you are using Adventureworks database and using the above approach properly, you must see we are joining tables based on some values.

So you can join Person.Person table and Person.EmailAddress table from the database using BusinessEntityID Column

Here is the code

using (AdventureWorks2012Entities db = new AdventureWorks2012Entities())
            {
                var person = (from e in db.EmailAddresses
                              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 == "US"
                              select new
                              {
                                  ID = e.BusinessEntityID,
                                  FirstName = p.FirstName,
                                  MiddleName = p.MiddleName,
                                  LastName = p.LastName,
                                  Region = t.CountryRegionCode,
                                  EmailAddress= e.EmailAddress1
                              }).ToList();

                foreach (var p in person)
                {
                    Console.WriteLine("{0} {1} {2} {3} {4} {5}", p.ID, p.FirstName, p.MiddleName, p.LastName, p.Region, p.EmailAddress);
                }
            }

Here is the gif image of the Output which shows Email Id of the person

Note: You would have to create a Class Model to show it in a view

To Show this data in the view, add a new Class "PersonWithEmail.cs" inside Models Folders of the project and then pass this Model to the view

namespace JoinsUsingEF.Models
{
    public class PersonWithEmail
    {
        public int ID { get; set; }
        public string FirstName { get; set; }
        public string MiddleName { get; set; }
        public string LastName { get; set; }
        public string Region { get; set; }
        public string EmailAddress { get; set; }
    }
}

Now, changes in C# controller would be

 public ActionResult Index()
        {
           
            List<PersonWithEmail> person = new List<PersonWithEmail>();
            using (AdventureWorks2012Entities db = new AdventureWorks2012Entities())
            {
                 person = (from e in db.EmailAddresses
                              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 == "US"
                              select new PersonWithEmail
                              {
                                  ID = e.BusinessEntityID,
                                  FirstName = p.FirstName,
                                  MiddleName = p.MiddleName,
                                  LastName = p.LastName,
                                  Region = t.CountryRegionCode,
                                  EmailAddress= e.EmailAddress1
                              }).ToList();

                foreach (var p in person)
                {
                    Console.WriteLine("{0} {1} {2} {3} {4} {5}", p.ID, p.FirstName, p.MiddleName, p.LastName, p.Region, p.EmailAddress);
                }
            }
            return View(person);
        }

In the Index.cshtml view

@model IEnumerable<JoinsUsingEF.Models.PersonWithEmail>
@{
    ViewBag.Title = "Index";
}

<h2>Index</h2>

<table>
    <thead>
        <tr>
            <th>Id</th>
            <th>First Name</th>
            <th>Email Address</th>
        </tr>
    </thead>
    <tbody>
        @foreach (var person in Model)
        {
        <tr>
            <td>@person.ID</td>
            <td>@person.FirstName</td>
            <td>@person.EmailAddress</td>
        </tr>
        }
    </tbody>
</table>

Output: