c# - 具有多个 ON OR 条件的 LINQ Left Join

Question

很抱歉告诉你我在 LINQ 方面有点弱，我总是在开始处理复杂的 LINQ 之前编写 SQL 查询。

我想问一下，如何使用 LEFT JOIN 和多个 ON 条件使用 OR 运算符 将此 SQL 查询转换为 LINQ。,

m.MerchandiseId 将在 ON 条件下使用两次

SELECT
    *
FROM
    Inbox AS i
    INNER JOIN [User] AS u ON i.FromUserId = u.UserId
    LEFT OUTER JOIN Merchandise AS m ON
        u.MerchandiseId = m.MerchandiseId
        OR
        i.ToMerchantId = m.MerchandiseId
WHERE
    i.ToCompanyId = 10
    OR
    i.FromCompanyId = 10



var message = (from i in db.Inbox
               join u in db.User on i.FromUserId equals u.UserId
               join m in db.Merchandise on u.MerchandiseId equals m.MerchandiseId //here I want to ON i.MerchantId = m.MerchandiseId, but it doesn't allow
                where i.ToCompanyId == user.CompanyId || i.FromCompanyId == user.CompanyId
                orderby i.CreatedAt descending
                group m.MerchandiseId by new { m.MerchandiseId, m.MerchandiseName } into grp
                select new
                {
                       MerchandiseId = grp.Key.MerchandiseId,
                       MerchandiseName = grp.Key.MerchandiseName,
                       InboxMessage = (from e in db.Inbox
                                        join us in db.User on e.FromUserId equals us.UserId
                                         join mer in db.Merchandise on us.MerchandiseId equals mer.MerchandiseId
                                          where mer.MerchandiseId == grp.Key.MerchandiseId
                                          orderby e.CreatedAt descending
                                          select e.InboxMessage).FirstOrDefault(),
                       CreatedAt = (from e in db.Inbox
                                    join us in db.User on e.FromUserId equals us.UserId
                                    join mer in db.Merchandise on us.MerchandiseId equals mer.MerchandiseId
                                     where mer.MerchandiseId == grp.Key.MerchandiseId
                                     orderby e.CreatedAt descending
                                     select e.CreatedAt).FirstOrDefault(),
                                       }).ToList();

我为此编写的底部 LINQ 查询。但是，我只能在 LINQ 中使用多个 ON 子句处理左连接。感谢有人会帮助我。谢谢!

Answer 1

我不相信 Linq 支持对多列使用 OR 运算符，但是也就是说，即使在 SQL 中我也不会使用 OR连接的意图不明确，它也模糊了数据的来源——如果每列有多个匹配项，也不会立即清楚会发生什么。相反，我会在不同的列上 JOIN 两次，然后让投影子句处理它:

SELECT
    *
FROM
    Inbox
    INNER JOIN [User] AS u ON i.FromUserId = u.UserId
    LEFT OUTER JOIN Merchandise AS userMerchant ON u.MerchandiseId = userMerchant.MerchandiseId
    LEFT OUTER JOIN Merchandise AS inboxMerchant ON Inbox.ToMerchantId = inboxMerchant .MerchandizeId
WHERE
    Inbox.ToCompanyId = 10
    OR
    Inbox.FromCompanyId = 10

然后可以使用 LEFT OUTER JOIN 方法将其转换为 Linq (How to implement left join in JOIN Extension method)

请注意，如果您使用的是 Entity Framework，那么您根本不需要担心执行任何这些操作!只需使用 Include:

var query = db.Inbox
    .Include( i => i.User )
    .Include( i => i.User.Merchandise )
    .Include  i => i.Merchandise )
    .Where( i => i.ToCompanyId = 10 || i.FromCompanyId == 10 );