Updated February 17, 2023
Introduction to Entity Framework Left Join
Entity Framework Left Join is a database-oriented process that joins the data from two or more tables using a join query. In other words, the left join returns the entire matching data from both tables and non-matching data from the left table. Entity Framework is the Object Relational Mapper for the .NET Applications, enabling us to work with databases using the .NET objects; through this, we retrieve the data from the database using the Left Joins.
Overview of Entity Framework Left Join
Entity Framework is an Object Relational Mapper (ORM) that allows developers to work with databases using the .NET objects. Left Join retrieves the data from the database based on several conditions. EF enables the developers to work on data objects of domain classes. Entity Framework Developers can work with the efficiency level of abstraction where they proceed with data and can manage the data-oriented applications with minimal code compared with habitual applications. Entity Framework removes the most required data access coding, which developers frequently need to write; it helps minimize the code line.
Left Join or Left Outer Join is the join where the data of matching records will be returned, and from the left table, it returns matching and non-matching records that non-matching records will be displayed as NULL. In other words, the left join returns the entire matching data from both tables and non-matching data from the left table. For example, look at the diagram which depicts the Left Join.
In the above diagram, there will be the left outer, right outer, left data source, and right outer and inner for better understanding. Left – Join usually returns the entire records from the left table and the matching records of the right table; if there is no match, it will return NULL on the columns.
How to Use Entity Framework Left Join?
Entity Framework removes the most required data access coding, which developers frequently need to write; it helps minimize the code line.
Syntax of the Left Outer Join:
Syntax:
SELECT [column-name]
FROM [table-1 name]
LEFT [OUTER] JOIN [table-2 name]
ON [table-1 column] = [table-2 column]Left – Join usually returns the entire records from the left table and the matching records of the right table; if there is no match, it will return NULL on the columns. Entity Framework performs the join query with inner joins; using that, we can join multiple tables and use various conditions. However, those conditions maintain only equality, and it does not support other operators. For performing the left–join, we need to use the DefaultIfEmpty method and the application’s join code. We can also perform the joining by use of the navigation properties.
Let us see how to use it with coding:
Left Join
Entity Framework performs the inner join while combining two tables; for converting that inner join to Left Join, we need to create a variable that holds the before result and use the DefaultIfEmpty method as coded below.
Code:
on p.StoreId equals sr.StoreId into lJStart the new query from lJ and include DefaultIfEmpty.
from res in lJ.DefaultIfEmpty()Let’s see the below code, which covers the entire code of performing Left-Join.
Code:
using (ConName db = new ConName ())
{
var data = (from p in db.ProductMasters join sr in db.StoreMasters
on p.StoreId equals sr.StoreId into lJ
from res in lJ.DefaultIfEmpty()
select new
{
ProductID = p.ProductId,
ProductName = p.ProductName,
StoreID=res.StoreId,
StoreName=res.StoreName,
StoreEmail=res.EmailId
}).ToList();
foreach (var products in data)
{
Console.WriteLine(products.ProductID+"\t"+products.ProductName+"\t\t" + products.StoreName+"\t"+products.StoreEmail+"\n");
}In SQL Query, we can code as follows:
Code:
SELECT p.ProductId, p.ProductName,s.StoreId,s.StoreName,s.EmailId
FROM StoreMaster s
LEFT JOIN ProductMaster p
ON p.StoreId = s.StoreIdIt retrieves the record based on the left join query.
Example
Let’s see how to implement it in the application with one programming example.
Initially, create a new project in VS, select File – New – Project, and select Console Application; give the suitable name and click OK.
Next, create the Entity Model; right-click on the Project Name in the Solution Explorer Add – New Item – ADO.NET Entity Model and give the suitable name.
Select EF Designer from Database and click Next.
New Connection – Click OK.
Once given a connection to the database, select the desired tables from which you want to retrieve the data. Finally, click on the Finish button.
Once finishing the above process, next come to the coding part to use the left join in the Entity Framework of the application, Entity Framework Left Join, which retrieves the data records from combining two or more tables; here, Entity Framework maintains connecting multiple tables on columns. Performing Left Join in Entity Framework, we need to use the Join operator and DefaultIfEmpty method.
Code:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace ConsoleEF_LeftJoin
{
class Program
{
static void Main(string[] args)
{
using (conName db = new conName())
{
var data = (from p in db.ProductMasters join sr in db.StoreMasters
on p.StoreId equals sr.StoreId into lJ
from res in lJ.DefaultIfEmpty()
select new
{
ProductID = p.ProductId,
ProductName = p.ProductName,
StoreID=res.StoreId,
StoreName=res.StoreName,
StoreEmail=res.EmailId
}).ToList();
Console.WriteLine("\n\t\t\tLeft - Join using Entity Framework");
Console.WriteLine("\t\t\t----------------------------------\n");
Console.WriteLine("Getting Product and Stores Details");
Console.WriteLine("----------------------------------\n\n");
Console.WriteLine("------------------------------------------------------------------\n");
Console.WriteLine("Prod_ID Product_Name Store_Name \tStore_Email\n");
Console.WriteLine("-----------------------------------------------------------------\n");
foreach (var products in data)
{
Console.WriteLine(products.ProductID+"\t"+products.ProductName+"\t\t"+products.StoreName+"\t"+products.StoreEmail+"\n");
}
Console.WriteLine("--------------------------------------------------------------------\n");
Console.Read();
}
}
}
}Entity Framework mainly uses the inner joins by using that we can join multiple tables and with various conditions that maintain only equality and do not support other operators. For performing the left–join, we need to use the DefaultIfEmpty method and the application’s join code.
Output:
Conclusion
In this article, we have seen the Entity Framework Left Join, which retrieves the data records from combining two or more tables; the entity Framework maintains for connecting multiple tables on columns. Performing Left Join in Entity Framework, we need to use the Join operator and DefaultIfEmpty method.
Recommended Articles
This is a guide to Entity Framework Left Join. Here we discuss the introduction and how to use the entity framework left join with an example. You may also have a look at the following articles to learn more –
