c# – Generic Query with PredicateBuilder and Linqkit

Question:

I have been using LinqKit to create generic queries for a long time.

One thing that always bothered me is the fact that I always have to test whether the value sent in the filter is valid.

For example: Suppose I have a string filter. Conditions can be Equal, StartsWith, EndsWith and Contains.

My method would be something like this:

public List<MyModel> Get(MyModelFilter filter)
{
    if (string.IsNullOrEmpty(filter.prop))
    {
        predicate = predicate.And(_myModel => myModel.Prop.Contains(filter.prop));
    }

    // mais uma gigante quantidade de if's com vários filtros

    return DbSet.AsExpandable()
            .Where(predicate)
            .ToList();
}

To get rid of this bunch of If's, I decided to create a generic method to apply the filter on the properties. My idea is to pass the property where the filter will be applied, and the filter definition, and encapsulate the Expression creation logic

It would be something like

public List<MyModel> Get(MyModelFilter filter)
{
    predicate = predicate.And(_myModel => myModel.Prop, filter.PropFilterDefinition);
    // adeus If's, apenas as outras implementações de filtros

    return DbSet.AsExpandable()
            .Where(predicate)
            .ToList();
}

For that, I created some extension methods to take care of this.

public static Expression<Func<TPredicate, bool>> And<TPredicate>(
    this ExpressionStarter<TPredicate> predicate,
    Func<TPredicate, string> property, StringFilterDefinition filter,
    bool ignoreNull = true)
{
    if (InvalidStringFilter(filter, ignoreNull))
    {
        return predicate;
    }

    // este é o And do LinqKit
    return predicate.And(BuildPredicate(property, filter));
}

private static Expression<Func<TPredicate, bool>> BuildPredicate<TPredicate>(
    Func<TPredicate, string> property,
    StringFilterDefinition filter)
{
    if (filter.Filter == StringFilterComparators.Equal)
    {
        return x => property.Invoke(x) == filter.Value;
    }

    if (filter.Filter == StringFilterComparators.BeginsWith)
    {
        return x => property.Invoke(x).StartsWith(filter.Value);
    }

    if (filter.Filter == StringFilterComparators.EndsWith)
    {
        return x => property.Invoke(x).EndsWith(filter.Value);
    }

    return x => property.Invoke(x).Contains(filter.Value);
}

private static bool InvalidStringFilter(
    StringFilterDefinition filter, 
    bool ignoreNullValue = true)
{
    if (filter?.Filter == null)
    {
        return true;
    }

    return ignoreNullValue && string.IsNullOrEmpty(filter.Value);
}

The problem is that the filter is not applied, and the answer is in Invoke right up there. EF is unable to translate the above expression to SQL. The EF error is

Microsoft.EntityFrameworkCore.Query.Internal.SqlServerQueryCompilationContextFactory[8] The LINQ expression '(__property_0.Invoke([x]) == __filter_Value_1)' could not be translated and will be evaluated locally. To configure this warning use the DbContextOptionsBuilder.ConfigureWarnings API (event id 'RelationalEventId.QueryClientEvaluationWarning'). ConfigureWarnings can be used when overriding the DbContext.OnConfiguring method or using AddDbContext on the application service provider.

The question is:

How can I make this construction work? Also, any suggestions on how to better this?

Answer:

Sorted out.

The big secret is that LinqKit has other really useful features, like AsExpandable, Expand and Invoke.

Because of these methods, it is possible to combine expressions without having problems.

However, these items only work with Expression<T, R>

So, just replace Func<T, R> by

private static Expression<Func<TPredicate, bool>> BuildPredicate<TPredicate>(
    Expression<Func<TPredicate, string>> property,
    StringFilterDefinition filter)
{
    if (filter.Filter == StringFilterComparators.Equal)
    {
        return x => property.Invoke(x) == filter.Value;
    }

    if (filter.Filter == StringFilterComparators.BeginsWith)
    {
        return x => property.Invoke(x).StartsWith(filter.Value);
    }

    if (filter.Filter == StringFilterComparators.EndsWith)
    {
        return x => property.Invoke(x).EndsWith(filter.Value);
    }

    return x => property.Invoke(x).Contains(filter.Value);
}
Scroll to Top
AllEscort