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
– Gert Arnold, Commented Dec 22, 2013 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
– JuanDYB, Commented Dec 23, 2013 at 0: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
– HLGEM, Commented Sep 8, 2014 at 19:39

Sonu K · Accepted Answer · 2014-09-22 11:19:29Z

3

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 ;

answered Sep 22, 2014 at 11:19

Sonu K

2,8424 gold badges25 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Multiple Left Outer Join with lambda expressions

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related