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.

You don'y need to do .Inculde("ORDER_DETAILS"), it is enough to do .Include("ORDER_DETAILS.PRODUCTS"). You can try without the .Include("ORDER_DETAILS") line and see what happens. — meJustAndrew, Jun 30 '16 at 09:05
Order of the tables like ORDERS -> ORDER_DETAILS -> PRODUCTS. So I have to include ORDER_DETAILS because that contains productID. — Kadir, Jun 30 '16 at 09:13
I understand, but pleasse take a look at [msdn examples](https://msdn.microsoft.com/en-us/library/bb738708(v=vs.110).aspx), they say that, in your case for example, ORDER_DETAILS will be included if you only do .Include("ORDER_DETAILS.PRODUCTS"). — meJustAndrew, Jun 30 '16 at 09:16
Ok, I see what you mean now (good to know that, thanks). But problem still same. — Kadir, Jun 30 '16 at 11:20
The problem is that ORDER_DETAILS is a list, and each order detail has a list of product, isn't it? This is why you get the error message. Do you see, in order to get products from ORDER_DETAILS you will need to iterate through it and get the products from each element. Am I right? — meJustAndrew, Jun 30 '16 at 11:23

score 3 · Accepted Answer · answered Jul 11 '16 at 15:49

3

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

answered Jul 11 '16 at 15:49

Ivan Stoev

195,425
15
312
343

Awesome, thank you very much. I was not realize it is that simple to use entity framework ANY expression with linq dynamic. – Kadir Jul 13 '16 at 06:09
Quick question, can I use where condition with order by also? Is the usage same? – Kadir Jul 13 '16 at 13:36
I guess so, but not 100% sure. It's trial and error experience, you know :) – Ivan Stoev Jul 13 '16 at 13:48
It turns out that it's not supported:( Neither "FirstOrDefault` and similar. Basically the only supported are `Any`, `All`, `Count`, `Min`, `Max`, `Sum` and `Average`. – Ivan Stoev Jul 13 '16 at 14:57
Yea when I write any or firstordefault it gives "No applicable aggregate method 'FirstOrDefault' exists" error. – Kadir Jul 13 '16 at 15:03

score 0 · Answer 2 · edited May 23 '17 at 11:51

0

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.

edited May 23 '17 at 11:51

Community

1
1

answered Jun 30 '16 at 11:42

meJustAndrew

6,011
8
50
76

I guess I found a solution here. http://stackoverflow.com/questions/22672050/dynamic-expression-tree-to-filter-on-nested-collection-properties. But still working on it. – Kadir Jul 01 '16 at 09:18
Sorry for late response (it was holiday in my country). So actually we can't do your solution because in foreach loop order is actual item. We cannot use Include method or linq function there. Btw that unitofwork returns IQuerable. – Kadir Jul 11 '16 at 06:24
Really, with the `Select` statement? – meJustAndrew Jul 11 '16 at 08:29
@Kadir I am pretty sure this should be working, can you try it again and keep me updated? – meJustAndrew Jul 11 '16 at 08:40
Nope, same result. – Kadir Jul 11 '16 at 09:15

How to filter child collection with linq dynamic

2 Answers2

Linked