EF Core is a very powerful and convenient way of writing SQL queries in C#. Though we have a native method EF.Functions.Like() to directly translate and filter match expression to SQL but there are scenarios where we need to add more than one match expression.

One way to tackle this is by calling EF.Functions.Like() multiple times in the same query like:

var users = await context.Users
    .Where(x => EF.Functions.Like(x.FirstName, $"%{filter.Keyword}%") 
    || EF.Functions.Like(x.LastName, $"%{filter.Keyword}%")
    || EF.Functions.Like(x.Id, $"%{filter.Keyword}%"));

So here we can see that we have to repeat the same code again and again. To solve this we can create a LINQ extension method which will allow us to add multiple match expressions along with the pattern.

For this, you should have a basic understanding of how expression trees in C# work. Below I have added a code snippet on how we can leverage the power of expression trees to filter multiple match expressions in one go.

public static class LinqExtension 
{
    /// <summary>
    /// An extended implementation of the SQL LIKE operation with multiple match expression selectors. 
    /// </summary>
    /// <typeparam name="TSource">Type of IQueryable</typeparam>
    /// <param name="source">The query</param>
    /// <param name="matchExpressionSelectors">Multiple selectors that are to be compared with the pattern</param>
    /// <param name="pattern">String we want to match the selector with</param>
    /// <returns>IQueryable containing filtered records</returns>
    public static IQueryable<TSource> WhereIfLike<TSource>(
    this IQueryable<TSource> source, 
    string pattern, 
    params Expression<Func<TSource, string>>[] matchExpressionSelectors)
    {
        if (String.IsNullOrEmpty(pattern))
            return source;

        var concatMethod = typeof(string).GetMethod(nameof(string.Concat), 
            new[] { typeof(object), typeof(object) });

        var likeExpressions = new List<Expression<Func<TSource, bool>>>();

        foreach (var matchExpressionSelector in matchExpressionSelectors)
        {
            var likeExpression = Expression.Lambda<Func<TSource, bool>>(
                Expression.Call(
                    typeof(DbFunctionsExtensions),
                    nameof(DbFunctionsExtensions.Like),
                    null,
                    Expression.Constant(EF.Functions),
                    matchExpressionSelector.Body,
                    Expression.Add(
                        Expression.Add(
                            Expression.Constant("%"),
                            Expression.Constant(pattern),
                            concatMethod),
                        Expression.Constant("%"),
                        concatMethod)),
                matchExpressionSelector.Parameters);

            likeExpressions.Add(likeExpression);
        }

        var firstLikeExpression = likeExpressions.First();

        if (likeExpressions.Count > 1)
        {
            foreach (var likeExpression in likeExpressions.Skip(1))
            {
                firstLikeExpression = firstLikeExpression.Or(likeExpression);
            }
        }

        source = source.Where(firstLikeExpression);
        return source;
    }


    // Used to combine multiple like expressions
    private static Expression<Func<T, bool>> Or<T>(
        this Expression<Func<T, bool>> exp1, 
        Expression<Func<T, bool>> exp2)
    {
        var visitor = new ParameterUpdateVisitor(exp2.Parameters.First(),
           exp1.Parameters.First());
        exp2 = visitor.Visit(exp2) as Expression<Func<T, bool>>;

        var binaryExp = Expression.Or(exp1.Body, exp2.Body);
        return Expression.Lambda<Func<T, bool>>(binaryExp, exp2.Parameters);
    }
}

internal class ParameterUpdateVisitor : ExpressionVisitor
{
    private ParameterExpression oldParameter;
    private ParameterExpression newParameter;

    public ParameterUpdateVisitor(ParameterExpression oldParameter, 
        ParameterExpression newParameter)
    {
        this.oldParameter = oldParameter;
        this.newParameter = newParameter;
    }

    protected override Expression VisitParameter(ParameterExpression node)
    {
        if (object.ReferenceEquals(node, this.oldParameter))
            return this.newParameter;

        return base.VisitParameter(node);
    }
}

You must have noticed that I have used the concept of Expression Visitor here, it's because combining two lambda expressions doesn't work directly in EF Core. So we use ExpressionVisitor to change the instance of lambda expressions.

More about that here .

Now let's see how we can use it in full swing:

var users = await context.Users
    .WhereIfLike(filter.Keyword, x => x.FirstName, x => x.LastName, x => x.Id);

So this is how we can use expression trees to implement SQL LIKE on multiple match expressions on LINQ. Cheers! 🍻