How to filter child collection with linq dynamic

Question

I'm trying to filter results for user request. For instance you have orders and order details and products is child collection.

When user wants to filter by product I'm getting an error because of No property or field 'PRODUCTS' exists in type 'ICollection1'`

I'm writing my query like this.

var orders = _uow.Repository<ORDERS>()
    .Query()
    .Where("PRODUCTS.HEADING.ToLower().Contains(\"foo\")")
    .Include("ORDER_DETAILS")
    .Include("ORDER_DETAILS.PRODUCTS")
    .ToList();

So it's not possible to filter child collection like this? Or any way to filter?

Thanks.

Answer 1

The problem is that ORDER_DETAILS is a list, and each order detail has a list of product? This is why you get the error message. In order to get products from ORDER_DETAILS you will need to iterate through it and get the products from each element.

You can try:

var orders = _uow.Repository<ORDERS>()
    .Query()
    .Where("PRODUCTS.HEADING.ToLower().Contains(\"foo\")")
    .Include(x=>x.ORDER_DETAILS.Select(y => y.PRODUCTS));

It seems like now you have the problem described in this question. Hope it works well now.

Answer 2

From the way you named your classes/properties it's hard to guess which one is a single object and which one is a collection property.

If ORDERS class property ORDER_DETAILS is a collection of ORDER_DETAILS class, and ORDER_DETAILS class property PRODUCTS is a singe object of PRODUCTS class having a string property HEADINGS, then the following should do the trick:

.Where("ORDER_DETAILS.Any(PRODUCTS.HEADING.ToLower().Contains(\"foo\"))")

It's basically the same as static query with lambda parameters skipped

.Where(o => o.ORDER_DETAILS.Any(d => d.PRODUCTS.HEADING.ToLower().Contains("foo")))