How to generalize a part of a LINQ to SQL query-CodePudding

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!

CodePudding user response：

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");

CodePudding user response：

You can't use Func here, you need an Expression

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

I must say though, that using ToLower is likely to significantly impact performance. If possible, try to set the column collation to a case-insensitive collation, then you will be able to use indexes properly.

Equally, a leading % wildcard in a LIKE is also going to impact performance. If you can, remove it.