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
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 docs.microsoft.com:
Here's the solution text which goes into the subtleties behind the join and why where
isn't applicable in this case:
CodePudding user response:
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
};
CodePudding user response:
Simply use a filtered include: https://docs.microsoft.com/en-us/ef/core/querying/related-data/eager#filtered-include
CodePudding user response:
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