Multiple Left Outer Join with lambda expressions

Question

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.

Can you show which navigation properties you've got? This is so much easier when you can use syntax like `orderby table1.Table3.f`. — Gert Arnold, Dec 22 '13 at 14:43
Hello, my lambda expression dos not indicate it because i want all the fields of the entities that is the reason of the select at the end of the lambda expression. — JuanDYB, Dec 23 '13 at 00:59
Sinceyou know how to write teh query with left joins, why not directly call it or put it in a stored proc and call it? — HLGEM, Sep 08 '14 at 19:39

score 3 · Answer 1 · answered Sep 22 '14 at 11:19

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 ;

Multiple Left Outer Join with lambda expressions

1 Answers1