CODEWITHSUNDEEP
c#linqasp.net-coreentity-framework-core
Paul
Paul

Reputation: 3253

Need to pass a generic filter function to list of objects to create where clause

I have a list of Countries in a dbContext using EfCore

I want to implement a generic way of filtering this list

    bool FilterCountriesv1(Country s)
    {
        var result = s.Name.StartsWith("A") && s.Name.Length > 4;
        return result;
    }

    private async Task<List<Country>> GetCountriesAsync(Func<Country, bool> func)
    {
        var allCountries = DbContext.Countries.AsQueryable();
        var filteredCountries = allCountries.Where(x=>func(x)).AsQueryable();
        var result = await filteredCountries.ToListAsync();
        return result;
    }

Where I use it like this

var countries = await GetCountriesAsync(FilterCountriesv1);

When I run this I get the error

The LINQ expression 'DbSet<Country>.Where(c => Invoke(__func_0, c[Country])
)' could not be translated. 
Either rewrite the query in a form that can be translated, 
or switch to client evaluation explicitly by inserting a call to either AsEnumerable(), 
AsAsyncEnumerable()

Not sure how to resolve this?

Upvotes: 0

Views: 324

Answers (3)

mj1313
mj1313

Reputation: 8459

The filter should be a string and convert it to the lambda expression, but make sure the property exists in the passed in model. Try like below:

public List<T> GetFilterResult<T>(IQueryable<T> currentlist, string filter)
{
    var exp = DynamicExpressionParser.ParseLambda<T, bool>(ParsingConfig.Default, false, filter, currentlist);
    var func = exp.Compile();
    var result = currentlist.Where(func).AsQueryable().ToList();
    return result;
}

string Filter()
{
    string filter = "x => x.Name.StartsWith(\"A\") && x.Name.Length > 4";
    return filter;
}

Use it like this:

var countries = _context.Countries.AsQueryable();
var result = GetFilterResult(countries, Filter());

Upvotes: -1

Truesteel86
Truesteel86

Reputation: 148

As stated by @COLD TOLD you should use an expression

Change your code to retrieve data to this:

private async Task<List<Country>> GetCountriesAsync(Expression<Func<Country, bool>> expression)
{
    var allCountries = DbContext.Countries.AsQueryable();
    var filteredCountries = allCountries.Where(expression).AsQueryable();
    var result = await filteredCountries.ToListAsync();
    return result;
}

Change your filter to return an expression

Expression<Func<Country, bool>> FilterCountriesv1(Country s)
{
    return (x) => x.Name.StartsWith("A") && x.Name > 4;

    // if you want to use s as a filter you can do
    // return (x) => x.Name.StartsWith(s.Name) && x.Name > 4
}

And call like this:

var countries = await GetCountriesAsync(FilterCountriesv1(new Country() { Name = "A"} ));

For conversion use this (install nuget package Serialize.Linq)

private string SerializeExpression(Expression expression)
{
    string result;

    using (MemoryStream stream = new MemoryStream())
        using (StreamReader reader = new StreamReader(stream))
        {
            new ExpressionSerializer(new JsonSerializer()).Serialize(stream, expression);
                
            stream.Position = 0;
            result = reader.ReadToEnd();
        }


    return result;
}

private Expression<Func<T, bool>> DeserializeExpression<T>(string expression)
{
    Expression<Func<T, bool>> result;

    using (MemoryStream stream = new MemoryStream())
        using (StreamWriter writer = new StreamWriter(stream))
        {
            writer.Write(expression);
            writer.Flush();

            stream.Position = 0;

            result = new ExpressionSerializer(new JsonSerializer()).Deserialize(stream) as Expression<Func<T, bool>>;
        }

    return result;
}

And then you can do something like this

string expression = SerializeExpression(FilterCountriesv1(new Country() { Name = "A"}));
var countries = dbContext.Countries.Where(DeserializeExpression<Country>(expression)).ToList();

*Note that the JsonSerializer used for serializing and deserializing is the one under Serializers.Linq.Serializers and not the one under Newtonsoft.Json

Upvotes: 0

COLD TOLD
COLD TOLD

Reputation: 13579

I do not think you can use pure delegates when trying to execute against sql. EF cant translate you pure delegate to sql.

Try

Expression<Func<Country, bool>> expected = p=> p.Name.StartsWith("A") && p.Name.Length > 4;

Upvotes: 3

Related Questions