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 ;
Author by
JuanDYB
Updated on June 09, 2022Comments
-
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.