Combine Expressions instead of using multiple queries in Entity Framework

Question

I have following generic queryable (which may already have selections applied):

IQueryable<TEntity> queryable = DBSet<TEntity>.AsQueryable();

Then there is the Provider class that looks like this:

public class Provider<TEntity>
{
    public Expression<Func<TEntity, bool>> Condition { get; set; }

    [...]
}

The Condition could be defined per instance in the following fashion:

Condition = entity => entity.Id == 3;

Now I want to select all Provider instances which have a Condition that is met at least by one entity of the DBSet:

List<Provider> providers = [...];
var matchingProviders = providers.Where(provider => queryable.Any(provider.Condition))

The problem with this: I'm starting a query for each Provider instance in the list. I'd rather use a single query to achieve the same result. This topic is especially important because of questionable performance. How can I achieve the same results with a single query and improve performance using Linq statements or Expression Trees?

Answer 1

Interesting challenge. The only way I see is to build dynamically UNION ALL query like this:

SELECT TOP 1 0 FROM Table WHERE Condition[0]
UNION ALL
SELECT TOP 1 1 FROM Table WHERE Condition[1]
...
UNION ALL
SELECT TOP 1 N-1 FROM Table WHERE Condition[N-1]

and then use the returned numbers as index to get the matching providers.

Something like this:

var parameter = Expression.Parameter(typeof(TEntity), "e");
var indexQuery = providers
    .Select((provider, index) => queryable
        .Where(provider.Condition)
        .Take(1)
        .Select(Expression.Lambda<Func<TEntity, int>>(Expression.Constant(index), parameter)))
    .Aggregate(Queryable.Concat);

var indexes = indexQuery.ToList();
var matchingProviders = indexes.Select(index => providers[index]);

Note that I could have built the query without using Expression class by replacing the above Select with

.Select(_ => index)

but that would introduce unnecessary SQL query parameter for each index.