Entity Framework Core Where All

Question

I can do this fine in EF.Net, but not in EFCore

List<string> keyList = string.IsNullOrEmpty(keywords) ? new List<string>() : keywords.Split(' ').ToList();

collections = await db.ProductCollections
    .Where(m => m.Children.Count == 0 && (!keyList.Any() ? true : keyList.All(x => m.Name.Contains(x))))
    .ToListAsync();

I changed it into:

collections = await db.ProductCollections
    .Where(m => m.Children.Count == 0 && (keyList.Count == 0 || keyList.All(x => m.Name.Contains(x))))
    .ToListAsync();

So I guess the problem is in keyList.All(). How can I achieve this in EFCore?

The error message:

The LINQ expression 'DbSet() .Where(p => DbSet() .Where(p0 => EF.Property<Nullable>(p, "ID") != null && object.Equals( objA: (object)EF.Property<Nullable>(p, "ID"), objB: (object)EF.Property<Nullable>(p0, "ParentId"))) .Count() == 0 && False || __keyList_0 .All(x => p.Name.Contains(x)))' 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.

In EF6 it will have done that query in C#, not SQL, so it would have been very inefficient because it would load the entire table into memory. — Dai

Fabio Fabio · Accepted Answer · 2021-01-04T04:52:29

Alternative approach to build query based on key list, before executing it.

var keys = string.IsNullOrEmpty(keywords) ? Array.Empty<string>() : keywords.Split(' ');

var query = db.ProductCollections.Where(p => p.Children.Any() == false);
foreach (var key in keys)
{
     query = query.Where(p => p.Name.Contains(key));
}

var collections = await query.ToListAsync();

Entity Framework Core Where All

1 Answers