using Expression Tree with Where Any EntityCore

Question

I am trying to filter a entity object that has attributes (another entity, nested).

I want to return the parent which is the entity but I want to filter by all the attributes with a group of expressions hence the for loop.

Currently it doesn't reach completion and will run forever?

The issue is I need to do ANY inside of WHERE

IQueryable<Product> entity = _context.Products.AsNoTracking()
    .Include(e => e.Attributes)
    .ThenInclude(e => e.Attribute.ParentAttribute)
    .Include(e => e.Prices);

foreach (var rule in filter.ToRuleGroup().Rules)
{
    var compiled = Rule.CompileRule<ProductAttribute>(rule);
    entity = entity.Where(x => x.Attributes.Any(y => compiled(y.Attribute)));
}

return entity.Take(10).ToArray(); 

From Rule Class

public static Func<T, bool> CompileRule<T>(Rule r)
{
    var paramUser = Expression.Parameter(typeof(T));
    Expression expr = BuildExpr<T>(r, paramUser);
    // build a lambda function User->bool and compile it
    return Expression.Lambda<Func<T, bool>>(expr, paramUser).Compile();
}
public static Expression BuildExpr<T>(Rule r, ParameterExpression param)
{
    var left = MemberExpression.Property(param, r.MemberName);
    var tProp = typeof(T).GetProperty(r.MemberName).PropertyType;
    ExpressionType tBinary;
    // is the operator a known .NET operator?
    if (ExpressionType.TryParse(r.Operator, out tBinary))
    {
        var right = Expression.Constant(Convert.ChangeType(r.TargetValue, tProp));
        // use a binary operation, e.g. 'Equal' -> 'u.Age == 15'
        return Expression.MakeBinary(tBinary, left, right);
    }
    else
    {
        var method = tProp.GetMethod(r.Operator);
        var tParam = method.GetParameters()[0].ParameterType;
        var right = Expression.Constant(Convert.ChangeType(r.TargetValue, tParam));
        // use a method call, e.g. 'Contains' -> 'u.Tags.Contains(some_tag)'
        return Expression.Call(left, method, right);
    }
}

Edit Lamda version :

  public static Expression<Func<T, bool>> ToWhere<T>(Rule r)
        {
            var paramUser = Expression.Parameter(typeof(T));
            Expression expr = BuildExpr<T>(r, paramUser);
            // build a lambda function User->bool and compile it
            return Expression.Lambda<Func<T, bool>>(expr, paramUser);
        }

Edit 2 with any changes version : see @Ivan Stoev's suggestions

   foreach (var rule in filter.ToRuleGroup().Rules){
           entity = entity.Where(Rule.Any((Product p) => p.Attributes.Select(a => a.Attribute), rule));
     }
return entity;

Issue is it is still taking a very long time. I am not getting a response. Here is the SQL from entity :

    SELECT [p].[Id], [p].[IsActive], [p].[IsSyncEnabled], [p].[IsSyncQueued], [p].[LastModifiedDate], [p].[MID], [p].[Sku], [p].[SkuId]
FROM [Product] AS [p]
WHERE (((EXISTS (
    SELECT 1
    FROM [Product_ProductAttribute] AS [a]
    INNER JOIN [Product_Attribute] AS [a.Attribute] ON [a].[AttributeId] = [a.Attribute].[Id]
    WHERE ([a.Attribute].[Type] = N'Raw Sku') AND ([p].[Id] = [a].[ProductId])) AND EXISTS (
    SELECT 1
    FROM [Product_ProductAttribute] AS [a0]
    INNER JOIN [Product_Attribute] AS [a.Attribute0] ON [a0].[AttributeId] = [a.Attribute0].[Id]
    WHERE ([a.Attribute0].[Name] = N'SMALL') AND ([p].[Id] = [a0].[ProductId]))) AND EXISTS (
    SELECT 1
    FROM [Product_ProductAttribute] AS [a1]
    INNER JOIN [Product_Attribute] AS [a.Attribute1] ON [a1].[AttributeId] = [a.Attribute1].[Id]
    WHERE ([a.Attribute1].[Type] = N'Raw Sku') AND ([p].[Id] = [a1].[ProductId]))) AND EXISTS (
    SELECT 1
    FROM [Product_ProductAttribute] AS [a2]
    INNER JOIN [Product_Attribute] AS [a.Attribute2] ON [a2].[AttributeId] = [a.Attribute2].[Id]
    WHERE ([a.Attribute2].[Name] = N'LARGE') AND ([p].[Id] = [a2].[ProductId]))) AND EXISTS (
    SELECT 1
    FROM [Product_ProductAttribute] AS [a3]
    INNER JOIN [Product_Attribute] AS [a.Attribute3] ON [a3].[AttributeId] = [a.Attribute3].[Id]
    WHERE ([a.Attribute3].[Name] = N'BLACK') AND ([p].[Id] = [a3].[ProductId]))
