Linq-to-Entities: LEFT OUTER JOIN with WHERE clause and projection

Question

I'm having a heckuva time figuring out how to translate a simple SQL LEFT OUTER JOIN with a two condition where clause into a working Linq-to-Entities query. There are only two tables. I need values for all rows from Table1, regardless of matches in Table2, but the WHERE clause uses fields from Table2. In SQL, the two parameters would be Table2WhereColumn1 and Table2WhereColumn2, and the query (which works) looks like this:

SELECT t1.Table1Id,
    t1.FieldDescription, 
    t2.FieldValue
FROM Table1 t1 WITH (NOLOCK)
LEFT JOIN Table2 t2 WITH (NOLOCK) ON t1.Table1Id = t2.Table1Id
WHERE (t2.Table2WhereColumn1 = @someId OR t2.Table2WhereColumn1 IS NULL)
AND (t2.Table2WhereColumn2 = @someOtherId OR t2.Table2WhereColumn2 IS NULL)
ORDER BY t1.OrderByColumn

I've tried using Group Join with DefaultIfEmpty(), as well as an implicit join (without the actual Join keyword), and I only get rows for items that have values in Table2. I'm sure this won't help, but here's an example of the Linq I've been trying that doesn't work:

Public Shared Function GetProfilePreferencesForCedent(ByVal dc As EntityContext, _
                                                      ByVal where1 As Int32, _
                                                      ByVal where2 As Int32) _
                                                  As IQueryable(Of ProjectedEntity)
    Return From t1 In dc.Table1
           Group Join t2 In dc.Table2 _
                On t1.Table1Id Equals t2.Table1Id _
                Into t2g1 = Group _
           From t2gx In t2g1.DefaultIfEmpty(Nothing)
           Where (t2gx.Table2Where1 = where1 Or t2gx.Table2Where1 = Nothing) _
                And (t2gx.Table2Where2 = where2 Or t2gx.Table2Where2 = Nothing)
           Order By t1.SortOrder
           Select New ProjectedEntity With {
               .Table1Id = t1.Table1Id, _
               .FieldDescription = t1.FieldDescription, _
               .FieldValue = If(t2gx Is Nothing, String.Empty, t2gx.FieldValue) _
           }
End Function

Answer 1

I won't take credit for this answer but it's gorgeous: LINQ to SQL - Left Outer Join with multiple join conditions

Essentially, use extension method where clause on the subquery but you must use it before DefaultIfEmpty():

from p in context.Periods
join f in context.Facts on p.id equals f.periodid into fg
from fgi in fg.Where(f => f.otherid == 17).DefaultIfEmpty()
where p.companyid == 100
select f.value

Answer 2

Have a go at these queries and tell me if they work for you. I haven't set up the data to test, but they should be fine.

Please excuse my mix of C# & VB.NET. I used to be a VB.NET developer, but in the last couple of years I've mostly worked in C#, so I now feel more comfortable there.

Here are the classes I created for Table1 & Table2:

public class Table1
{
    public int Table1Id { get; set; }
    public string FieldDescription { get; set; }
    public int OrderByColumn { get; set; }
}
public class Table2
{
    public int Table1Id { get; set; }
    public string FieldValue { get; set; }
    public int Table2WhereColumn1 { get; set; }
    public int Table2WhereColumn2 { get; set; }
}

Now the query in C# should be:

var query =
    from t1 in Table1
    join t2 in Table2 on t1.Table1Id equals t2.Table1Id into _Table2
    from _t2 in _Table2.DefaultIfEmpty()
    where _t2 == null ? true :
        _t2.Table2WhereColumn1 == @someId
        && _t2.Table2WhereColumn2 == @someOtherId
    orderby t1.OrderByColumn
    select new
    {
        t1.Table1Id,
        t1.FieldDescription,
        FieldValue = _t2 == null ? "" : _t2.FieldValue,
    };

And the translation into VB.NET:

Dim query = _
    From t1 In Table1 _
    Group Join t2 In Table2 On t1.Table1Id Equals t2.Table1Id Into _Table2 = Group _
    From _t2 In _Table2.DefaultIfEmpty() _
    Where If(_t2 Is Nothing, True, _t2.Table2WhereColumn1 = someId AndAlso  _
                                   _t2.Table2WhereColumn2 = someOtherId) _
    Order By t1.OrderByColumn _
    Select New With { _
            .Table1Id = t1.Table1Id, _
            .FieldDescription = t1.FieldDescription, _
            .FieldValue = If(_t2 Is Nothing, "", _t2.FieldValue) _
        }

Let me know if they work. Fingers crossed. :-)

Answer 3

Personally if there are where conditions for the right hand side of a left join I generally prefer to put them into the join criteria

In this case the SQL would look like:

SELECT t1.Table1Id,
       t1.FieldDescription, 
       t2.FieldValue
FROM Table1 t1 WITH (NOLOCK)
LEFT JOIN Table2 t2 WITH (NOLOCK) ON t1.Table1Id = t2.Table1Id 
                                  AND t2.Table2WhereColumn1 = @someId
                                  AND t2.Table2WhereColumn2 = @someOtherId
ORDER BY t1.OrderByColumn

The LINQ code for this (in C#) would look like:

var query =
    from t1 in Table1
    join t2 in Table2 on new{a = t1.Table1Id, b = someId, c = someotherId} 
                         equals new {a = t2.Table1Id b = t2.Table2WhereColumn1, c = Table2WhereColumn2} 
    into _Table2
    from _t2 in _Table2.DefaultIfEmpty()
    orderby t1.OrderByColumn
    select new
    {
        t1.Table1Id,
        t1.FieldDescription,
        FieldValue = _t2 == null ? "" : _t2.FieldValue,
    };

not tested it - but should work