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)
Accepted Answer
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.
Popular Answer
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.
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow