How to apply Left Outer join in Linq to Sql

Applying Left Outer Join in LINQ to SQL

In this post, I will demonstrate how to apply a left outer join in LINQ to SQL using the example of joining the “Products” and “Categories” tables in the Northwind database.

Imagine that we want to retrieve the CategoryName from the “Categories” table, including unmatched entries from the “Products” table.

The SQL query we aim to achieve looks 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])

To achieve the same result using LINQ to SQL, we can write the following code:

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);
        }
    }
}

In the code snippet above, we create a LINQ query that performs a left outer join. The join keyword is used to join the “Products” and “Categories” tables based on the “CategoryID” column. The into joinData clause groups the joined data, and DefaultIfEmpty() ensures that unmatched entries from the left table are included in the result set. Finally, we select the desired columns using an anonymous type.

When executed, the LINQ query will be translated into the equivalent SQL query, achieving the desired left outer join functionality.

Feel free to adapt this code to your specific scenario and explore the power of left outer joins in LINQ to SQL.

Happy coding!