how to write LINQ left outer join in method syntax?

Question

We have a complicated scenario where any of three tables, QRKeys, Tags and Docs, might or might not have data.

If Docs has data, then Devices does too.

We have it working as an inner join.

Now we're trying to extend it to a GroupJoin to handle missing tags.
Would upgrading to EF Core 7 help?

public static DeviceSummaryModel? GetDeviceSummary(string qrKey) 
{
    return context.QRKeys
                  .GroupJoin(context.Tags, qr => qr.TagID, 
                             tg => tg.ID, (qr, tg) => new { qr, tg })
                  .Join(context.Documents, qt => qt.qr.DocID, dc => dc.ID, (qt, dc) => new { qt.qr, qt.tg, dc })
                  .Join(context.Devices, qtd => qtd.dc.DeviceID, dv => dv.ID, (qtd, dv) => new { qtd.qr, qtd.tg, dv })
                  .Where(x => x.qr.QRKey == qrKey)
                  .SelectMany(x => x.tg.DefaultIfEmpty(new TagModel()),
                              (x, tg) => new DeviceSummaryModel 
                                         {
                                             QRKey = x.qr.QRKey,
                                             TagNumber = tg.TagNumber,
                                             Type = x.dv.Type,
                                             Vendor = x.dv.Vendor,
                                             Model = x.dv.Model
                                         })
                  .FirstOrDefault();
}

System.InvalidOperationException : The LINQ expression 'DbSet() .GroupJoin( inner: DbSet(), outerKeySelector: qr => qr.TagID, innerKeySelector: tg => tg.ID, resultSelector: (qr, tg) => new { qr = qr, tg = tg })' could not be translated. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to 'AsEnumerable'

Working version for inner join:

return context.QRKeys
              .Join(context.Tags, qr => qr.TagID, tg => tg.ID, (qr, tg) => new { qr, tg })
              .Join(context.Documents, qt => qt.qr.DocID, dc => dc.ID, (qt, dc) => new { qt.qr, qt.tg, dc })
              .Join(context.Devices, qtd => qtd.dc.DeviceID, dv => dv.ID, (qtd, dv) => new { qtd.qr, qtd.tg, dv })
              .Where(x => x.qr.QRKey == qrKey)
              .Select(x => new DeviceSummaryModel 
                           {
                                QRKey = x.qr.QRKey, // or m.ppc.pc.ProdId
                                TagNumber = x.tg.TagNumber,
                                Type = x.dv.Type,
                                Vendor = x.dv.Vendor,
                                Model = x.dv.Model
                           })
              .FirstOrDefault();

From the [EF Core documentation regarding GroupJoin](https://learn.microsoft.com/en-us/ef/core/querying/complex-query-operators#groupjoin) _"That's why EF Core doesn't translate GroupJoin."_ Feels like you're out of luck with `GroupJoin` and EF Core. — phuzi, Aug 31 '23 at 23:11
As mentioned in the duplicate target, `GroupJoin` translation is supported in EFC 7.0, so upgrading definitely would help. Otherwise, just use the equivalent `Select` with correlated subquery, or (preferable) collection navigation property if you have so (as you should). All pure LINQ join patterns are pure LINQ, and with proper navigation properties you don't need any of those. — Ivan Stoev, Sep 01 '23 at 06:16

score 0 · Answer 1 · answered Sep 01 '23 at 05:41

Still open to finding a way to do this in a single statement, but here's the best I've been able to do.
This iteration isn't too bad, since there is a single instance of DeviceSummaryModel returned. But it is going to get worse in the next round where I have to return a list of DeviceSummaryModel.

        QRModel? qr = deviceSummaryContext.QRKeys.Where(x => x.QRKey == qrKey).FirstOrDefault();
        if(qr == null) {
            throw new KeyNotFoundException(qrKey);
        }
        TagModel? tag = deviceSummaryContext.Tags
            .Where(x => x.ID == qr.TagID)
            .AsEnumerable()
            .FirstOrDefault(new TagModel());
        DocumentModel? doc = deviceSummaryContext.Documents
            .Where(x => x.ID == qr.DocID)
            .AsEnumerable()
            .FirstOrDefault(new DocumentModel());
        DeviceModel? dev = deviceSummaryContext.Devices
            .Where(x => x.ID == doc.DeviceID)
            .AsEnumerable()
            .FirstOrDefault(new DeviceModel());
        return new DeviceSummaryModel {
            QRKey = qr.QRKey,
            TagNumber = tag.TagNumber,
            Type = dev.Type,
            Vendor = dev.Vendor,
            Model = dev.Model
        };

I love the new FirstOrDefault with customizable default. Works great so long as you AsEnum first since the DB can't handle it.

how to write LINQ left outer join in method syntax?

1 Answers1