C# - Entity Framework Core : build a dynamic expression from multiple expressions

Question

I have an entity called Product and inside it a navigation property called ProductAttributes.

I need to get products with multiple conditions like this:

var products = context.Products
    .Include(x => x.ProductAttributes)
    .ThenInclude(x => x.Attribute)
    .AsNoTracking()
    .Where(x => x.ProductAttributes.FirstOrDefault(x => x.AttributeId == attributesIds[0]) != null 
                && x.ProductAttributes.FirstOrDefault(x => x.AttributeId == attributesIds[1]) != null
                && x.ProductAttributes.FirstOrDefault(x => x.AttributeId == attributesIds[2]) != null);

I've tried to build the expression dynamically, but all of my tries failed.

This is the code I've written:

var arg = Expression.Parameter(typeof(Product), "x");

var left = Expression.Property(arg, "ProductAttributes");
var right = Expression.Constant(null);

var exp = Expression.NotEqual(left, right);

Expression<Func<Product, bool>> expression = Expression.Lambda<Func<Product, bool>>(exp, arg);

foreach (var id in attributesIds)
{
     var searchArg = Expression.Parameter(typeof(ProductAttribute), "z");
     var searchLeft = Expression.Property(searchArg, "AttributeId");
     var searchRight = Expression.Constant(id);
     var searchExp = Expression.Equal(searchLeft, searchRight);

     Expression<Func<ProductAttribute, bool>> searchExpression = Expression.Lambda<Func<ProductAttribute, bool>>(searchExp, searchArg);
     Expression<Func<Product, bool>> subExpression = x => x.ProductAttributes.AsQueryable().Where(searchExpression).FirstOrDefault() != null;

     var andExp = Expression.And(expression.Body, subExpression.Body);
     expression = Expression.Lambda<Func<Product, bool>>(andExp, arg);
}

var products = context.Products.Include(x => x.ProductAttributes)
    .ThenInclude(x => x.Attribute)
    .AsNoTracking()
    .Where(expression);
    // .Where(x => x.ProductAttributes.FirstOrDefault(x => x.AttributeId == attributesIds[0]) != null);

return Ok(products);

The error I get is:

The LINQ expression '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.

So, how can I create this expression? Or what is the wrong in my code?

Answer 1

It is not an answer yet, but I think you have to build the following filter, which much more simpler

.Where(x => x.ProductAttributes
    .Count(a => attributesIds.Contains(a.AttributeId)) == attributesIds.Length
);