Composing an OR query with EF Core

Question

I have a DbQuery that I'm trying to filter on based on various (optional) parameters provided by a user. I'm trying to compose the query with LINQ to account for this but have hit a snag, so this is kind of a two parter.

Postgres: I'm using postgres so I have an array column, and I want to be able to basically do useCaseArray && entity.useCases. However, the EF provider currently doesn't support this.

I don't want to drop down to composing the whole thing in raw sql if I can avoid it so I thought I could do a very ugly WHERE like this:

WHERE (useCases.Contains(x) || useCases.Contains(y) ...)

however I don't know how to compose that with LINQ. I know you can do ORs inline, such as

query.Where(item => item.cases.Contains(x) || item.cases.Contains(y))

However I can't write it that way because I need to foreach/loop over the array containing my Xs and Ys. Does anyone know how I can do this?

foreach(var usecase in request.UseCases) 
{
  query = query.Where(item => item.UseCases.Contains(usecase));
}

This will generate just a long series of ANDs when what I want is that whole subset to be one OR.

I hope I've managed to explain this appropriately! Alternately I'd love to be able to inject a single WHERE clause component in raw SQL but I think that would cause EF Core to explode and it sounds like doing a FromSQL doesn't support WHERE just SELECT.

UPDATE:

Based on comment I tried this: https://petemontgomery.wordpress.com/2011/02/10/a-universal-predicatebuilder/ which works largely fantastically:

var useCaseQuery = request.UseCases
  .Select(useCase => PredicateBuilder.Create<MyEntity>(entity => entity.UseCases.Contains(useCase)))
  .Aggregate(PredicateBuilder.Or);

query = query.Where(useCaseQuery);

This is great, in a way, but EF Core still does not like it:

The LINQ expression 'where ({[assumption].UseCases => Contains(__useCase_3)} OrElse {[assumption].UseCases => Contains(__useCase_4)})' could not be translated and will be evaluated locally.

I think this will be fine for me, but the original problem still stands, I'd like this to run on the DB.

Answer 1

So after some helpful comments I tried FromSql

if (request.UseCases != null && request.UseCases.Count > 0)
{
  // I know UseCases is a List<int> hence why I'm just joining without escaping.
  query = query.FromSql("SELECT * FROM my_table WHERE ARRAY[" + String.Join(',', request.UseCases) + "] && use_cases");
}

I don't 100% like this because now my service knows about my database table and field names which pretty much invalidates the purpose of the ORM. However for this one specific case, it does what I need, and I can continue to compose like so:

if (request.Groups != null && request.Groups.Count > 0)
{
  query = query.Where(data => request.Groups.Contains(data.GroupId));
}

if (!String.IsNullOrWhiteSpace(request.Title))
{
  query = query.Where(data => EF.Functions.ILike(data.Name, $"%{request.Title}%"));
}

The SQL produced is this:

SELECT my_table.classes, my_table."group", my_table.group_id, my_table.id, my_table.name, my_table.use_cases
FROM (
    SELECT * FROM my_table WHERE ARRAY[7,4] && use_cases
) AS assumption
WHERE my_table.group_id IN (7) AND (my_table.name ILIKE @__Format_3 ESCAPE '' = TRUE)
ORDER BY my_table.name DESC

Until the Postgres EF Core driver is updated this might be the best I can do.