EF Core and LINQ to SQL that matches multiple serverside (C#) values-CodePudding

I would like to write a LINQ to SQL query using EF Core that would get results from multiple matching columns in a SQL table based on an array of serverside data that I would provide for matching. SQL query should look/do something like this:

SELECT * FROM (VALUES ('a', 'one'), ('b', 'two'), ('c', 'three')) AS myserverdata (TransactionId, OrderId)
join ReportExecution re ON myserverdata.OrderId = re.OrderId AND myserverdata.TransactionId = re.TransactionId

Is this even possible?

I had few attempts where all end up crashing on generaing a SQL from expression:

using join

var query =
    from execution in context.ReportExecutions
    join candidate in insertCandidates // serverside array of data I'd like to match in SQL
        on new {execution.OrderId, execution.TransactionId} equals new{candidate.OrderId, candidate.TransactionId}
        select execution;

return query.ToListAsync();

using Contains and similarly with .Any (this would work for array of strings and then generate WHERE IN (...), but I can't pull it off for matching multiple columns.

var keys = candidates.Select(x => new { x.TransactionId, x.OrderId });
return context.ReportExecutions
    .Where(rme => keys.Contains(new { rme.TransactionId, rme.OrderId } ))
    .ToListAsync();

Thanks for the feedback.

CodePudding user response：

EF Core supports only Contains with local collection, but there are workaround. You can use my function FilterByItems and rewire query in the following way:

var query =
    from execution in context.ReportExecutions
        .FilterByItems(insertCandidates, (e, c) => e.OrderId == c.OrderId && e.TransactionId == c.TransactionId, true)
    select execution;

CodePudding user response：

You mentioned trying Any, but did not show the attempt above. Does this solve the problem?

var results = context.ReportExecutions
    .Where(rme => keys
        .Any(key => 
            rme.TransactionId == key.TransactionId && 
            rme.OrderId == key.OrderId
        )
    )
    .ToListAsync();