Question: .Where with a list of expressions

[buehler]

I've been working on a project where I use entity framework core 3.0. Now I have a table with a list of elements that contain two columns which must be matched together. In sql, this query would be super easy, but in expressions, every solution I tried resulted in "Expression cannot be translated".

Since this table has 5 million records in it, it's not an option to load them (as mentioned by EFCore) into the memory and filter them in code.

The SQL I try to achieve would look like:

select *
from "mytyble"
where ("IndicatorId" = 2 AND "ProductId" = 1)
   OR ("IndicatorId" = 3 AND "ProductId" = 1)
   OR ("IndicatorId" = 4 AND "ProductId" = 1)

Is this somehow possible with linq expressions or do I need to prepare the sql statement in code for myself?

Further technical details

EF Core version: Microsoft.EntityFrameworkCore" Version="3.0.0"
Database provider: Npgsql.EntityFrameworkCore.PostgreSQL
Target framework: .net core 3
Operating system: Windows 10
IDE: jetbrains rider

[buehler]

And yes, I'm sorry that I used "bug" here. Sadly I haven't found a place to put this question :-(

[roji]

Can you please post more information, specifically the LINQ query you tried to use and got an exception on? Without any further information, there's no reason why you wouldn't be able to use regular boolean logic in C# with LINQ and have that translated to SQL.

[smitpatel]

Related #11799

[buehler]

@roji Of course.

So I have a dynamic list of this combinations, which is why I can't just use boolean c# logic.

public class Selector 
{
    public int Id1 {get;set;}
    public int Id2 {get;set;}
}

public class SubModel
{
    public int Id {get;set;}
    public int Id1 {get;set;}
    public int Id2 {get;set;}
}

public class Model 
{
    public ICollection<SubModel> SubModels {get;set;} = new List<SubModel>();
}

public async Task<IEnumerable<Model>> GetModels(IList<Selector> selectors)
{
    var query = context.Models.AsQueryable();
    query = query.Where(m => m.SubModels.Any(sub => selectors.Any(s => s.Id1 == sub.Id1 && s.Id2 == sub.Id2));
    var result = await query.ToListAsync();
}

I tried it with various approaches: from the models perspective, from the sub models perspective, etc.

But I guess, this is really related as @smitpatel said.
Since the related element is open, and I can solve my problem with FromSqlRaw it's fine for the moment :-)