How to apply Left Outer join in Linq to Sql

In this post, I will show you how to apply Left outer join in Linq to SQL. Imagine that we want to apply left outer join in Products and Categories table in Northwind database Something like this:

SELECT [t1].[CategoryName] AS [CategoryId]
FROM [dbo].[Products] AS [t0]
LEFT OUTER JOIN [dbo].[Categories] AS [t1] ON [t0].[CategoryID] = ([t1].[Categor
yID])

As an example, I will take the northwind Products and Categories table. Both these tables contain CategoryId as a common field.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace Linq_Tips
{
    class Program
    {
        static void Main(string[] args)
        {
            NorthwindDataContext dc = new NorthwindDataContext();
            var filteredQuery = from p in dc.Products
                                join c in dc.Categories
                                on p.CategoryID equals c.CategoryID into joinData
                                from data in joinData.DefaultIfEmpty()
                                select new
                                {
                                    CategoryId = data.CategoryName
                                };
            Console.WriteLine(filteredQuery);
        }
    }
}

The code above gets translated into this SQL query. {alertInfo}

SELECT [t1].[CategoryName] AS [CategoryId]
FROM [dbo].[Products] AS [t0]
LEFT OUTER JOIN [dbo].[Categories] AS [t1] ON [t0].[CategoryID] = ([t1].[Categor
yID])

Please do not post any spam link in the comment box😊

Post a Comment (0)
Previous Post Next Post