Efcore: Query : does EF.Functions.Like support with array words ?

[toanhuynh83]

Hi guys,

Do you have support "EF.Functions.Like" with array words ?

I have an array words as below:
var words = new[] { "Jon", "Sem", "Tuna" };

so I would to query like this:
var query = context.Products.Where(q => EF.Functions.Like(q.Empid, words, "%"));

and sql command generate to execute something like this:
SELECT *
FROM table
WHERE (Empid LIKE '%Jon%') OR (Empid LIKE '%Sem%') OR (Empid LIKE '%Tuna%')

EF.Functions.Like seems supported with one word only. Please let me know if you have any suggestion.

Thanks,

[roji]

EF.Functions.Like() doesn't (currently) support string arrays. Methods on EF.Functions, such as Like, are in general meant to correspond to a single database function or operator. Note that you can very easily have multiple likes by writing your query exactly as it is to be translated to SQL:

c# var query = context.Products.Where(q => EF.Functions.Like(q.Empid, "%Jon%") || EF.Functions.Like(q.Empid, "%Sem%"));

[toanhuynh83]

Thanks @roji ,

string arrays is dynamic values, so I cannot write the query exactly as your query. I hope "EF.Functions.Like" will support string arrays in the future.

[BladeWise]

Given that Db functions are supported, it is just a matter of creating a proper Expression dynamically.

Consider this simple context:
``` C#
public class MyContext : DbContext
{
public DbSet Products { get; set; }

public MyContext(DbContextOptions<MyContext> options)
        : base(options)
{
}

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    base.OnModelCreating(modelBuilder);

    modelBuilder.Entity<Product>()
                .HasKey(x => x.Id);
}

}

public class Product
{
public string Empid { get; set; }

public int? Id { get; set; }

}

I created an extension method to provide the proper where clause:
``` C#
public static class Extensions
{
    public static IQueryable<Product> EmpidLikeAny(this IQueryable<Product> products, params string[] words)
    {
        var parameter = Expression.Parameter(typeof(Product));

        var body = words.Select(word => Expression.Call(typeof(DbFunctionsExtensions).GetMethod(nameof(DbFunctionsExtensions.Like),
                                                                                                new[]
                                                                                                {
                                                                                                        typeof(DbFunctions), typeof(string), typeof(string)
                                                                                                }),
                                                        Expression.Constant(EF.Functions),
                                                        Expression.Property(parameter, typeof(Product).GetProperty(nameof(Product.Empid))),
                                                        Expression.Constant(word)))
                        .Aggregate<MethodCallExpression, Expression>(null, (current, call) => current != null ? Expression.OrElse(current, call) : (Expression)call);

        return products.Where(Expression.Lambda<Func<Product, bool>>(body, parameter));
    }
}

the above method will build-up dynamically an Expression equivalent to the lambda provided by @roji. The parameter expression (Product), will be passed to the EF.Functions.Like() together with a constant value (the word itself).
It could be a good idea to cache both the MethodInfo and PropertyInfo instances for performance reasons (notice by the way that the Likefunction is indeed an extension method).

I tested the above with this simple program
``` C#
private static void Main(string[] args)
{
var builder = new DbContextOptionsBuilder().UseSqlServer("Server=localhost\SQLEXPRESS;Database=EmpidTest;Trusted_Connection=True;MultipleActiveResultSets=true;Integrated Security=true")
.UseLoggerFactory(new LoggerFactory().AddDebug());
using (var context = new MyContext(builder.Options))
{
var products = context.Products;
if (!products.Any())
{
products.AddRange(Enumerable.Range(1, 1000)
.Select(x => new Product
{
Empid = $"Prod{x}"
}));

        context.SaveChanges();
    }

    var values = products.EmpidLikeAny("%d1", "%d2", "%d8")
                            .ToList();
    Console.Write(string.Join(Environment.NewLine, values.Select(x => $"{x.Id} [{x.Empid}]")));

    Console.ReadLine();
}

}

and the SQL output looks like this
``` SQL
SELECT [p].[Id], [p].[Empid]
FROM [Products] AS [p]
WHERE ([p].[Empid] LIKE N'%d1' OR [p].[Empid] LIKE N'%d2') OR [p].[Empid] LIKE N'%d8'

Edit: I previously used the Expression.Or to combine filters, while it should have been Expression.OrElse, that yields the expected SQL query.

[BladeWise]

By the way it is not that hard to come up with a more generalized approach:
``` C#
public static IQueryable WhereAny(this IQueryable queryable, params Expression>[] predicates)
{
var parameter = Expression.Parameter(typeof(T));
return queryable.Where(Expression.Lambda>(predicates.Aggregate>, Expression>(null,
(current, predicate) =>
{
var visitor = new ParameterSubstitutionVisitor(predicate.Parameters[0], parameter);
return current != null ? Expression.OrElse(current, visitor.Visit(predicate.Body)) : visitor.Visit(predicate.Body);
}),
parameter));
}

private class ParameterSubstitutionVisitor : ExpressionVisitor
{
private readonly ParameterExpression _destination;
private readonly ParameterExpression _source;

public ParameterSubstitutionVisitor(ParameterExpression source, ParameterExpression destination)
{
    _source = source;
    _destination = destination;
}

protected override Expression VisitParameter(ParameterExpression node)
{
    return ReferenceEquals(node, _source) ? _destination : base.VisitParameter(node);
}

}

using the `WhereAny` extension you can write something like this
``` C#
var words = new[]
            {
                    "%od1", "%100%"
            };
var otherValues = products.WhereAny(words.Select(w => (Expression<Func<Product, bool>>)(x => EF.Functions.Like(x.Empid, w)))
                                            .ToArray())
                            .ToList();

It's just a matter of creating expressions and manipulating them... you can get really creative... :)

[toanhuynh83]

@BladeWise Exactly what I need, thanks. 👍