Make dynamic expression of EF core "Like" function

Question

I've written some codes to make dynamic expressions for filtering my pagination. I'm trying to make a dynamic expression of EF Core built-in functions for searching (EF.Functions.Like).

I've tried a way like bottom but it is an extension method and first parameters is not used when calling the method. I don't know how to follow the way ==> Ef => Function => Like.
The method should be used like this => Ef.Functions.Like("Property to search", "%Some Pattern")

var likeMethod = typeof(DbFunctionsExtensions)
                        .GetMethods()
                        .Where(p => p.Name == "Like")
                        .First();
string pattern = $"%{finalConstant}%"; 

ConstantExpression likeConstant = Expression.Constant(pattern,typeof(string));

// the member expression is the property expression for example p.Name
var likeMethodCall = Expression.Call(method: likeMethod, arguments: new[] { memberExpression, likeConstant });

var searchLambda = Expression.Lambda<Func<T, bool>>(likeMethodCall, parameter);
query = query.Where(searchLambda);

but it throw exception saying

Incorrect number of arguments supplied for call to method 'Boolean Like(Microsoft.EntityFrameworkCore.DbFunctions, System.String, System.String)'\r\nParameter name: method

Answer 1

I implemented a dynamic search based on this article .NET Core Npgsql.EntityFrameworkCore ILikeExpression That's what I did:

I implement the [Searchable] attribute, with which I will mark the properties by which the search will be performed. Properties are only of type string, if necessary I can explain how to search for properties of type long and int.

[AttributeUsage(AttributeTargets.Property)]
public class SearchableAttribute : Attribute
{
}

An extension has been created for IQueryable , which takes the input string from the search and implements the Like function according to the specified properties

public static class QueryableExtension
{
    public static IQueryable<TEntityDto> ExecuteQueryFilter<TEntityDto>(this IQueryable<TEntityDto> queryable, string query)
        where TEntityDto : class, IEntityDto
    {
        // If the incoming request is empty, skip the search
        if (string.IsNullOrEmpty(query))
        {
            return queryable;
        }

        // We get all properties with type of string marked with our attribute
        var properties = typeof(TEntityDto).GetProperties()
            .Where(p => p.PropertyType == typeof(string) &&
                        p.GetCustomAttributes(typeof(SearchableAttribute), true).FirstOrDefault() != null)
            .Select(x => x.Name).ToList();

        // If there are no such properties, skip the search
        if (!properties.Any())
        {
            return queryable;
        }

        // Get our generic object
        ParameterExpression entity = Expression.Parameter(typeof(TEntityDto), "entity");

        // Get the Like Method from EF.Functions
        var efLikeMethod = typeof(DbFunctionsExtensions).GetMethod("Like",
            BindingFlags.Static | BindingFlags.Public | BindingFlags.NonPublic,
            null,
            new[] { typeof(DbFunctions), typeof(string), typeof(string) },
            null);

        // We make a pattern for the search
        var pattern = Expression.Constant($"%{query}%", typeof(string));

        // Here we will collect a single search request for all properties
        Expression body = Expression.Constant(false);

        foreach (var propertyName in properties)
        {
            // Get property from our object
            var property = Expression.Property(entity, propertyName);

            // Сall the method with all the required arguments
            Expression expr = Expression.Call(efLikeMethod,
                    Expression.Property(null, typeof(EF), nameof(EF.Functions)), property, pattern);

            // Add to the main request
            body = Expression.OrElse(body, expr);
        }

        // Compose and pass the expression to Where
        var expression = Expression.Lambda<Func<TEntityDto, bool>>(body, entity);
        return queryable.Where(expression);
    }
}

The Dto object itself looks like this:

public class CategoryDto : IEntityDto
{
    public long Id { get; set; }

    [Searchable]
    public string Name { get; set; }

    [Searchable]
    public string IconKey { get; set; }

    public long UploadId { get; private set; }

    [Searchable]
    public string UploadFileName { get; set; }

    [Searchable]
    public string CreatedBy { get; set; }
    public DateTime Created { get; set; }
}

I tested this search method on one million records, with objects name in one to five words. The search process very fast. The performance benefit here is that Expression is converted on the database side as LINQ to SQL

Answer 2

Here's a working example

public static Expression<Func<T, bool>> Like<T>(Expression<Func<T, string>> prop, string keyword)
{
    var concatMethod = typeof(string).GetMethod(nameof(string.Concat), new[] { typeof(string), typeof(string) });
    return Expression.Lambda<Func<T, bool>>(
        Expression.Call(
            typeof(DbFunctionsExtensions),
            nameof(DbFunctionsExtensions.Like),
            null,
            Expression.Constant(EF.Functions),
            prop.Body,
            Expression.Add(
                Expression.Add(
                    Expression.Constant("%"),
                    Expression.Constant(keyword),
                    concatMethod),
                Expression.Constant("%"),
                concatMethod)),
        prop.Parameters);
}

query = query.Where(Like<User>(u => u.UserName, "angel"));