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?

First, you don't need those Includes unless you need to force eager loading. Second, you can write Where(x=>attributeIds.Contain(x.AttributeId) to generate a x.AttributeID in (1,4,7,123,...) clause in SQL. You don't need to dynamically generate anything. FirstOrDefault is mainly a TOP 1 clauses, not a filtering clause. — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Oct 18, 2022 at 9:53
What are you trying to do in the first place? What is ProductAttributes ? This smells strongly of the Entity-Attribute_Value antipattern (ie very bad idea). Instead of making your code easier to modify, you just made it a lot harder to write and query. You can't fix a bad design with queries. If anything, you have to "pivot" the EAV schema back into a proper table just to query it — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Oct 18, 2022 at 9:57
@PanagiotisKanavos for the idea of using Contain(x.AttributeId) I need to get the products which have productAttributes have attributeId=1 AND attributeId=2 ...etc, and using Where(x=>attributeIds.Contain(x.AttributeId) I got products have productAttributes have attributeId=1 OR attributeId=2 ... — James.mikael
– James.mikael, Commented Oct 18, 2022 at 9:57
@PanagiotisKanavos ProductAttributes is an entity which create a relation between Products table and Attributes table as well. — James.mikael
– James.mikael, Commented Oct 18, 2022 at 9:58
Why a separate Attributes table instead of columns? That's the problem here, not LINQ. You're trying to find a single product that has 3 specific attribute values but you can't perform a simple WHERE field1=@val1 AND field2=@val2 AND field3=@val3. LINQ can't do anything that can't be done in SQL and the SQL query you'd want for this would be very complex. You don't need such a schema in the first place. SQL Server allows over 30000 sparse columns. You can use XML or JSON fields to store "bags" of extra attributes too — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Oct 18, 2022 at 10:03

Svyatoslav Danyliv · Accepted Answer · 2022-10-18 10:04:45Z

2

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

answered Oct 18, 2022 at 10:04

Svyatoslav Danyliv

28.3k4 gold badges24 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

James.mikael Over a year ago

Greeeeeat!! this worked for some cases, I'll check the rest of the cases and mark this as a solution, thanks man.

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related