Query that matches multiple serverside (C#) values

Question

I would like to write a LINQ 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:

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

2. 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.

Answer 1

EF Core supports only Contains with local collection, but there is 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;