Multiple Left Outer Join with lambda expressions

10,495

Why don't you try using linq query, it is also much easier to write and understand both as compared to lambda expressions. I have on such implementation like:

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

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

        where p.ProductID == productID
        select new
        {
            ProductID = p.ProductID,
            Heading = p.Heading,                
            Category = pc.ProductCategory
        };
    return products ;
Share:
10,495
JuanDYB
Author by

JuanDYB

Updated on June 09, 2022

Comments

  • JuanDYB
    JuanDYB almost 2 years

    I have an SQL Query to do with Lambda Expressions like this, generally with more joins than in this example.

    select Table2.a,
              Table2.b,
              Table2.c,
              Table2.d
       from Table1
         LEFT OUTER JOIN Table2
         ON Table2.a = Table1.a and
            Table2.b = Table1.b and
            Table2.c = Table1.c 
         LEFT OUTER JOIN Table3
         ON Table3.b = Table1.b AND
            Table3.c = Table1.c AND
            Table3.d = Table1.d 
       where ( Table1.a = ValueA )
       order by Table3.f
    

    I'm doing this with Join() Lambda Expression, but i see in SQL Server profiler that this generate an INNER JOIN and i need a LEFT OUTER JOIN.

    This is how i'm doing it with Join()

    var RS = DBContext.Table1.Join(DBContext.Table2,
      Table1 => new {Table1.a, Table1.b, Table1.c},
      Table2 => new {Table1.a, Table1.b, Table1.c},
      (Table1, Table2) => new {Table1})
    .Join(DBContext.Table3,
      LastJoin => new {LastJoin.Table1.b, LastJoin.Table1.c, LastJoin.Table1.d},
      Table3 => new {Table3.b, Table3.c, Table3.d},
      (LastJoin,Table3) => new {LastJoin.Table1, Table3})
    .Where (LastTable => LastTable.Table1.a == ValueA)
    .OrderBy(LastTable => LastTable.Table3.f)
    .Select (LastTable => new {LastTable.Table1, LastTable.Table3});
    

    I have been reading that it can be done with DefaultIfEmpty() or GroupJoin() but i haven't find any complex example with more than one LEFT OUTER JOIN.