Multiple LEFT JOIN Statements for LINQ Expression Syntax
I have a somewhat complex SQL query that I am trying to convert to LINQ expression syntax as this is what we are using for our code base. However, everyone online seems to use query syntax, making it difficult to find the correct answer.
The request looks like this and returns exactly what I need (maybe a nicer way to make this request, please feel free to suggest it);
SELECT e.*, ce.Certificate_ID
FROM FCERTSTest.dbo.Entities AS e
INNER JOIN FCERTSTest.dbo.RequirementEntries AS re
ON re.Position_ID = e.EntityPosition_ID
LEFT JOIN FCERTSTest.dbo.CertificateEntries AS ce
ON ce.Entity_ID = e.EntityID AND ce.Certificate_ID = re.Certificate_ID
WHERE ce.Certificate_ID IS NULL
The problem lies in its transformation. So far I have:
List<Entities> unqualified = new List<Entities>();
unqualified = Entities.Join(RequirementEntries,
ent => ent.EntityPosition_ID,
req => req.Position_ID,
(ent, req) => ent).ToList();
Which I pulled from the internet ... Ihonestly doesn't understand the request 100%, but it gets Entities whose position has a Requirement, which it should have done.
So in conclusion, if anyone can help me transform the rest of the SQL statement, that would be greatly appreciated.
source to share
This is how your original query might look in the syntax of the LINQ method:
unqualified = Entities.Join(RequirementEntries,
ent => ent.EntityPosition_ID,
req => req.Position_ID,
(e, r) => new {e,r})
.GroupJoin(CertificateEntries.Where(c=>c.CertificateID == null),
req => new{ Cid = (int?) req.r.Certificate_ID, Eid = (int?) req.e.EntityID },
cer => new{ Cid = (int?) cer.Certificate_ID, Eid = (int?) cer.EntityID },
(x,y) => new {EnRe = x, Cer = y })
.SelectMany(x=> x.Cer.DefaultIfEmpty(),
(x,y) => new { Ent = x.Enre.e, Certs = y});
GroupJoin
here is equivalent to SQL LEFT JOIN
.
IMHO, the syntax of the method is inconvenient for such complex unions. The query syntax will be more readable.
source to share