Left outer join using LINQ

A left outer join is a way of joining tables together. Unmatched entries from only the table given before the LEFT OUTER JOIN clause are included in the result. Use a LEFT OUTER JOIN clause or a RIGHT OUTER JOIN clause when linking two tables and only want the result set to have mismatched rows from one table.

LINQ (Language Integrated Query) is a compelling feature of C#, which allows the developer to write code in the query like syntax. Linq enable the developer to write code in C#, and then it generates the SQL on the fly. LINQ does not provide any operator for left join, but we can achieve this very quickly using Linq
In this article, I will show how to generate a left outer join using LINQ to SQL. For this post, I am going to use the Categories and Product table of northwind.

The first way to generate left outer join

var query=from p in Products
          join c in Categories
          on p.CategoryID equals c.CategoryID into pc
          where p.CategoryID==1
          from j in pc.DefaultIfEmpty()
          select new
          {
          CategoryName=j.CategoryName,
          ProductName=p.ProductName
          };

output generated code

SELECT [t1].[CategoryName] AS [CategoryName], [t0].[ProductName]
FROM [Products] AS [t0]
LEFT OUTER JOIN [Categories] AS [t1] ON [t0].[CategoryID] = ([t1].[CategoryID])
WHERE [t0].[CategoryID] = @p0

second way without using into the operator

var query=from p in Products
          from c in Categories.Where(x=>x.CategoryID==p.CategoryID).DefaultIfEmpty()
          select new
          {
          CategoryName=c.CategoryName,
          ProductName=p.ProductName
          };

HAPPY CODING 😊❤❤

Left outer join using LINQ

Contact Form