EF Core can't translate an expression to compare two collections which EF 6 could-CodePudding

I have the following query in the old Entity Framework (.NET Framework):

db.ProductVariations
    .Where(pv => pv.Product.Categories
        .Any(cat => categorySearchStrings
            .Any(categorySearchString => cat.SearchTree.StartsWith(categorySearchString))));

I realize this isn't pretty, but I'm refactoring a legacy app and we have to choose our battles.

So what happens is that you can pass a list of search string (the categorySearchStrings), e.g.:

"38.54.", "45."

This is basically an implementation of a search tree where each category in our database has a SearchTree property. So a category with search tree 38.54.99 would match, but 38. would not.

A product can have multiple categories and we can pass in multiple search tree strings to the query. So we're comparing two collections.

This gets translated to

SELECT 
    [GroupBy1].[A1] AS [C1]
    FROM ( SELECT 
        COUNT(1) AS [A1]
        FROM [dbo].[ProductVariation] AS [Extent1]
        WHERE  EXISTS (SELECT 
            1 AS [C1]
            FROM ( SELECT 
                [Extent3].[SearchTree] AS [SearchTree]
                FROM  [dbo].[ProductCategory] AS [Extent2]
                INNER JOIN [dbo].[Category] AS [Extent3] ON [Extent2].[CategoryId] = [Extent3].[Id]
                WHERE [Extent1].[ProductId] = [Extent2].[ProductId]
            )  AS [Project1]
            WHERE  EXISTS (SELECT 
                1 AS [C1]
                FROM  ( SELECT 1 AS X ) AS [SingleRowTable1]
                WHERE ( CAST(CHARINDEX(N'38.', [Project1].[SearchTree]) AS int)) = 1
            )
        )
    )  AS [GroupBy1]

I'm trying to migrate to Entity Framework Core (6, running on .NET 6) but this now gives me the following error:

System.InvalidOperationException : The LINQ expression 'categorySearchString => categorySearchString == "" || EntityShaperExpression: 
        Company.Data.Models.Category
        ValueBufferExpression: 
            ProjectionBindingExpression: Inner
        IsNullable: False
    .SearchTree != null && categorySearchString != null && EntityShaperExpression: 
        Company.Data.Models.Category
        ValueBufferExpression: 
            ProjectionBindingExpression: Inner
        IsNullable: False
    .SearchTree.StartsWith(categorySearchString)' 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.

Switching to client evaluation isn't really an option I believe, because there's too many data that will be retrieved. Plus, there's more going on than just this Where clause. I simplified it.

I also tried rewriting it as this:

.Where(pv => pv.Product.Categories.Select(c => c.SearchTree).Any(st => categorySearchStrings.Any(ss => st.StartsWith(ss))));

But I just get the same error.

Is it possible to do this with EF Core?

CodePudding user response：

I'd be inclined to build a dynamic expression tree to represent the filter:

var cat = Expression.Parameter(typeof(Category), "cat");
var parts = new List<Expression>(categorySearchStrings.Count);
var startsWithMethod = typeof(string).GetMethod(nameof(string.StartsWith), new[] { typeof(string) });

foreach (string categorySearchString in categorySearchStrings)
{
    var searchTree = Expression.Property(cat, nameof(Category.SearchTree));
    var value = Expression.Constant(categorySearchString);
    var startsWith = Expression.Call(searchTree, startsWithMethod, value);
    parts.Add(startsWith);
}

var body = parts.Aggregate(Expression.OrElse);
var categoryFilter = Expression.Lambda<Func<Category, bool>>(body, cat);

var pv = Expression.Parameter(typeof(ProductVariation), "pv");
var product = Expression.Property(pv, nameof(ProductVariation.Product));
var categories = Expression.Property(product, nameof(Product.Categories));
var any = Expression.Call(typeof(Enumerable), nameof(Enumerable.Any), new[] { typeof(Category) }, categories, categoryFilter);
var finalFilter = Expression.Lambda<Func<ProductVariation, bool>>(any, pv);

db.ProductVariations
    .Where(finalFilter)
    ...

You should also report this as an issue on the efcore repository, to see if it can be fixed in a future version.