Set or condition in Include EF Core 6

Question

I need to Filter Products by list of related Entities. for example, I have a table related to Product Table named InventoryPrice that has Price with the color Table and I wanna filter Table Product by List of Colors

I used PredicateBuilder for defining Or in Loop:

 var PredicateI = PredicateBuilder.New<InventoryPriceModel>();
            
    foreach (var Color in Filter.Color)
    PredicateI = PredicateI.Or(i => i.colorId == Color);
    
    Query = Query
    .Include(i => i.InventoryPrice.Where(PredicateI))
    .ThenInclude(i => i.color);

and had this exception:

"Expression of type 'System.Func2[ClickStore.UseCases.InventoryPrice.Models.InventoryPriceModel,System.Boolean]' cannot be used for parameter of type 'System.Linq.Expressions.Expression1[System.Func2[ClickStore.UseCases.InventoryPrice.Models.InventoryPriceModel,System.Boolean]]' of method 'System.Linq.IQueryable1[ClickStore.UseCases.InventoryPrice.Models.InventoryPriceModel] Where[InventoryPriceModel](System.Linq.IQueryable1[ClickStore.UseCases.InventoryPrice.Models.InventoryPriceModel], System.Linq.Expressions.Expression1[System.Func`2[ClickStore.UseCases.InventoryPrice.Models.InventoryPriceModel,System.Boolean]])' (Parameter 'arg1')"

I used List.Contains in Where:

 Query = Query
         .Include(i => i.InventoryPrice 
         .Where(i => Filter.Color.Contains(x => x == i.colorId)))
         .ThenInclude(i => i.color);

and had this exception:

{"The LINQ expression 'x => x == EntityShaperExpression: \r\n    ClickStore.UseCases.InventoryPrice.Models.InventoryPriceModel\r\n    ValueBufferExpression: \r\n        ProjectionBindingExpression: EmptyProjectionMember\r\n    IsNullable: False\r\n.colorId' 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."}

I did some Searching to fixing it but still no answer

Update: I did Solutions users Mentioned and three of them works without Exception but suddenly all has no result either. this is the whole code:

Note That:

=> I changed default UseQuerySplittingBehavior to SplitQuery.

=> codes are part by part cuz they are from diffrent Parts that called for be able to reusable code in different requests.

=> I've Tested Without AsNoTracking() and SplitQuery() now, no luck

in Controller:

var Products = _ProductRepository.SetProductsView(await _ProductRepository.GetProductsClientView(Filter).Take(Take).Skip(Skip).ToListAsync());

in GetProductsClientView:

var Query = _db.Product
                .AsNoTracking()
                .Where(p => !p.productHide &&
                             p.InventoryPrice.Count > 0)
                .Include(a => a.Category)
                .Include(a => a.BrandCompany)
                    .ThenInclude(a => a.brandCompany)
                    .Include(a => a.Status)
                    .Include(a => a.Warranty)
                    .Include(a => a.ProducTag)
                    .AsQueryable();

list Filter on filtering include:

Query = Query
                        .Include(i => i.InventoryPrice
                        .AsQueryable().Where(i => Colors.Any(c => c.Equals(i.colorId))))
                        .ThenInclude(i => i.color);

Get and Calc, Recipts and Remittances for Detect how many Product is available:

return Query
              .Select(ProductModel => new ProductInventoryModel
              {
                  Product = ProductModel,

                  Recipt = _db.ProductRecipt.AsNoTracking()
                  .Where(rec => rec.productId == ProductModel.productId && !rec.productReciptDisabled)
                  .Select(rec => rec.productNumber).ToList(),

                  Remittance = _db.RemittanceProduct.AsNoTracking()
                  .Where(RemP => RemP.productId == ProductModel.productId && RemP.Remittance.Status.title == "Successful" && RemP.Remittance.Order.Status.productHasTaken)
                  .Include(RemP => RemP.Remittance)
                  .ThenInclude(Rem => Rem.Status)
                  .Include(RemP => RemP.Remittance)
                  .ThenInclude(Rem => Rem.Order)
                  .ThenInclude(Rem => Rem.Status)
                  .Select(RemP => RemP.requestNumber).ToList()

              });

if the product is Smart Mobile:

if (CategoryId == 0)
                    Query = Query.Where(PI => PI.Product.productCategoryId == _db.ProductCategory
                  .Where(C => C.productCategoryTitle == "MobilePhone")
                  .Select(C => C.productCategoryId)
                  .FirstOrDefault());
                else
                    Query = Query.Where(PI => PI.Product.productCategoryId == CategoryId);


                return Query
                  .Join(
                    _db.MobilePhone
                    .AsNoTracking()
                    .Include(M => M.displayTechnology)
                    .Include(M => M.memoryCard)
                    .Include(M => M.network)
                    .ThenInclude(M => M.network)
                    .Include(M => M.technology)
                    .ThenInclude(M => M.technology)
                    .Include(M => M.mobilePhoneOS)
                    .Include(M => M.screenSizeRange)

                    , PI => PI.Product.productId
                    , M => M.productId
                    , (PI, M) => new ProductInventoryModel
                    {

                        Product = PI.Product,
                        Recipt = PI.Recipt,
                        Remittance = PI.Remittance,
                        Properties = M

                    });

I know code is very long and confusing for understanding in the first time and maybe I could Make it better later or not but code is working with result without inserting the new filter code...

Answer 1

You can do something like this with pure LINQ,

List<YourColorEntity> colorList = ..... //Your color list

var yourQuery = Query
                     .Include(/*your related entities*/)
                     .Where(i => colorlist
                           .Any(c => c.colorId.Equals(i.colorId)));

And, in your List.Contains query, you are missing the closing parenthesis after Include.

Answer 2

You should be able to fix this by using AsQueryable on i.InventoryPrice:

Query = Query
    .Include(i => i.InventoryPrice.AsQueryable().Where(PredicateI))
    .ThenInclude(i => i.color);

But for this particular case (based on provided code and some assumptions, cause there is not enough code provided to test it) simple Contains should do the trick:

Query = Query
    .Include(i => i.InventoryPrice.Where(ip => Filter.Color.Contains(ip.colorId)))
    .ThenInclude(i => i.color);