How to perform left join using two or more table in LInq to Sql query?

Question

I want to perform left join using two or more table in Linq to Sql query but it looks like there is no keyword for doing so in C#. Is there any way to perform left join in the same way we do inner joins using Linq to Sql?

public IQueryable GetProducts(int productID)
{
    var products = 
        from p in this.Products 
        left join c in this.ProductCategoryProducts 
        on p.CategoryID equals c.ProductCategoryID
        left join ac in this.ProductCategories 
        on c.ProductCategoryID equals ac.ProductCategoryID
        where p.ProductID == productID
        select new
        {
            ProductID = a.ProductID,
            Heading = p.Heading,                
            Category = ac.ProductCategory
        };
    return products ;
}

`DefaultIfEmpty()` is the trick to left outer joins in Linq. See this http://msdn.microsoft.com/en-us/library/bb397895.aspx — Ben Robinson, Sep 19 '14 at 13:31
check this answer for a good explanation of group join http://stackoverflow.com/a/15599143/2617732 — rdans, Sep 21 '14 at 10:53

score 0 · Answer 1 · answered Sep 21 '14 at 03:38

public IQueryable GetProducts(int productID)
{
    var products = 
        from p in this.Products 
        join c in this.ProductCategoryProducts 
        on p.CategoryID equals c.ProductCategoryID into pclj
        from pc in pclj.DefaultIfEmpty()
        join ac in this.ProductCategories 
        on c.ProductCategoryID equals ac.ProductCategoryID into pcidlj
        from pcid in pcidlj.DefaultIfEmpty()
        where p.ProductID == productID
        select new
        {
            ProductID = p.ProductID,
            Heading = p.Heading,                
            Category = pcid != null ? pcid.ProductCategory : null
        };
    return products ;
}

Sonu K · Accepted Answer · 2014-09-22T13:05:17.407

Anyways I got the solution using Lambda expressions in this way:

var products = 
        from p in this.Products 
        join cat in this.ProductCategoryProducts 
        .Where(c => c.ProductID == p.ProductID).DefaultIfEmpty()

        from pc in this.ProductCategories 
        .Where(pc => pc.ProductCategoryID == cat.ProductCategoryID).DefaultIfEmpty()

        where p.ProductID == productID
        select new
        {
            ProductID = p.ProductID,
            Heading = p.Heading,                
            Category = pc.ProductCategory
        };
    return products ;

How to perform left join using two or more table in LInq to Sql query?

2 Answers2