Checking if database column contains, starts with or ends with any of list items inside a where clause of an EF query doesn't work

Question

I'm trying to retrieve from database the list of products which names contains, starts with or ends with any of list elements that I pass as parameter to the method executing the query.

I've tried both Linq approaches (method and query syntax)

Here is the relevant code of Linq method query, names being List of names passed as argument to the method:

var query =  _dbContext.Products
                       .Where(p => p.IdExternalProduct == null && 
                                   names.Any(name => p.ProductName.Contains(name)))
                       .Select(item => new ProductEntity() { Uuid = item.Uuid, Code = item.Code});

or

var query =  _dbContext.Products
                       .Where(p => p.IdExternalProduct == null && 
                                   names.Any(name => p.ProductName.StartsWith(name)))
                       .Select(item => new ProductEntity() { Uuid = item.Uuid, Code = item.Code});

And here is the query syntax approach:

IQueryable<ProductEntity> query = (
                        from p in _dbContext.Products
                        where 
                            p.IdExternalProduct == null &&
                            (names != null && names.Count > 0 ? names.Any(name => p.ProductName.Contains(name)) : true)
                        select new ProductEntity()
                        {
                          Uuid = p.Uuid,
                          Code = p.Code
                        }

The exact same query works when I try with Equals instead of Contains, StartsWith or EndsWith.

I searched a lot and found a lot of resources but no solution worked for me.

Here are two related Stack overflow's questions where Jon Skeet recommends the same implementation.

Check if a string within a list contains a specific string with Linq

Proper LINQ where clauses

I'm using the version 6.0.10 of Entity Framework and SQL Server provider.

But it's seems that these formulas cannot be translated, throwing an exception with the following message:

The LINQ expression 'name => EntityShaperExpression:
Service.Product.Products
ValueBufferExpression:
ProjectionBindingExpression: EmptyProjectionMember
IsNullable: False
.ProductName.Contains(name)' 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 'AsEnumerable', 'AsAsyncEnumerable', 'ToList', or 'ToListAsync'. See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.

There's often a _SQL Function_ that will do the trick. Take a look at: https://learn.microsoft.com/en-us/ef/core/providers/sql-server/functions — Flydog57, Apr 08 '23 at 22:52
@Flydog57 The functions are well supported by EF, i'm getting the exception because of the list (Any), the simple use case *p.ProductName.Contains(name)* works well — lyes makhloufi, Apr 08 '23 at 22:58
@LeandroBardelli The sql equivelent of my request is *where product_name like 'test1%' OR product_name like 'test2%' OR product_name like 'test3%'* I don't see any logic to handle on database side — lyes makhloufi, Apr 09 '23 at 00:19
I know it can be translated, that's the way EF works, but in my humble opinion, for architecture concerns, this should be at db side. — Leandro Bardelli, Apr 09 '23 at 00:26
I finally found a workaround by using the LinqKit PredicateBuilder, this way, the predicates gets translated, put it as comment because it's not a solution to my initial problem. Hope this will help others. — lyes makhloufi, Apr 09 '23 at 00:40

ArwynFr · Answer 1 · 2023-04-09T22:28:30.610

The reason why Jon Skeet recommends this implementation in the other SO answers is because it refers to IEnumerable queries (linq-to-object) and not IQueryable queries (linq-to-entities). Linq-to-object executes on in-memory objects, and will actually execute the string.Contains method. Methods given to EF are not actually executed, they are translated into SQL statements, and efcore 6 does not know how to translate your statement, especially the name variable that comes from a linq-to-object lambda in the IEnumerable.Any call.

Most developper forget that IQueryable<T> is a cumulative type, and you can aggregate predicates on it. A simple solution would be to union queries from each name, and distinct all results:

var query = names.Select(name => _dbContext.Products
    .Where(product => product.IdExternalProduct == null)
    .Where(product => product.ProductName.Contains(name)))
  .Aggregate(Queryable.Union)
  .Distinct()
  .Select(product => new ProductEntity(...));

This query produces the following statement:

DECLARE @__name_0 nvarchar(4000) = N'a';
DECLARE @__name_1 nvarchar(4000) = N'b';

SELECT DISTINCT [t].[Id], [t].[IdExternalProduct], [t].[ProductName]
FROM (
    SELECT [p].[Id], [p].[IdExternalProduct], [p].[ProductName]
    FROM [Products] AS [p]
    WHERE ([p].[IdExternalProduct] IS NULL) AND ((@__name_0 LIKE N'') OR CHARINDEX(@__name_0, [p].[ProductName]) > 0)
    UNION
    SELECT [p0].[Id], [p0].[IdExternalProduct], [p0].[ProductName]
    FROM [Products] AS [p0]
    WHERE ([p0].[IdExternalProduct] IS NULL) AND ((@__name_1 LIKE N'') OR CHARINDEX(@__name_1, [p0].[ProductName]) > 0)
) AS [t]

The solution your suggest doesn't match with the situation I described in the question, I'm checking if productName column contains any of LIST string items, not just one string value as it's shown in your solution (name), applying the contains on one string value works well for me too. — lyes makhloufi, Apr 09 '23 at 22:02
No, the snippet will return all entries that match *any* of the values in the `names` collection. — ArwynFr, Apr 09 '23 at 22:21

Checking if database column contains, starts with or ends with any of list items inside a where clause of an EF query doesn't work

1 Answers1