In this article, I have explained how you can implement inner join on two tables using entity framework (ef) join in ASP.NET MVC and C# 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 the 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

ado-net-modal-ef-join-two-tables-min.png

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.

ef-ado-net-entity-min.png

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

output-results-ef-join-linq-two-tables-min.png

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

ef-join-using-lambda-results-min.png

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

ef-join-more-than-two-tables-min.gif

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.