語境

Itzik Ben-Gan所著的《T-SQL Fundamentals Third Edition》一書在第 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';

請注意，join 子句有兩個條件：

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

其他帖子中展示的技術

以下帖子（以及其他帖子）演示了如何將多個條件與 JOIN

CS1941：join 子句中的一個運算式的型別不正確。呼叫“GroupJoin”時型別推斷失敗。

鏈接到 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:

uj5u.com熱心網友回復：

將Key2not-so-join-but-a-side-filter 部分取出來where看起來像這樣：

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
};

uj5u.com熱心網友回復：

只需使用過濾的包含：https : //docs.microsoft.com/en-us/ef/core/querying/related-data/eager#filtered-include

uj5u.com熱心網友回復：

查理臉的建議

這是一種基于 Charlieface 在上述評論中的建議的方法。它確實似乎有效！

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
    };

使用以下命令輸出結果：

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"));
}

顯示以下內容：

 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

標籤：c# entity-framework linq tsql entity-framework-core

上一篇：將引數傳遞給window.location.href失敗

下一篇：如何更改此ruby??匹配行以回傳true或false