How to generalize a part of a LINQ to SQL query

user_831290 0
Viewed 74

I have the following snippet that is repeated through my code for different fields:

if (filters.NameIsLike)
    query = query.Where (x => EF.Functions.Like (x.Name, $"%{filters.Name}%"));
else
    query = query.Where (x => x.Name.ToLower () == filters.Name.ToLower ());

How can I create an extension method that generalizes what the snippet does?

Something like:

public static IQueryable<T> EqualsOrLike (this IQueryable<T> query, ??? field, bool isLike, string filter)
{
    if (isLike)
        return query.Where (x => EF.Functions.Like (field.ToLower (), $"%{filter.ToLower ()}%"));
    else
        return query.Where (x => field.ToLower () == filter.ToLower ());
}

Thanks in advance!

UPDATE:

This is how the initial snippet is used (as an example):

In this case the class is Company, but it could be something different... Therefore I don't know the class nor the field. And the comment explain how I would use the EqualsOrLike function:

protected   override            IQueryable<Company>                 _QueryAsync         (IQueryable<Company> query, QueryFilterBase filter)
{
    CompanyQueryFilter filters = (CompanyQueryFilter)filter;

    if (!string.IsNullOrEmpty (filters.Name))
    {
        if (filters.NameIsLike)
            query = query.Where (x => EF.Functions.Like (x.Name.ToLower (), $"%{filters.Name.ToLower ()}%"));
        else
            query = query.Where (x => x.Name.ToLower () == filters.Name.ToLower ());

        //query = EqualsOrLike (query, x => x.Name, filters.NameIsLike, filters.Name);
    }

    if (!string.IsNullOrEmpty (filters.AtecoCode))
        query = query.Where (x => EF.Functions.Like (x.AtecoCode, $"%{filters.AtecoCode}%"));

    if (!string.IsNullOrEmpty (filters.VatNumber))
    {
        if (filters.VatNumberIsLike)
            query = query.Where (x => EF.Functions.Like (x.VatNumber, $"%{filters.VatNumber}%"));
        else
            query = query.Where (x => x.VatNumber.ToLower () == filters.VatNumber.ToLower ());
            
        //query = EqualsOrLike (query, x => x.VatNumber, filters.VatNumberIsLike, filters.VatNumber);
    }

    return query;
}
ef-core-6.0 linq c#
2 Answers

Since you don't know the property to compare, you need to build your expression by hand.

Option 1, you can construct the entire expression explicitly. As a first step, I find it useful to see how C# compiles an expression by using a decompiler;

Expression<Func<C,bool>> expr = x => x.Name.ToLower () == filter;

Cleaning that up a little would give you the following;

private class LambdaCaptures
{
    public string filter;
}

var locals = new LambdaCaptures();
locals.filter = "";

ParameterExpression parameterExpression =
    Expression.Parameter(typeof(C), "x");

var expr =
    Expression.Lambda<Func<C, bool>>(
        Expression.Equal(
            Expression.Call(
                Expression.MakeMemberAccess(
                    parameterExpression,
                    typeof(C).GetProperty(nameof(C.Name))
                ),
                typeof(string).GetMethod(nameof(string.ToLower), new Type[] { }),
                Array.Empty<Expression>()
            ),
            Expression.Field(
                Expression.Constant(locals, typeof(LambdaCaptures)),
                typeof(C).GetField(nameof(LambdaCaptures.filter))
            )
        ),
        parameterExpression);

Which you can now tweak to replace the incoming type and property name. Note that you'll want to keep the captured lambda so that EF binds this value as a parameter. Otherwise EF and Sql server will need to recompile the query every time.

Option 2, you can use an ExpressionVisitor to tweak a template expression. For example, if you had an expression for both the field you wish to compare, and the comparison you wish to perform. You can replace the parameter from one expression with the body of the other.

Giving a complete solution that would look something like;

public static Expression<Func<T, bool>> BuildExpr<T, V>(Expression<Func<V, bool>> operation, Expression<Func<T, V>> value)
    => Expression.Lambda<Func<T, bool>>(
        ReplacingExpressionVisitor.Replace(
            operation.Parameters.Single(),
            value.Body,
            operation.Body
        ),
        value.Parameters.Single());

public static IQueryable<T> EqualsOrLike<T>(this IQueryable<T> query, Expression<Func<T, string>> value, bool isLike, string filter)
{
    if (string.IsNullOrEmpty(filter))
        return query;

    if (isLike)
    {
        filter = $"%{filter.ToLower()}%";
        var expr = BuildExpr(x => EF.Functions.Like(x, filter), value);
        return query.Where(expr);
    }
    else
    {
        filter = filter.ToLower();
        var expr = BuildExpr(x => x.ToLower() == filter, value);
        return query.Where(expr);
    }
}

query = query.EqualsOrLike(d => d.Name, filters.NameIsLike, filters.NameValue);

Since this type of replacement is a fairly common operation, you can re-use EF Core's ReplacingExpressionVisitor instead of writing your own.

user_41398090

You can use a Func to select the field from the result:

public static IQueryable<T> EqualsOrLike<T>(this IQueryable<T> query, Func<T, string> fieldSelector, bool isLike, string filter)
{
    if (isLike)
        return query.Where(x => EF.Functions.Like(fieldSelector(x).ToLower(), $"%{filter.ToLower ()}%"));
    else
        return query.Where(x => fieldSelector(x).ToLower() == filter.ToLower ());
}

You would then call it like so:

var results = someQueryable.EqualsOrLike(x => x.Name, false, "Robert");
user_11597630
Related