Flexible string filter in Entity Framework

Question

I created this method to prepare a 'Func' server-side so that I can apply a "flexible" string filter to my LINQ query. But perhaps I am going about this all wrong. Func are not allowed to run inside a Where clause.

Is there any way around this or should I be doing this differently? I would like for the method to be reusable for multiple string fields in my models.

This is the method I was planning to use before I learnt that that wouldn't work.

public static Tuple<Func<string,bool>, bool> PrepareFilterFieldFunc(string filter)
{
    bool valid = false;

    var retFunc = new Func<string, bool> ((string query_val)=>{
        return true;
    });

    string filter_string = "";

    if (filter.Contains("*")) 
    {
        var f_strs = filter.Split("*");

        if (f_strs.Length == 3)   //contains
        {
            if (f_strs[0] == "" && f_strs[2] == "")
            {
                filter_string = f_strs[1];
                retFunc = new Func<string, bool> ((string query_val)=>{
                    return query_val.Contains(filter_string);
                });

                valid = true;
            }
            else
            {
                filter_string = filter;
                retFunc = new Func<string, bool> ((string query_val)=>{
                    return query_val.Equals(filter_string);
                });
                valid = true;
            }
        }
        else if (f_strs.Length == 2)   //startswith or endswith
        {
            if (f_strs[0] == "")   //startswith
            {
                filter_string = f_strs[1];
                retFunc = new Func<string, bool> ((string query_val)=>{
                    return query_val.StartsWith(filter_string);
                });
                valid = true;
            }
            else if (f_strs[1] == "")  //endswith
            {
                filter_string = f_strs[1];
                retFunc = new Func<string, bool> ((string query_val)=>{
                    return query_val.EndsWith(filter_string);
                });
                valid = true;
            }
            else
            {
                filter_string = filter;
                retFunc = new Func<string, bool> ((string query_val)=>{
                    return query_val.Equals(filter_string);
                });
                valid = true;
            }
        }
        else
        {
                filter_string = filter;
                retFunc = new Func<string, bool> ((string query_val)=>{
                    return query_val.Equals(filter_string);
                });
                valid = true;
        }
    }
    else
    {
        filter_string = filter;
        retFunc = new Func<string, bool> ((string query_val)=>{
            return query_val.Equals(filter_string);
        });
        valid = true;
    }

    return new Tuple<Func<string, bool>, bool>( retFunc, valid);
}

When preparing the query I then wanted to run the following code.

( Func<string, bool> FuncCountryFilter, bool boolExecuteCountryFilter ) = 
    SHARED.PrepareFilterFieldFunc(SHQ.filter_countryCode);
if (boolExecuteCountryFilter)
{
    type_query = type_query.Where( sh => FuncCountryFilter( sh.bill_to_country_region_code ) );
}

But I then get the following error.

Unhandled exception. System.NotSupportedException: The LINQ expression node type 'Invoke' is not supported in LINQ to Entities.

Building via expressions instead ? https://stackoverflow.com/questions/52889567/dynamic-linq-with-any-in-where-clausule-c-net-core-ef-core — Caius Jard, Sep 16 '21 at 05:32
Thank you for your suggestions. I came up with a solution (see answer). — WouldBeNerd, Sep 17 '21 at 01:28

score 0 · Accepted Answer · answered Sep 17 '21 at 01:26

per the suggestions in the comments I spent some time learning about Expressions. This is probably not the most elegant/efficient method. But it seems to work.

public static IQueryable<Tmodel> WildcardFilter<Tmodel>(string filter_field, string filter_value, IQueryable<Tmodel> previous_query ){


    var type = typeof (Tmodel);
    var pe = Expression.Parameter(type, "p");
    var propertyReference = Expression.Property(pe, filter_field);
    

    string filter_string =  filter_value.Replace("*", string.Empty);
    if (filter_value.StartsWith("*") && filter_value.EndsWith("*")){

        var constantReference = Expression.Constant(filter_string);

        MethodInfo mi = typeof(string).GetMethod("Contains", new Type[] { typeof(string) });
        Expression filterExpression = Expression.Call(propertyReference, mi, constantReference);

        var lambdaComparrison = Expression.Lambda<Func<Tmodel, bool>> ( filterExpression, pe );
        return previous_query.Where(lambdaComparrison);

    }else if (filter_value.StartsWith("*")){
        
        var constantReference = Expression.Constant(filter_string);

        MethodInfo mi = typeof(string).GetMethod("EndsWith", new Type[] { typeof(string) });
        Expression filterExpression = Expression.Call(propertyReference, mi, constantReference);

        var lambdaComparrison = Expression.Lambda<Func<Tmodel, bool>> ( filterExpression, pe );
        return previous_query.Where(lambdaComparrison);

    }else if (filter_value.EndsWith("*")){

        var constantReference = Expression.Constant(filter_string);

        MethodInfo mi = typeof(string).GetMethod("StartsWith", new Type[] { typeof(string) });
        Expression filterExpression = Expression.Call(propertyReference, mi, constantReference);

        var lambdaComparrison = Expression.Lambda<Func<Tmodel, bool>> ( filterExpression, pe );
        return previous_query.Where(lambdaComparrison);

    }else{

        var constantReference = Expression.Constant(filter_value);
        var filterExpression = Expression.Equal(propertyReference, constantReference);
        var lambdaComparrison = Expression.Lambda<Func<Tmodel, bool>> ( filterExpression, pe );
        return previous_query.Where(lambdaComparrison);

    }

}

Flexible string filter in Entity Framework

1 Answers1