SQL limitation equivalent in linq

Question

I am looking for a linq equivalent of a SQL query bellow.

select * from tableA as A
left join tableB as B on A.Id = B.Id
left join tableC as C on B.Id = C.Id
left join tableD as D on C.Id = D.Id and D.OrderId = B.OrderId

I am most interested how to correctly limit results using this expression:

 and D.OrderId = B.OrderId

C# code

var data = from a in tableA

join innerB in tableB on a.Id equals innerB.Id into INNERB
from b in INNERB.DefaultIfEmpty()

join innerC in tableC on b.Id equals innerC.Id into INNERC
from c in INNERC.DefaultIfEmpty()

join innerD in tableD on c.Id equals innerD.Id into INNERD
from d in INNERD.DefaultIfEmpty().Where(p=>p.OrderId == b.OrderId)

The linq returned results from db doesn't match SQL query. Any hints are appreciated if you could explain me how to use a variable from a previous join table that is applied for each row.

I've tried also but the compiler throws an error that "the name of b doesn't exist in current context":

join innerD in tableD.Where(p=>p.OrderId == b.OrderId) on c.Id equals innerD.Id into INNERD
from d in INNERD.DefaultIfEmpty()

score 1 · Accepted Answer · edited May 23 '17 at 11:58

1

See this answer for a simpler LEFT JOIN syntax: https://stackoverflow.com/a/4739738/1869660

Your query would be:

var data = from a in tableA
           from b in tableB.Where(x => x.Id == a.Id)
                           .DefaultIfEmpty()
           from c in tableC.Where(x => x.Id == b.Id)
                           .DefaultIfEmpty()
           from d in tableC.Where(x => (x.Id == c.Id) && (x.OrderId == b.OrderId))
                           .DefaultIfEmpty()
           select ...

edited May 23 '17 at 11:58

Community

1
1

answered Aug 02 '15 at 13:39

Sphinxxx

12,484
4
54
84

1

In my case it worked. Thanks a lot. However I cannot always use this syntax. "There is an issue with using this syntax when joining nullable values or strings. Since the where statement doesn’t know about the cardinality relationship (1-Many, 0-1 – Many, etc), Entity Framework adds an additional check where the nullable value is not null to allow for join cases where both sides have a null value" from http://www.thinqlinq.com/Post.aspx/Title/Left-Outer-Joins-in-LINQ-with-Entity-Framework – wariacik Aug 02 '15 at 17:51

SQL limitation equivalent in linq

1 Answers1