EF Core - multiple join conditions causing CS1941

Question

Context

The book T-SQL Fundamentals Third Edition by Itzik Ben-Gan contains the following query in chapter 3:

SELECT C.custid, C.companyname, O.orderid, O.orderdate
FROM Sales.Customers AS C
  LEFT OUTER JOIN Sales.Orders AS O
    ON O.custid = C.custid
    AND O.orderdate = '20160212';

Note that the join clause has two conditions:

O.custid = C.custid
AND 
O.orderdate = '20160212'

Techniques demonstrated in other posts

The following posts (among others) demonstrate how to use multiple conditions with a JOIN

LINQ to Entity : Multiple join conditions

LINQ Join with Multiple Conditions in On Clause

The issue

Based on the posts shared above, here's what I came up with for an EF Core version of the query:

var result =
    from customer in db.Customers
    join order in db.Orders
    on
    new
    {
        Key1 = customer.Custid,
        Key2 = true
    }
    equals 
    new
    {
        Key1 = order.Custid,
        Key2 = order.Orderdate == new DateTime(2016, 2, 12)
    }                    
    into Abc
    from abc in Abc.DefaultIfEmpty()
    select new
    {
        customer.Custid,
        customer.Companyname,
        Orderid = abc == null ? -1 : abc.Orderid,
        Orderdate = abc == null ? new DateTime() : abc.Orderdate
    };

However, with that code, a red squiggly comes up on join with the following message:

CS1941: The type of one of the expressions in the join clause is incorrect. Type inference failed in the call to 'GroupJoin'.

Link to the compiler error on learn.microsoft.com:

https://learn.microsoft.com/en-us/dotnet/csharp/language-reference/compiler-messages/cs1941?f1url=%3FappId%3Droslyn%26k%3Dk(CS1941)

Question

I've modeled my version after the examples shown in the other posts. So not sure what in my example is causing the issue.

What's a good way to setup the LINQ query for the given SQL call?

Thanks!

Notes

The above query is in a project available here if you'd actually like to run the query yourself:

https://github.com/dharmatech/TSqlEf/blob/master/Chapter3e7/Program.cs

See the project readme for how to setup the database:

https://github.com/dharmatech/TSqlEf

Cly's suggestion

Here's an approach based on Cly's answer which uses a where clause:

var result =
    from customer in db.Customers
    join order in db.Orders
    on customer.Custid equals order.Custid
    into Abc
    from abc in Abc.DefaultIfEmpty()
    where abc.Orderdate == new DateTime(2016, 2, 12)
    select new
    {
        customer.Custid,
        customer.Companyname,
        Orderid = abc == null ? -1 : abc.Orderid,
        Orderdate = abc == null ? new DateTime() : abc.Orderdate
    };

If I use the following to output the results:

foreach (var item in result)
{
    Console.WriteLine("{0} {1} {2}", item.Custid, item.Companyname, item.Orderid, item.Orderdate);
}

Console.WriteLine();
Console.WriteLine("{0} rows", result.Count());

I get the following:

48 Customer DVFMB 10883
45 Customer QXPPT 10884
76 Customer SFOGW 10885

3 rows

Whereas if I run the original SQL code in SSMS, I get 91 rows.

The exercise description in the book also states that 91 rows are expected. Here's the exercise text and expected output:

Here's the solution text which goes into the subtleties behind the join and why where isn't applicable in this case:

Answer 1

Getting out the Key2 not-so-join-but-a-side-filter part into where looks like this:

var result =
from customer in db.Customers
join order in db.Orders
on customer.Custid equals order.Custid into Abc
from abc in Abc.DefaultIfEmpty()
where abc.Orderdate == new DateTime(2016, 2, 12)
select new
{
    customer.Custid,
    customer.Companyname,
    Orderid = abc == null ? -1 : abc.Orderid,
    Orderdate = abc == null ? new DateTime() : abc.Orderdate
};

Answer 2

Simply use a filtered include: https://learn.microsoft.com/en-us/ef/core/querying/related-data/eager#filtered-include

Answer 3

Charlieface's suggestion

Here's an approach based on Charlieface's suggestion in a comment above. It does indeed appear to work!

var result =
    from customer in db.Customers
    join order in db.Orders
    on customer.Custid equals order.Custid
    into Abc
    from abc in Abc.Where(abc => abc.Orderdate == new DateTime(2016, 2, 12)).DefaultIfEmpty()
    select new
    {
        customer.Custid,
        customer.Companyname,
        Orderid = abc == null ? -1 : abc.Orderid,
        Orderdate = abc == null ? new DateTime() : abc.Orderdate
    };

Using the following to output the result:

