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.

mipe34 · Accepted Answer · 2013-02-08T07:28:02.163

3

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.

edited Feb 08 '13 at 07:28

answered Feb 05 '13 at 09:01

mipe34

5,596
3
26
38

Thanks. I can follow that up to `(x,y)` and then you've lost me...Are they just random variables? What's EnRe? It's also where it seems to fall down and have some compile errors...for example, GroupJoin can't inferred type arguments from usage and Ent and Certs don't exist. – Trent Feb 07 '13 at 04:06
Sorry, I have forgot to put there the creation of anonymous object in the `SelectMany` statement. Updated my answer (I believe, it should compile now). `(x,y)` just declares that `x` and `y` will be used as the names of my anonymous method input parameters. `new {Enre = x,..}` means, that I am creating an anonymous object with property named `Enre` and its value will be `x`, which is here actually the anonymous object (`new{e,r}` created as the select result of the first join). – mipe34 Feb 07 '13 at 07:56
OK, that makes more sense...kind of. I still get this error on `GroupJoin()` though :( The type arguments for method `'System.Linq.Enumerable.GroupJoin(System.Collections.Generic.IEnumerable, System.Collections.Generic.IEnumerable, System.Func, System.Func, System.Func,TResult>)' cannot be inferred from the usage. Try specifying the type arguments explicitly.` – Trent Feb 07 '13 at 08:17
The problem is probably that properties `Certificate_ID` and/or `EntityID` are nullable on the one side of the statement and not nullable on the other => so two types of anonymous 'key' objects are created (one with nullable, second with not nullable properties). We have to specfify the types of anonymous object property explicitly, see my updated answer. – mipe34 Feb 07 '13 at 08:45
The final problem (on the `e` in `Ent = x.e`); `'AnonymousType#1' does not contain a definition for 'e' and no extension method 'e' accepting a first argument of type 'AnonymousType#1' could be found (are you missing a using directive or an assembly reference?)` And thankyou so much for your help with this. I'm way out of my league here... – Trent Feb 08 '13 at 00:27
Always trying hard without the compiler... There should be `x.Enre.e` – mipe34 Feb 08 '13 at 07:30
Heh, the problems continue -_- ; `Cannot implicitly convert type 'System.Collections.Generic.IEnumerable' to 'System.Collections.Generic.List'. An explicit conversion exists (are you missing a cast?)` I've actually built a bit of a system that will allow me to insert new reports without having to send out a program update, which uses raw SQL, so I think I'm just going to stick with the SQL. Thanks for all your efforts though :) – Trent Feb 08 '13 at 07:51
Np ;-). The error is obvious. When the query is executed it returns an `IEnumerable` of anonymous objects, which contains properties `Ent` of type `Entity` and `Certs` of type `Certificate`. So you cannot directly convert it into `Entity`. However, you can change the `Select` to project just the columns you need. – mipe34 Feb 08 '13 at 08:01

Multiple Join Statements with LEFT JOIN to LINQ Expression syntax

1 Answers1