ORDER BY [p].[Id]
Microsoft.EntityFrameworkCore.Database.Command:Information: Executed DbCommand (93ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
SELECT [p.Attributes].[Id], [p.Attributes].[AttributeId], [p.Attributes].[ProductId], [p.Attribute].[Id], [p.Attribute].[Hiearchy], [p.Attribute].[Name], [p.Attribute].[ParentAttributeId], [p.Attribute].[Type], [p.Attribute].[Value], [p.Attribute.ParentAttribute].[Id], [p.Attribute.ParentAttribute].[Hiearchy], [p.Attribute.ParentAttribute].[Name], [p.Attribute.ParentAttribute].[ParentAttributeId], [p.Attribute.ParentAttribute].[Type], [p.Attribute.ParentAttribute].[Value]
FROM [Product_ProductAttribute] AS [p.Attributes]
INNER JOIN [Product_Attribute] AS [p.Attribute] ON [p.Attributes].[AttributeId] = [p.Attribute].[Id]
LEFT JOIN [Product_Attribute] AS [p.Attribute.ParentAttribute] ON [p.Attribute].[ParentAttributeId] = [p.Attribute.ParentAttribute].[Id]
INNER JOIN (
    SELECT [p0].[Id]
    FROM [Product] AS [p0]
    WHERE (((EXISTS (
        SELECT 1
        FROM [Product_ProductAttribute] AS [a4]
        INNER JOIN [Product_Attribute] AS [a.Attribute4] ON [a4].[AttributeId] = [a.Attribute4].[Id]
        WHERE ([a.Attribute4].[Type] = N'Raw Sku') AND ([p0].[Id] = [a4].[ProductId])) AND EXISTS (
        SELECT 1
        FROM [Product_ProductAttribute] AS [a5]
        INNER JOIN [Product_Attribute] AS [a.Attribute5] ON [a5].[AttributeId] = [a.Attribute5].[Id]
        WHERE ([a.Attribute5].[Name] = N'SMALL') AND ([p0].[Id] = [a5].[ProductId]))) AND EXISTS (
        SELECT 1
        FROM [Product_ProductAttribute] AS [a6]
        INNER JOIN [Product_Attribute] AS [a.Attribute6] ON [a6].[AttributeId] = [a.Attribute6].[Id]
        WHERE ([a.Attribute6].[Type] = N'Raw Sku') AND ([p0].[Id] = [a6].[ProductId]))) AND EXISTS (
        SELECT 1
        FROM [Product_ProductAttribute] AS [a7]
        INNER JOIN [Product_Attribute] AS [a.Attribute7] ON [a7].[AttributeId] = [a.Attribute7].[Id]
        WHERE ([a.Attribute7].[Name] = N'LARGE') AND ([p0].[Id] = [a7].[ProductId]))) AND EXISTS (
        SELECT 1
        FROM [Product_ProductAttribute] AS [a8]
        INNER JOIN [Product_Attribute] AS [a.Attribute8] ON [a8].[AttributeId] = [a.Attribute8].[Id]
        WHERE ([a.Attribute8].[Name] = N'BLACK') AND ([p0].[Id] = [a8].[ProductId]))
) AS [t] ON [p.Attributes].[ProductId] = [t].[Id]
ORDER BY [t].[Id]
Microsoft.EntityFrameworkCore.Database.Command:Information: Executed DbCommand (156ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
SELECT [p.Prices].[Division], [p.Prices].[ProductId], [p.Prices].[PriceId], [p.Prices].[EntryDate], [p.Prices].[EffectiveDate], [p.Prices].[ExpireDate]
FROM [Product_ProductPrice] AS [p.Prices]
INNER JOIN (
    SELECT [p1].[Id]
    FROM [Product] AS [p1]
    WHERE (((EXISTS (
        SELECT 1
        FROM [Product_ProductAttribute] AS [a9]
        INNER JOIN [Product_Attribute] AS [a.Attribute9] ON [a9].[AttributeId] = [a.Attribute9].[Id]
        WHERE ([a.Attribute9].[Type] = N'Raw Sku') AND ([p1].[Id] = [a9].[ProductId])) AND EXISTS (
        SELECT 1
        FROM [Product_ProductAttribute] AS [a10]
        INNER JOIN [Product_Attribute] AS [a.Attribute10] ON [a10].[AttributeId] = [a.Attribute10].[Id]
        WHERE ([a.Attribute10].[Name] = N'SMALL') AND ([p1].[Id] = [a10].[ProductId]))) AND EXISTS (
        SELECT 1
        FROM [Product_ProductAttribute] AS [a11]
        INNER JOIN [Product_Attribute] AS [a.Attribute11] ON [a11].[AttributeId] = [a.Attribute11].[Id]
        WHERE ([a.Attribute11].[Type] = N'Raw Sku') AND ([p1].[Id] = [a11].[ProductId]))) AND EXISTS (
        SELECT 1
        FROM [Product_ProductAttribute] AS [a12]
        INNER JOIN [Product_Attribute] AS [a.Attribute12] ON [a12].[AttributeId] = [a.Attribute12].[Id]
        WHERE ([a.Attribute12].[Name] = N'LARGE') AND ([p1].[Id] = [a12].[ProductId]))) AND EXISTS (
        SELECT 1
        FROM [Product_ProductAttribute] AS [a13]
        INNER JOIN [Product_Attribute] AS [a.Attribute13] ON [a13].[AttributeId] = [a.Attribute13].[Id]
        WHERE ([a.Attribute13].[Name] = N'BLACK') AND ([p1].[Id] = [a13].[ProductId]))
) AS [t0] ON [p.Prices].[ProductId] = [t0].[Id]
ORDER BY [t0].[Id]

Answer 1

Don't use delegates (Func<...>) inside query expression treee because they cause client evaluation. In your case, instead of Func<T, bool> CompileRule<T>(Rule r), you shoud use the Expression<Func<T, bool>> ToWhere<T>(Rule r) (consider changing the name).

In order to be able to use Expression<Func<T, bool>> for Enumerable.Any<T> which expects Func<T, bool> and apply on inner collection accessor inside expression tree, you'd need a helper method like this:

public static partial class ExpressionUtils
{
    public static Expression<Func<TOuter, bool>> Any<TOuter, TInner>(Expression<Func<TOuter, IEnumerable<TInner>>> innerSelector, Expression<Func<TInner, bool>> innerPredicate)
    {
        var parameter = innerSelector.Parameters[0];
        var body = Expression.Call(
            typeof(Enumerable), nameof(Enumerable.Any), new[] { typeof(TInner) },
            innerSelector.Body, innerPredicate);
        return Expression.Lambda<Func<TOuter, bool>>(body, parameter);
    }
}

The above is a generic implementation. The Rule specific implementation inside your Rule class will be basically combining the two:

public static Expression<Func<TOuter, bool>> Any<TOuter, TInner>(Expression<Func<TOuter, IEnumerable<TInner>>> innerSelector, Rule r)
    => innerSelector.Any(ToWhere<TInner>(r));

The usage with your sample would be:

foreach (var rule in filter.ToRuleGroup().Rules)
{
    entity = entity.Where(Rule.Any((Product p) => p.Attributes.Select(a => a.Attribute), rule));
}