Nested Any query to compare collection in database with input collection

[petermorlion]

The Issue

I have not found anything in the docs that state my scenario isn't supported. I have also tested it with the latest version (6.0.1) because #26593 looks similar and is fixed. But my issue persists.

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))));

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.

With the help of StackOverflow I have a workaround, but it involves complicated code to build up an expression:

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) }, categoryFilter);
var finalFilter = Expression.Lambda<Func<ProductVariation, bool>>(any, pv);

db.ProductVariations.Where(finalFilter)

Version Info

EF Core version: 6.0.1
Database provider: Microsoft.EntityFrameworkCore.SqlServer
Target framework: .NET 6.0
Operating system: Windows 11
IDE: Visual Studio 2022 17.0.4

[roji]

This seems like a scenario where SQL Server's hierarchyid feature would be useful - it was specifically conceived for this e.g. IIRC it should allow doing checks like this in an index-optimized way). See https://github.com/efcore/EFCore.SqlServer.HierarchyId for a package to support hierarchyid with EF Core.

Is there some specific reason you can't use hierarchyid?

[ajcvickers]

@smitpatel to find dupe.

[smitpatel]

Duplicate of #19070

[petermorlion]

@roji This is a legacy app that we're migrating from .NET Framework 4.6 to .NET 6. I'm trying to keep the changes to a minimum because that's quite a lot of work in and of itself. HierarchyId is probably something that could be useful to replace the current system but as I've found a workaround with the expression, I'll leave it as is for the time being. Thanks for pointing me to that package though!

Thank you for pointing me to the duplicate. My apologies for not having found it.