How to query on groups of predicate in EF Core

Question

I want to apply a number of composite filters to a group of data like this:

Filter[] filters = new[] { new Filter { Name = "Bob", Gender = "Male"  },
new Filter { Name = "Alice", Height = "Female" } };

_dbContext.People.Where(p => filter.Any(f => f.Name == p.Name && f.Gender == p.Gender)).Select(p => p.Id);

I'm interested in the Ids of Male Bobs, and Female Alices. Sorry Female Bobs. I don't want you.

This is the correct way of solving that problem in memory Linq, but there's a problem. This is what the SQL EF generates looks like (I'm checking this in my SQL server profiler)

SELECT [p].[Name], [p].[Gender], [p].[Id] FROM [People] AS [p]

This is terrible. It digs up everything and then does the actual work in memory. There is no way this will work with a lot of people, it will grind to a halt.

Is there any way to make the generated sql look more like this?

SELECT 
[Person].[Id]
FROM [Person] 
WHERE 
   ((([Person].[Name] = "Bob") AND ([Person].[Gender] = "Male")) 
   OR (([Person].[Name] = "Alice") AND ([Person].[Gender] = "Female")))

(as is possible in Dapper)

You could try it with a [predicate builder](https://stackoverflow.com/questions/51720429/predicate-build-with-net-core-and-ef-core) — stuartd, May 13 '19 at 15:11
Hey isn't it having an error, it should be _dbContext.People.Where(p => filter.Any(f => f.Name == p.Name && f.Gender == p.Gender)).Select(p => p.Id); — Code Name Jack, May 13 '19 at 15:12

score 3 · Answer 1 · answered May 13 '19 at 15:28

The structure of your query suggests that the number of name/sex combinations could be more than two. In that case it might make more sense to write your own stored procedure instead of letting EF create the query.

In this case I would use a table-valued parameter to pass a set of rows as parameters to the stored procedure. Each row contains both a name and a gender. The query joins that table parameter to your Person table, returning rows where the names and genders both match.

score 2 · Accepted Answer · answered May 13 '19 at 16:03

Here's what I did in the end, as @stuartd suggested:

var predicate = PredicateBuilder.New<Person>();
foreach (var filter in filters)
{
    predicate = predicate.Or(p =>
       p.Gender == filter.Gender &&  filter.Name == p.Name));
}

people = _dbContext.People.Where(predicate).Select(r => r.Id).Distinct().ToArrayAsync();

Works like a charm. Thanks.

score 1 · Answer 3 · answered May 13 '19 at 15:37

In this particular situation (if you only have small number of filters) I'd suggest splitting the query into explicit expression, like

var maleNameFilter = "Bob";
var femaleNameFilter = "Alice";
_dbContext.People.Where(p => 
    (p.Name == maleNameFilter && p.Gender == "Male") 
    || (p.Name == femaleNameFilter && p.Gender == "Female")
).Select(p => p.Id);

However I suspect that you would want to use an extensive number of filters, in which case it becomes tougher with LINQ. As already suggested in some comments, you can use Predicate Builder for this (see example).

Finally, if you want maximum performance at a cost of a bit more complexity, you might consider putting your filter values into a separate table in the database and rewriting your query using Join().

How to query on groups of predicate in EF Core

3 Answers3