2

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.

Vassi
  • 780
  • 5
  • 14
  • 1
    Any predicate builder helper would do the OR job - like [this](https://petemontgomery.wordpress.com/2011/02/10/a-universal-predicatebuilder/), [this](https://stackoverflow.com/questions/36246162/establish-a-link-between-two-lists-in-linq-to-entities-where-clause/36247259#36247259), even the [classical](http://www.albahari.com/nutshell/predicatebuilder.aspx) etc. – Ivan Stoev Feb 26 '19 at 17:16
  • 1
    Derp. I've used one of these before but I kind of just assumed it wouldn't work with core for some reason. I will give this a go, thanks. – Vassi Feb 26 '19 at 18:02
  • 1
    So, follow up, using this worked - technically - but EF Core complains it can't evaluate this and does the filtering locally instead of on the database. I'm not sure this is technically a bad thing - for me - but it doesn't really solve my problem as posed. – Vassi Feb 26 '19 at 18:54
  • Yeah, client evaluation is one of the EF Core pitfalls - technically it "works", but practically it doesn't. What is the type of `item.UserCases` ? Can we see the sample entity model? Also, if you know how to that with raw SQL, then do it with raw SQL - `db.UseCasesFromSql("SELECT * FROM … WHERE ...)` returns `IQueryable` which is [composable](https://learn.microsoft.com/en-us/ef/core/querying/raw-sql#composing-with-linq) - you can add `Include`, and virtually any normal LINQ query operator. – Ivan Stoev Feb 26 '19 at 19:08
  • You could use a predicate builder, but there might be an easier way if you clarified the question, What is X and Y in this case, `query.Where(item => item.cases.Contains(x) || item.cases.Contains(y))` – johnny 5 Feb 26 '19 at 19:27
  • 1
    x and y are ints, sorry I wasn't sure it super mattered in this case because the situation would be the same if the were strings. @IvanStoev the FromSql actually works ok, if not how I expected, I will make another update! – Vassi Feb 26 '19 at 20:10

1 Answers1

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.

Vassi
  • 780
  • 5
  • 14