Check a column with Contain list in EF Core 3.1

Question

I using .Net Core & EF Core 3.1

I Have a Expression like Under, but when run throw this exception

could not be translated. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to either AsEnumerable(), AsAsyncEnumerable(), ToList(), or ToListAsync(). See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.

my code is

var years = new List<string> { "1390","1391" };
queryable = queryable.Where(x =>
     x.ProdYears != null &&
     years.Any(z => x.ProdYears.Contains(z))
 );

Why this code not working & how can fix this?

Answer 1

You can return all match items from the list using this extension method I found and modified from: Reference

This is not generic, so you will have to change "ContainTest" to your entity type.

    public static class EntityHelper
    {
        public static IQueryable<ContainTest> SqlLikeInList(this IQueryable<ContainTest> products, List<string> containsList)
        {
            if (!containsList.Any()) return null;

            var patterns = containsList.Select(t => $"%{t}%").ToList();

            ParameterExpression parameter = Expression.Parameter(typeof(ContainTest));
            Expression body = patterns.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(ContainTest).GetProperty(nameof(ContainTest.ProdYears))),
                                                                      Expression.Constant(word)))
                                      .Aggregate<MethodCallExpression, Expression>(null, (current, call) => current != null ? Expression.OrElse(current, call) : (Expression)call);

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

and then call it from your DBSet like this:

var years = new List<string> { "1390", "1391" };
IQueryable<ContainTest> queryable = context.ContainTests.SqlLikeInList(years);

From testing, I found that all of that could be avoided if you were looking for exact matches of '1390' or '1391'

years.Any(z => x.ProdYears == z)

...so it is the .contains statement that was forcing you to use client side evaluation. Using this extension method, however, allows you to use a single call for server side evaluation.

Using SQL Server Profiler, my example produces this SQL

SELECT [c].[ContainTestId], [c].[ProdYears], [c].[RequiredContentColumn]
FROM [ContainTests] AS [c]
WHERE ([c].[ProdYears] LIKE N'%1390%') OR ([c].[ProdYears] LIKE N'%1391%')

For reference, I created an entity to recreate your scenario as such (which is what the SQL references)

    public class ContainTest
    {
        public long ContainTestId { get; set; }
        public string? ProdYears { get; set; }
        public string RequiredContentColumn { get; set; }
    }

and I populated the table with this sample data:

SET IDENTITY_INSERT [dbo].[ContainTests] ON
INSERT INTO [dbo].[ContainTests] ([ContainTestId], [ProdYears], [RequiredContentColumn]) VALUES (1, N'aa1389zz', N'dont get me')
INSERT INTO [dbo].[ContainTests] ([ContainTestId], [ProdYears], [RequiredContentColumn]) VALUES (2, N'zz1390aa', N'get me')
INSERT INTO [dbo].[ContainTests] ([ContainTestId], [ProdYears], [RequiredContentColumn]) VALUES (3, N'aa1391zz', N'get me too')
INSERT INTO [dbo].[ContainTests] ([ContainTestId], [ProdYears], [RequiredContentColumn]) VALUES (4, NULL, N'dont get me')
SET IDENTITY_INSERT [dbo].[ContainTests] OFF

Happy Coding!!!

Answer 2

If you are willing to use LINQKit, you can create extension methods to handle your query:

public static class LinqKitExt { // using LINQKit
    // searchTerms - IEnumerable<TSearch> where one must match for a row
    // testFne(row,searchTerm) - test one of searchTerms against a row
    // r => searchTerms.All(s => testFne(r,s))
    public static Expression<Func<T, bool>> AnyIs<T, TSearch>(this IEnumerable<TSearch> searchTerms, Expression<Func<T, TSearch, bool>> testFne) {
        var pred = PredicateBuilder.New<T>();
        foreach (var s in searchTerms)
            pred = pred.Or(r => testFne.Invoke(r, s));
    
        return pred;
    }

    // searchTerms - IEnumerable<TSearch> where one must match for a row
    // testFne(row,searchTerm) - test one of searchTerms against a row
    // dbq.Where(r => searchTerms.Any(s => testFne(r,s)))
    public static IQueryable<T> WhereAny<T,TSearch>(this IQueryable<T> dbq, IEnumerable<TSearch> searchTerms, Expression<Func<T, TSearch, bool>> testFne) =>
        dbq.AsExpandable().Where(searchTerms.AnyIs(testFne));
    public static IQueryable<T> WhereAny<T,TSearch>(this IQueryable<T> dbq, Expression<Func<T, TSearch, bool>> testFne, IEnumerable<TSearch> searchTerms) =>
        dbq.AsExpandable().Where(searchTerms.AnyIs(testFne));
    public static IQueryable<T> WhereAny<T,TSearch>(this IQueryable<T> dbq, Expression<Func<T, TSearch, bool>> testFne, params TSearch[] searchTerms) =>
        dbq.AsExpandable().Where(searchTerms.AnyIs(testFne));
}

Then you can use the methods:

var years = new List<string> { "1390","1391" };
queryable = queryable.WhereAny(years, (qr, y) => qr.ProdYears.Contains(y));

NOTE: You don't need to test against null as SQL will handle it automatically.