Combine Expressions instead of using multiple queries in Entity Framework

asp.net c# entity-framework expression-trees iqueryable

Question

The general queryable I'm using is as follows (and it could already have choices made):

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

There is also theProvider a class that resembles this:

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

    [...]
}

The Condition could be defined in the following way for each instance:

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

I now wish to choose all.Provider situations that have aCondition that is satisfied by at least one member of theDBSet :

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

The issue with this is that I'm beginning a query for each.Provider On the list, an instance. If I could get the same result with only one query, I would like to. Due to dubious performance, this subject is very crucial. How can I use a single query to get the same results and boost performance?Linq declarations orExpression Trees ?

1
11
8/24/2016 9:53:18 AM

Popular Answer

Interesting obstacle. I can only imagine building dynamically as a solution.UNION ALL anything 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]

then use the numbers that were returned as an index to find the matched suppliers.

Possibly 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 didn't have to use to build the query.Expression by substituting the above withSelect with

.Select(_ => index)

However, doing so would add pointless SQL query parameters for each index.

5
8/18/2016 12:03:43 PM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow