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!

@Sherlock It isn't a duplicate, that Linq query you linked to has multiple `AND`, not `OR`. — Dai, Nov 23 '16 at 07:00
@Dai i didn't said its a duplicate santosh singh commented it not me :) dont give me a heart attack, And the link, let him figure out what is happening so he can learn, we need to get our hands dirty in order to learn not by spoonfeeding — Anonymous Duck, Nov 23 '16 at 07:01
@Sherlock check out my query properly, it doesn't duplicate as I cannot found a lot of solution in stackoverflow and I just posted it to here. — jefferyleo, Nov 23 '16 at 07:02
@jefferyleo i didn't said its a duplicate santosh singh said it ( and his comment is already deleted). The link I provided serves as a hint for you where to start. — Anonymous Duck, Nov 23 '16 at 07:03
@Sherlock thanks by the way, but my condition a little bit different from others, my ON condition is with 3 different columns — jefferyleo, Nov 23 '16 at 07:04
Please never just post SQL and ask for conversion. At least show a class model so navigation properties and the multiplicity of associations are visible. Also, tell what type of LINQ you're targeting (to entities?), *and* show your own first efforts. They clarify more to us than you might think. — Gert Arnold, Nov 23 '16 at 08:28
@GertArnold Thanks for your advice, I've already added my code that I've try to write on linq. — jefferyleo, Nov 23 '16 at 08:38
Still can't see multiplicity (`Inbox` - `User` seems to be n:1? Etc.), still no idea which type of LINQ it is. Still no idea whether navigation properties exists (which a class model would have revealed). — Gert Arnold, Nov 23 '16 at 10:55

score 4 · Answer 1 · edited May 23 '17 at 11:45

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

+one for the EF part, I didn't know this :). Im also excited to see if you can translate it using linq query or linq extensions without EF — Anonymous Duck, Nov 23 '16 at 07:17
Yup, same goes to @Sherlock, if you could convert it to linq query it would be best — jefferyleo, Nov 23 '16 at 07:38
@Sherlock It's possible, but I'm too tired to do it right now, I'll give it a stab tomorrow for the intellectual challenge. Note that I always prefer Extension Method-syntax over "native" Linq. — Dai, Nov 23 '16 at 08:18

LINQ Left Join with multiple ON OR conditions

1 Answers1