Multiple Join Statements with LEFT JOIN to LINQ Expression syntax

Question

I have a slightly complicated SQL query that I'm trying to convert to LINQ Expression syntax as that is what we use for our code base. Everyone online seems to use query syntax though, which is making finding the right answer quite difficult.

The query is as follows and returns exactly what I'm after (there's probably a nicer way to do this query, please feel free to suggest one);

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 is converting this. So far all I've got is;

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 don't understand the query 100% but it gets Entities who's Position has a Requirement, which is what it's meant to do.

So in closing, if someone could help me convert the rest of the SQL statement, it would be much appreciated.

Answer 1

This is how could your original query look in LINQ method syntax:

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});

The GroupJoin is here equivalent of SQL LEFT JOIN.

IMHO, the method syntax is awkward for such complicated joins. The query syntax would be far more readable.