C# Linq expression for nested left join

Question

I'm looking to format a nested left join in a linq expression. I've downloaded LINQPad4, but it doesn't seem to have the ability to translate SQL into linq.

I don't understand how to get a nested left join. I've tried manipulating the expression with the 'into' clause to no avail.

Linq expression

from cli in Clients
join er in EntityRelationships on cli.EGUID equals er.ParentID
join con in Contacts on er.ChildID equals con.EGUID

join erEmail in EntityRelationships on er.EGUID equals erEmail.ParentID into erIfAny
from r in erIfAny.DefaultIfEmpty()
join ea in EmailAddresses on r.ChildID equals ea.EGUID
join eaDefault in EntityDefaultValues on r.ID equals eaDefault.RelationshipID

where cli.ClientID == 6
select new
{
    ContactID = con.ContactID,
    ContactLastName = con.ContactLastName,
    ContactFirstName = con.ContactFirstName,
    ContactTitle = con.ContactTitle,
    EmailAddress = ea
}).Distinct()

Produces the following SQL statement

DECLARE @p0 Int = 6

SELECT DISTINCT con.*, ea.*
FROM Clients AS cli
INNER JOIN EntityRelationships AS er ON cli.eGUID = er.ParentID
INNER JOIN Contacts AS con ON er.ChildID = con.eGUID

LEFT OUTER JOIN EntityRelationships AS erEmail ON er.eGUID = erEmail.ParentID
INNER JOIN EmailAddresses AS ea ON erEmail.ChildID = ea.eGUID
INNER JOIN EntityDefaultValues AS eaDefault ON erEmail.ID = eaDefault.RelationshipID

WHERE cli.ClientID = @p0

However, I need the two inner joins after the left to be wrapped inside the left like so.

DECLARE @p0 Int = 6

SELECT DISTINCT con.*, ea.*
FROM Clients AS cli
INNER JOIN EntityRelationships AS er ON cli.eGUID = er.ParentID
INNER JOIN Contacts AS con ON er.ChildID = con.eGUID

LEFT OUTER JOIN EntityRelationships AS erEmail
    INNER JOIN EmailAddresses AS ea ON erEmail.ChildID = ea.eGUID
    INNER JOIN EntityDefaultValues AS eaDefault ON erEmail.ID = eaDefault.RelationshipID
ON er.eGUID = erEmail.ParentID

WHERE cli.ClientID = @p0

What should my linq expression look like?

Edit:

Didn't know I could do this, but I ended up just writing inline SQL.

var query = db.Database.SqlQuery<ContactViewModel>(
"select distinct con.*, ea.Value as EmailAddress " +
"from Clients AS cli " +
"join EntityRelationships AS er ON cli.eGUID = er.ParentID " +
"join Contacts AS con ON er.ChildID = con.eGUID " +
"left join EntityRelationships AS erEmail " +
    "join EmailAddresses AS ea ON erEmail.ChildID = ea.eGUID " +
    "join EntityDefaultValues AS eaDefault ON erEmail.ID = eaDefault.RelationshipID " +
"on er.eGUID = erEmail.ParentID " +
"where cli.ClientID=" + cID.ToString() + " " +
"order by con.ContactLastName, con.ContactFirstName");

Possible duplicate of this: http://stackoverflow.com/questions/267488/linq-to-sql-multiple-left-outer-joins?rq=1 — ataravati, Mar 17 '15 at 14:33
Those are just 2 left joins on each other. I didn't notice anything with nesting. — Jameson, Mar 17 '15 at 14:46
SQL doesn't actually work the way you're describing it. If `EmailAddresses` and `EntityDefaultValues` are dependent on `EntityRelationships`, they have to either be left joins or explicitly nested (subselect) in order to return the results you want. — GalacticCowboy, Mar 17 '15 at 14:53

score 1 · Answer 1 · answered Mar 17 '15 at 14:37

It's a very common mistake for people to try to get LINQ code to match the SQL they were used to. Since SQL is limited to a flattened result set, whereas object-oriented code can represent richer hierarchical data, it's very likely what you really want can be modeled better in a LINQ query, if you stop thinking too hard about the SQL side of things.

I'm not entirely sure what you're going for here, but often a left-outer join in LINQ ends up looking like one object with other objects on one of its properties:

from cli in Clients
where cli.ClientID == 6
join er in EntityRelationships on cli.EGUID equals er.ParentID
join con in Contacts on er.ChildID equals con.EGUID
select new
{
    ContactID = con.ContactID,
    ContactLastName = con.ContactLastName,
    ContactFirstName = con.ContactFirstName,
    ContactTitle = con.ContactTitle,
    Emails = 
        from erEmail in EntityRelationships
        where er.EGUID == erEmail.ParentID
        join ea in EmailAddresses on r.ChildID equals ea.EGUID
        where EntityDefaultValues.Any(eaDefault => r.ID == eaDefault.RelationshipID
        select ea
}

I've never thought about it that way - I guess I should start. ;) Still didn't work exactly how I need it too, though. Thanks. — Jameson, Mar 17 '15 at 17:56

C# Linq expression for nested left join

1 Answers1