EF Core 5 Contains with several columns

Question

I have two models that look something like this:

public class Filter
{
    public string Key {get;set;}
    public Guid ProgramId {get;set;}
}

public class MyEntity
{
    public string Name {get;set;}
    public string Key {get;set;}
    public Guid ProgramId {get;set;}
}

I want to fetch all MyEntity where Key and ProgramId are represented in a list at least once. The combination of Key and ProgramId is crucial, since both Key and ProgramId can exist in different Filters in different coalitions. Therefore both Key and ProgramId must be a match.

My code looks something like this:

// Initiation of filters is obviously different, but it could look something like this
var filters = new []
{
    new Filter { Key = "1", ProgramId = Guid.NewGuid() },
    new Filter { Key = "2", ProgramId = Guid.NewGuid() }
}

I have tried the following. All examples gives the result I want if they are evaluated in-memory, but no one can be executed/translated as SQL.

Alt 1. Based on this question:

var concatFilters = filters.Select(x => x.Key + "_" + x.ProgramId).ToArray();
var result = myContext.MyTable.Where(x => concatFilters.Contains(x.Key + "_" +  x.ProgramId));
// It gives: Arithmetic overflow error converting expression to data type nvarchar.

Alt 2:

var filterPrograms = filters.Select(x => new { Key = x.Key, ProgramId = x.ProgramId }).ToArray();  
var query = myContext.MyTable.Where(x => filterPrograms.Contains( new { Key = x.Key, ProgramId = x.ProgramId} ));
// It cannot be translated.

Alt 3:

var query = myContext.MyTable.Where(x => filters.Any(y => y.Key == x.Key && y.ProgramId == x.ProgramId));
// It cannot be translated.

I use paging and need this to be evaluated in SQL for my paging to work correctly. I'm running .NET 5 with EF Core 5.0.6. Any ideas?

Answer 1

Use this my answer for extension method FilterByItems. Then you can do the following:

var filters = new []
{
    new Filter { Key = "1", ProgramId = Guid.NewGuid() },
    new Filter { Key = "2", ProgramId = Guid.NewGuid() }
};

var result = myContext.MyTable
    .FilterByItems(filters, (x, f) => x.Key == f.Key && x.ProgramId == f.ProgramId, true);