foreach (var item in result)
{
    Console.WriteLine("{0,3} {1} {2,6} {3,10}", 
        item.Custid, 
        item.Companyname, 
        item.Orderid == -1 ? "NULL" : item.Orderid, 
        item.Orderid == -1 ? "NULL" : item.Orderdate.ToString("yyyy-MM-dd"));
}

shows the following:

 72 Customer AHPOP   NULL       NULL
 58 Customer AHXHT   NULL       NULL
 25 Customer AZJED   NULL       NULL
 18 Customer BSVAR   NULL       NULL
 91 Customer CCFIZ   NULL       NULL
 68 Customer CCKOT   NULL       NULL
 49 Customer CQRAA   NULL       NULL
 24 Customer CYZTN   NULL       NULL
 22 Customer DTDMN   NULL       NULL
 48 Customer DVFMB  10883 2016-02-12
 10 Customer EEALV   NULL       NULL
 40 Customer EFFTC   NULL       NULL
 85 Customer ENQZT   NULL       NULL
 82 Customer EYHKM   NULL       NULL
 79 Customer FAPSM   NULL       NULL
 17 Customer FEVNN   NULL       NULL
 37 Customer FRXZL   NULL       NULL
 33 Customer FVXPQ   NULL       NULL
 53 Customer GCJSG   NULL       NULL
 39 Customer GLLAG   NULL       NULL
 16 Customer GYBBY   NULL       NULL
  4 Customer HFBZG   NULL       NULL
  5 Customer HGVLZ   NULL       NULL
 42 Customer IAIJK   NULL       NULL
 34 Customer IBVRG   NULL       NULL
 63 Customer IRRVL   NULL       NULL
 73 Customer JMIKW   NULL       NULL
 15 Customer JUWXK   NULL       NULL
 50 Customer JYPSC   NULL       NULL
  3 Customer KBUDE   NULL       NULL
 21 Customer KIDPX   NULL       NULL
 30 Customer KSLQF   NULL       NULL
 55 Customer KZQZT   NULL       NULL
 71 Customer LCOUJ   NULL       NULL
 77 Customer LCYBZ   NULL       NULL
 66 Customer LHANT   NULL       NULL
 38 Customer LJUCA   NULL       NULL
 59 Customer LOLJO   NULL       NULL
 36 Customer LVJSO   NULL       NULL
 64 Customer LWGMD   NULL       NULL
 29 Customer MDLWA   NULL       NULL
  2 Customer MLTDN   NULL       NULL
 78 Customer NLTYP   NULL       NULL
 84 Customer NRCSK   NULL       NULL
  1 Customer NRZBB   NULL       NULL
 65 Customer NYUHS   NULL       NULL
 44 Customer OXFRU   NULL       NULL
 12 Customer PSNMQ   NULL       NULL
 47 Customer PSQUZ   NULL       NULL
 51 Customer PVDZC   NULL       NULL
 52 Customer PZNLA   NULL       NULL
 56 Customer QNIVZ   NULL       NULL
  8 Customer QUHWH   NULL       NULL
 67 Customer QVEPD   NULL       NULL
 45 Customer QXPPT  10884 2016-02-12
  7 Customer QXVLA   NULL       NULL
 60 Customer QZURI   NULL       NULL
 19 Customer RFNQC   NULL       NULL
  9 Customer RTXGC   NULL       NULL
 76 Customer SFOGW  10885 2016-02-12
 69 Customer SIUIH   NULL       NULL
 86 Customer SNXOJ   NULL       NULL
 88 Customer SRQVM   NULL       NULL
 54 Customer TDKEG   NULL       NULL
 20 Customer THHDP   NULL       NULL
 70 Customer TMXGN   NULL       NULL
 11 Customer UBHAU   NULL       NULL
 43 Customer UISOJ   NULL       NULL
 35 Customer UMTLM   NULL       NULL
 26 Customer USDBG   NULL       NULL
 13 Customer VMLOG   NULL       NULL
 80 Customer VONTK   NULL       NULL
 62 Customer WFIZJ   NULL       NULL
 27 Customer WMFEA   NULL       NULL
 14 Customer WNMAF   NULL       NULL
 61 Customer WULWD   NULL       NULL
 57 Customer WVAXS   NULL       NULL
 23 Customer WVFAF   NULL       NULL
 90 Customer XBBVR   NULL       NULL
  6 Customer XHXJV   NULL       NULL
 41 Customer XIIWM   NULL       NULL
 75 Customer XOJYP   NULL       NULL
 46 Customer XPNIK   NULL       NULL
 28 Customer XYUFB   NULL       NULL
 89 Customer YBQTI   NULL       NULL
 31 Customer YJCBX   NULL       NULL
 81 Customer YQQWW   NULL       NULL
 74 Customer YSHXL   NULL       NULL
 32 Customer YSIQX   NULL       NULL
 87 Customer ZHYOS   NULL       NULL
 83 Customer ZRNDE   NULL       NULL

91 rows