LINQ Left Join with multiple ON OR conditions

Question

I'm sorry for telling that I've a little bit weak on LINQ, I always do write SQL query before I start working on the complicated LINQ.

I want to ask that how to convert this SQL Query into LINQ with LEFT JOIN with multiple ON conditons with the OR operator.,

m.MerchandiseId will be use for twice in ON condition

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();

The bottom LINQ Query I've write for it. However, I just can work on the left join with multiple ON clause in LINQ. Appreciate if someone would help me on this. Thanks!

Answer 1

I don't believe Linq supports the use of the OR operator with multiple columns, but that said, I wouldn't use OR even in SQL as it makes the join's intention unclear and it also obscures where the data originated from - it also isn't immediately clear what happens if there are multiple matches for each column. Instead I would JOIN twice on the different columns and let the projection-clause handle it:

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

This can then be translated into Linq using the LEFT OUTER JOIN approach ( How to implement left join in JOIN Extension method )

Note that if you're using Entity Framework then you don't need to worry about doing any of this at all! Just use 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 );