Multiple Left Outer Join with lambda expressions

c# entity-framework lambda sql sql-server

Question

I often use more joins than in this example in a SQL Query I have to accomplish using lambda expressions.

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 using the Join() Lambda Expression for this, however I can see in the SQL Server profiler that I need to use an LEFT OUTER JOIN instead because this generates an INNER JOIN.

I'm using Join in this manner ()

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've read that it can be accomplished using GroupJoin() or DefaultIfEmpty(), but I haven't come across any intricate examples involving more than one LEFT OUTER JOIN.

1
5
12/15/2014 1:55:39 PM

Popular Answer

Why don't you give linq queries a shot? They're considerably simpler to create and comprehend than lambda expressions. Regarding such implementation, I have:

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 ;
3
9/22/2014 11:19:29 AM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow