Linq query with multiple OR conditions in multiple joins-CodePudding

I have gone through a number of answers but haven't found anything that helps me with this. I understand that the "OR" part should happen in the Where clause, not the join itself, but unfortunately that doesn't look like a possibility for my query.

With these two tables, drastically simplified for this question:

Claim: FKeyID int, CKeyID int, ICode varchar(100), IName varchar(100)
Lookup: LFKeyID int, LCKeyID int, LICode varchar(100), LIName varchar(100)

I need to do the following join in linq method syntax:

select * from Claim c
left join Lookup l on 
(l.LFKeyID = c.FKeyID OR l.LFKeyID = 0)
AND (l.LCKeyID = c.CKeyID OR l.LCKeyID = 0)
AND (l.LICode = c.ICode OR l.LIName = c.IName)

I've tried the following, but it's only joining on the initial key and doesn't have the OR clause for the lookup key = 0. I'm completely stumped.

query = _context.Claim.Join(_context.Lookup, 
clm => clm.FKeyID, lk => lk.LFKeyID, (clm, lk) => new 
{ ... fields go here ... })
.DefaultIfEmpty().ToList();

In case anyone else is trying to accomplish this using method syntax, here is the solution thanks to the comment given:

var query = _context.Claim.Where(i => (various initial filter criteria))
.SelectMany(clm => _context.Lookup, (clm, lk) = new { clm, lk })
.Where(lk => (lk.LFKeyID == clm.FKeyID || lk.LFKeyID == 0)
            && (lk.LCKeyID == clm.CKeyID || lk.LCKeyID == 0)
            && (lk.LICode == clm.ICode || lk.LIName == clm.IName))
        .DefaultIfEmpty()
 .Select(s => new 
{
// list of fields
}).ToList();

CodePudding user response：

You can use other technique for LEFT JOIN described in Complex Query Operators

var query = 
    from clm in _context.Claim
    from lk in _context.Lookup
        .Where(lk => (lk.LFKeyID == clm.FKeyID || lk.LFKeyID == 0)
            && (lk.LCKeyID == clm.CKeyID || lk.LCKeyID == 0)
            && (lk.LICode == clm.ICode || lk.LIName == clm.IName))
        .DefaultIfEmpty()
    select new 
    {
        clm, // or specify fields precisely
        lk
    };