sql - Linq 中的多列检查

Question

请指导在 linq 中编写以下 sql 查询

SELECT DISTINCT
    Item.Id AS ItemId, Item.CatId, [Case].Id AS CaseId
    FROM [Item] WITH (NOLOCK)
    LEFT OUTER JOIN [Case] WITH (NOLOCK) ON [Case].Id = Item.CaseId
    LEFT OUTER JOIN [Cust] WITH (NOLOCK) ON [Case].CustId1 = Cust.Id OR 
       [Case].CustId2 = Cust.Id

我不明白如何在联接中包含 CustId1 和 CustId2。

下面是我没有 CustId2 的 linq：

 var query = (from i in db.Item
              join c in db.Case on i.CaseId equals c.Id
              into joined
              from c in joined.DefaultIfEmpty()
              join cus in db.Cust on c.CustId1 equals cus.Id
              into cust

              from cus in cust.DefaultIfEmpty()
              select new
              {
                  Item=i,
                  Case = c,
                  Category = ct,
                  Pro = pr,
                  ZCatClass =zcc,
                  ZCatTyp = cct,
                  cust = cus
              }).OrderByDescending(d => d.Item.Id).ToList();

标签： sqlsql-serverlinq

Answer 1

var query = (from i in Items
    from c in Cases.Where(z => z.Id == i.CaseId).DefaultIfEmpty()
    from cust in Custs.Where(z => z.Id == c.CustId1 || z.Id == c.CustId2).DefaultIfEmpty()
    select new
    {
        ...
    }).OrderByDescending(z => z.Item.Id).ToList();