Introduction to Entity Framework Join LINQ

Entity Framework Join LINQ is used to load the data from one or more than one tables. Entity Framework supports and is advisable to use navigational properties instead of LINQ Join to query the target data. We can also make use of the generated queries for performance benefits. The LINQ Join operator enables to join several tables on one or more columns.

What is Entity Framework Join LINQ?

It uses the Join query, which loads the data from one or two tables. The LINQ Join operators enable to join several tables on more columns by using multiple columns. We can perform several joins like inner joins on tables to perform the left and right joins in Entity Framework using the Join Operator and the DefaultIfEmpty Method.

Using Entity Framework Join LINQ

Entity Framework Join is used to join the entities from one or more tables with the related column between the entities. In Entity Framework, LINQ joins load the data from several tables. Mainly it is used to combine the data from two or more tables.

Let’s see several procedures:

• Using Navigational Properties instead of LINQ Join is always desirable for querying the targeted data.
• We need to use Join Operators if the entities do not have any Navigational Properties defined.
• It is used to fine-tune the developed queries for achieving benefits.

Let’s see the following queries joining the two tables, ProductMaster and StoresMaster, by using the Join Method as follows:

Code:

using (dbEntities db = new dbEntities())
   {
    var LinqJoin = db.ProductMasters.Join(db.StoreMasters, p => p.StoreId, s => s.StoreId, (p, s) => new
    {
     Product_ID = p.ProductId,
     ProductName = p.ProductName,
     StoresName = s.StoreName
     }).ToList();

In the above code, we use the Join operator in LINQ to retrieve the records based on one equality entity in that table, StoreID, which appears in both tables; based on that, we retrieve the records.

Code:

  foreach (var stores in LinqJoin)
  {
Console.WriteLine("\n\t"+stores.StoresName+"\t"+stores.Product_ID+"\t"+stores.ProductName);
  }

We retrieved the StoresName, Product_ID, and ProductName by combining two tables.

Entity Framework Join LINQ Multiple Columns

Entity Framework LINQ Join operators enable to join several tables on more columns by using multiple columns.

Let’s see the following example, which combines three tables ProductMasters, StoreMasters, and CustomerMasters.

Code:

var LinqJoin = db.ProductMasters.Join(db.StoreMasters, p => p.StoreId, s => s.StoreId, (p, s) => new
{
Product_ID = p.ProductId,
ProductName = p.ProductName,
StoresName = s.StoreName
}).ToList();

Here we combine three tables based on the ID:

Code:

var customerProducts=db.CustomerMasters.Join(db.ProductMasters,
  c=>c.CustomerId,
   p=>p.ProductId,(c,p)=>new
   {
    Customer_ID=c.CustomerId,
    Customer_Name=c.CustomerFirstName,
    Product_Name=p.ProductName
    }).Join(db.StoreMasters, c=>c.Customer_ID, s => s.StoreId, (c, s) => new
   {
    CustID= c.Customer_ID,
    CustName=c.Customer_Name,
    ProdName=c.Product_Name,
    StoreID=s.StoreId,
    StoreName=s.StoreName
}).ToList();

In the below code, we retrieve entities by joining three tables. We are using the Join operator in LINQ to retrieve the records based on one equality entity in that table: StoreID, and Customer_ID, which appear in tables, and based on that, we are retrieving the records.

Code:

foreach (var res in customerProducts)
  {
   Console.WriteLine("\n\t" + res.CustID + "\t" + res.CustName + "\t" + res.StoreID+"\t"+res.StoreName+"\t"+res.ProdName);
  }

Examples

In this Entity Framework, we look into how the Join Query loads the data from one or more tables. LINQ Join enables to combine the several tables on multiple columns. Entity Framework uses the LINQ (Language Integrated Query) for querying the data from the database. LINQ enables to make use of strongly typed queries. It uses the customized context and entity classes to reference the database objects. The Entity Framework passes the representation of queries of LINQ to the database provider. In database providers, it converts the database-specific query language.

The LINQ Join operators enable to join several tables on more columns by using multiple columns. For example, using the Join Operator, we can perform several joins like inner joins on tables to perform the left and right joins in Entity Framework.

Code:

var LinqJoin = db.ProductMasters.Join(db.StoreMasters,
  p => p.StoreId, s => s.StoreId, (p, s) => new
{
Product_ID = p.ProductId,
ProductName = p.ProductName,
StoresName = s.StoreName
}).ToList();

Here in the above code, we initially combine two tables, StoreMasters and ProductMaster, based on the StoresID.

Code:

db.CustomerMasters.Join(db.ProductMasters,
   c=>c.CustomerId,
   p=>p.ProductId,(c,p)=>new
   {
    Customer_ID=c.CustomerId,
    Customer_Name=c.CustomerFirstName,
    Product_Name=p.ProductName
      }).Join(db.StoreMasters, c=>c.Customer_ID,
      s => s.StoreId, (c, s)

Here in the above code, we join three tables and retrieve the records; using the LINQ operator, we join the object names as (c,p) and (c,s).

Code:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace Console_EF_LINQ_Joins
 {
  class Program
   {
    static void Main(string[] args)
     {
      using (dbEntities db = new dbEntities())
       {
        var LinqJoin = db.ProductMasters.Join(db.StoreMasters,
p => p.StoreId, s => s.StoreId, (p, s) => new
        {
          Product_ID = p.ProductId,
          ProductName = p.ProductName,
          StoresName = s.StoreName
          }).ToList();
var customerProducts=db.CustomerMasters.Join(db.ProductMasters,
 c=>c.CustomerId,
 p=>p.ProductId,(c,p)=>new
 {
  Customer_ID=c.CustomerId,
  Customer_Name=c.CustomerFirstName,
  Product_Name=p.ProductName
     }).Join(db.StoreMasters, c=>c.Customer_ID,
     s => s.StoreId, (c, s) => new
{
  CustID= c.Customer_ID,
  CustName=c.Customer_Name,
  ProdName=c.Product_Name,
  StoreID=s.StoreId,
  StoreName=s.StoreName
  }).ToList();
 foreach (var res in customerProducts)
  {
    Console.WriteLine("\n\t" + res.CustID + "\t" + res.CustName + "\t" + res.StoreID+"\t"+res.StoreName+"\t"+res.ProdName);
  }
   Console.ReadLine();
   }
  }
 }
}

Let’s see one more sample program which joins two tables as follows:

Code:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace Console_EF_LINQ
 {
  class Program
   {
    static void Main(string[] args)
    {
     using (PASTRYEntities db = new PASTRYEntities())
     {
      var LinqJoin = db.ProductMasters.Join(db.StoreMasters, p => p.StoreId, s => s.StoreId, (p, s) => new
     {
     Product_ID = p.ProductId,
     ProductName = p.ProductName,
     StoresName = s.StoreName
    }).ToList();
    Console.WriteLine("\n\t\t\tEntity Framework - LINQ Join");
    Console.WriteLine("\t\t\t----------------------------\n");
    Console.WriteLine("-----------------------------------------------------------------\n");
    Console.WriteLine("\tStores Name \tProduct ID \tProduct Name \n");
    Console.WriteLine("-----------------------------------------------------------------\n");
    foreach (var stores in LinqJoin)
    {
     Console.WriteLine("\n\t" + stores.StoresName + "\t" + stores.Product_ID + "\t\t" + stores.ProductName);
     }
      Console.WriteLine("-----------------------------------------------------------------\n");
      Console.ReadLine();
     }
   }
 }
}

Output:

Conclusion

In this article, we have seen Entity Framework LINQ Join; here, we are joining the multiple tables using several conditions. Unfortunately, join only supports equality which does not support other operators.

Recommended Articles

This is a guide to Entity Framework Join LINQ. Here we discuss the introduction with multiple columns and examples. You may also have a look at the following articles to learn more –

1. Entity Framework Core
2. ASP.NET Core Entity Framework
3. Entity Framework NuGet
4. Entity Framework Join

The post Entity Framework Join LINQ appeared first on EDUCBA.