Build Expression to filter data EF Core

Question

I need to reuse available expression:

Expression<Func<Picture, int>> selector = o => o.EntityId;

And build expression for Where:

Expression<Func<Picture, bool>> filter = w => w.EntityId > 5;

How can I build such an expression?

Next operation won't be executed on the client side, am I right?

var collection = _dbContext.Pictures.Where(filter).ToList();

Resistor

Usually, yes, you want to do so e.g. if you have complex search query. In general, to get a hint during coding, just hover the mouse over the Where : it should be IQueryable (basically saying that the query has not been materialized) , and the result of ToList should be IEnumerable, meaning that it will materialize the query the whole query that has been built. Note that sometimes, Entity Framework (or rather, the DB SQL adapter that is used) may not know how to translate some condition, but you should get a warning, sometimes during execution (so keep an eye on your console!). – Tynes

Answer 1

I finally figured out how to build an expression dynamically:

Expression<Func<Picture, int>> selector = o => o.EntityId;

var parameter = Expression.Parameter(typeof(Picture));

// get property name
if (!(selector.Body is MemberExpression memberExpression))
{
    memberExpression = ((UnaryExpression)selector.Body).Operand as MemberExpression;
}
var propertyName = memberExpression.ToString().Substring(2);

var expressionParameter = Expression.Property(parameter, propertyName);
var expressionBody = Expression.GreaterThan(expressionParameter, Expression.Constant(5, typeof(int)));

var filter = Expression.Lambda<Func<Picture, bool>>(expressionBody, parameter);
var collection = _dbContext.Pictures.Where(filter).ToList();

Generic example:

var filter = CreateFilter<Picture, int>(o => o.EntityId, 5);
var collection = _dbContext.Pictures.Where(filter).ToList();

private Expression<Func<TData, bool>> CreateFilter<TData, TKey>(Expression<Func<TData, TKey>> selector, TKey valueToCompare)
{
    var parameter = Expression.Parameter(typeof(TData));
    var expressionParameter = Expression.Property(parameter, GetParameterName(selector));

    var body = Expression.GreaterThan(expressionParameter, Expression.Constant(valueToCompare, typeof(TKey)));
    return Expression.Lambda<Func<TData, bool>>(body, parameter);
}

private string GetParameterName<TData, TKey>(Expression<Func<TData, TKey>> expression)
{
    if (!(expression.Body is MemberExpression memberExpression))
    {
        memberExpression = ((UnaryExpression)expression.Body).Operand as MemberExpression;
    }

    return memberExpression.ToString().Substring(2);
}

Thanks to David’s response about Prohibit client-side evaluation, I was able to verify that filtering doesn't executed on the client

Resistor

Answer 2

Configure your DbContext to prohibit client-side evaluation, and test.

eg

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
    optionsBuilder
        .UseSqlServer(@"Server=(localdb)\mssqllocaldb;Database=EFQuerying;Trusted_Connection=True;")
        .ConfigureWarnings(warnings => warnings.Throw(RelationalEventId.QueryClientEvaluationWarning));
}

See https://learn.microsoft.com/en-us/ef/core/querying/client-eval#previous-versions

Or start your migration to EF Core 3.1 which has enhanced query translation capabilities, and has removed the client-side evaluation "feature".

Trantham

Thank you, I haven't know about this possibility. It works and throws a very useful exception. For example: Error generated for warning 'Microsoft.EntityFrameworkCore.Query.QueryClientEvaluationWarning: The LINQ expression 'where [w].EntityId.ToString().StartsWith("6")' could not be translated and will be evaluated locally.'. This exception can be suppressed or logged by passing event ID 'RelationalEventId.QueryClientEvaluationWarning' to the 'ConfigureWarnings' method in 'DbContext.OnConfiguring' or 'AddDbContext'. – Resistor