Linq - Using a left join on to a table which may not have records

Question

Let's say I have 3 tables - 1 header and 2 detail:

Header Table

id | label
 1 | foo
 2 | bar

Detail 1 Table

id | date       | value
 1 | 2015-01-01 |     5

Detail 2 Table

id | date       | value
 1 | 2015-01-01 |     7
 2 | 2016-02-02 |    10

I want to make a linq query that joins all three, but does not eliminate data due to one detail table not having a record where the other one does. The result should look like:

Resulting Table

id | label | date       | value1 | value2
 1 | foo   | 2015-01-01 |      5 |      7
 2 | bar   | 2016-02-02 | <null> |     10

So, a null for value1, instead of the entire row being removed.

If I were writing SQL, I could write

select
    h.id,
    h.label,
    coalesce(d1.date, d2.date) as date,
    d1.value as value1,
    d2.value as value2
from
    header h
    left join detail1 d1
        on d1.id = h.id
    left join detail2 d2
        on d2.id = h.id
        and (
            d2.date = d1.date
            or d1.date is null
        )

Is it possible to write this using Linq? I'm using the "on new equals new " syntax, and I cannot figure out how to preserve the detail2 record when there is no matching detail1 record.

Edit: I feel like the linked answer only answers the left join portion of my question. I know I can left join in linq, but the detail2 table is joining on to both header (not a problem) and detail1. If detail1 doesn't have a record for a date in detail2, the detail2 record will not appear in the result. Using "select new{} equals new{}" doesn't allow me to use the detail2 object before the equals, so I can't write

from
    h in header.AsEnumerable()
join d1.AsEnumerable().DefaultIfEmpty()
    on p.Id equals d1.Id
join d2.AsEnumerable().DefaultIfEmpty()
    on new {
        Id = h["Id"],
        Date = d1["Date"] ?? d2["Date"], // Doesn't work, can't use d2 here.
    }                                    // d1 may not have a record, so there may not be a match
    equals new {
        Id = d2["Id"],
        Date = d2["Date"],
    }
select new {
    // etc...
}

Possible duplicate of [LEFT OUTER JOIN in LINQ](http://stackoverflow.com/questions/3404975/left-outer-join-in-linq) — Milney, May 09 '17 at 21:27
LINQ supports left joins. See .GroupJoin(inner, key, key, result) extension method used in lambda syntax, or .DefaultIfEmpty() used in classic linq. You can see the latter one in the [this answer in question "LEFT OUTER JOIN in LINQ"](http://stackoverflow.com/a/3413732/717732) - it's the `from p in ps.DefaultIfEmpty()` and that `ps` join will be translated into LEFT JOIN. — quetzalcoatl, May 09 '17 at 21:38
This isn't a simple left outer join because joins in LINQ are equijoins and this has a extra condition on the second `LEFT JOIN`. — NetMage, May 09 '17 at 22:14

score 3 · Accepted Answer · answered May 09 '17 at 22:08

To implement a join with arbitrary conditions, you need to use another from clause with a where to handle your condition. I am not sure if used with Linq to SQL what type of SQL will be produced, you may be better off with my FullOuterJoin/LeftOuterJoin IQueryable extensions.

var ans = from h in header
          join d1 in detail1 on h.id equals d1.id into hd1j
          from hd1 in hd1j.DefaultIfEmpty()
          from d2 in detail2 where h.id == d2.id && (hd1?.date == null || hd1.date == d2?.date)
          select new { h.id, h.label, date = hd1?.date ?? d2?.date, value1 = hd1?.value, value2 = d2?.value };

For my Enumerable testing, I put in the conditional operators. You should remove them if testing against IQueryable (e.g. Linq to SQL).

Awesome! I haven't gotten to test it in my actual code yet, but this looks like what I need. Thanks! — user1858704, May 09 '17 at 22:28

Linq - Using a left join on to a table which may not have records

1 Answers1