3

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?

smoksnes
  • 10,509
  • 4
  • 49
  • 74
  • I did this recently using a custom expression which basicly made a huge `(y.Key == x.Key && y.ProgramId == x.ProgramId) OR (y.Key == x.Key && y.ProgramId == x.ProgramId)` statement for each of the items I wanted to check (in your case `filters`). I don't have the time to write up a proper answer right now but that might give you an idea at least. – Karl-Johan Sjögren Jun 11 '21 at 09:23
  • the most correct looks like Alt 1. question have you tired... `var result = myContext.MyTable.Where(x => concatFilters.Contains(x.Key + "_" + x.ProgramId.ToString()));` or `var result = myContext.MyTable.Where(x => concatFilters.Contains((x.Key + "_" + x.ProgramId.ToString())));` – Seabizkit Jun 11 '21 at 09:39
  • also try https://stackoverflow.com/questions/21257251/convert-int-to-string-in-linq-for-searching which would be like `concatFilters.Contains(x.Key + "_" + (string)x.ProgramId));` or `concatFilters.Contains(x.Key + "_" + SqlFunctions.StringConvert(x.ProgramId)));` one of those should be the answer... let me know which one and i will post as answer. – Seabizkit Jun 11 '21 at 09:47

1 Answers1

2

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);
Svyatoslav Danyliv
  • 21,911
  • 3
  • 16
  • 32