C# Dynamic database filtering with Linq Expression

Question

I try to create generic method for filtering different entities in database in order to avoid creating huge methods for every entity with combining filtering rules using very similar if statements.

Now I'm trying to create expression which would represent equality comparison getting as parameter expression describing property to compare and some value.

My concept how to solve problem and what I created basing on other posts shows bellow code snippet:

public class FuelCard 
{
    public int Id { get; set; }
    public string Number { get; set; }
    public virtual User User { get; set; }
}

public static IQueryable<TEntity> ApplyFilter<TEntity, TProperty>(
    this IQueryable<TEntity> query, 
    Expression<Func<TEntity, TProperty>> expr, TProperty value)
{
    Expression<Func<TEntity, bool>> predicate = param => true;

    var filterExpression = Expression.Equal(expr, Expression.Constant(value));
    var lambda = Expression.Lambda<Func<TEntity, bool>>(filterExpression);

    predicate = predicate.And(lambda);
    return query.Where(predicate);
}

And in the end I would like to use it like that:

IQueryable<FuelCard> query = // Get cards from database as IQueryable

query = query.ApplyFilter(x => x.Id, 85);
query = query.ApplyFilter(x => x.User.LastName + " " + x.User.FirstName, "Jon Green");

I would like to define annonymous expressions describing how to get value of columns and then apply different filters (here is shown simple example with equal method).

But when I call Expression.Equal i got error that there is no binary operator for Func and Int32.

In the all examples there are created Expression.Parameter objects with the name of the property, but there they are operating only on properties in Entity class (without using navigation properties etc.). But is it possible to combine filter expression with announymous property expression?

I hope I described clearly what am I trying to achieve and what is the difference from the standard examples, which is source of my problems

I would be very grateful if someone help me how to create such filter in order to compare result of the expression in given in parameter with value and than apply the predicate to the query in order to run it against sql database :)

Answer 1

It is something like:

public static IQueryable<TSource> WhereEqual<TSource, TProperty>(this IQueryable<TSource> query, Expression<Func<TSource, TProperty>> propertySelector, TProperty value)
{
    var body2 = Expression.Equal(propertySelector.Body, Expression.Constant(value));
    var lambda = Expression.Lambda<Func<TSource, bool>>(body2, propertySelector.Parameters);
    return query.Where(lambda);
}

Use it like:

IQueryable<FuelCard> query = // Get cards from database as IQueryable

query = query.WhereEqual(x => x.Id, 85);
query = query.WhereEqual(x => x.User.LastName + " " + x.User.FirstName, "Jon Green");

In general multiple .Where() are implicitly in && (in and) between them. So you only need to create an expression based on the property selector expression plus equals plus the value passed and then return a .Where() that uses that expression.