linq query not filtering child table data

Question

query = from x in query where x.sxID == 1 && x.SecCompanies.Where(c => c.SecID == 1).Any() select x;

See the picture below only SecID "1" is suppose to show, but both records are showing.

What I'm doing wrong?



Answer 1

If I understand correctly, this query is the source for the first data grid. If so, the query is working as I would expect. The query states "choose the entity where its ssID property is 1 and where it has at least one related entity through the SecCompanies property that has a SecID of 1". Clearly, the only entity in the first data grid has a ssID of 1 and, assuming the second data grid is related to the first one through the SecCompanies property, there is at least one (in fact, only one) SecCompany that has a SecID of 1. The query has no impact on the related table, if that is what you are expecting.

---

Justin Anderson, LightSwitch Development Team

Answer 2

Thanks for the reply, I think I understand a bit what you are saying. Not sure how to do what i want to do. Basically I want to filter companies and I set the SecID == 1 to one which is "DIC" company but second company is still showing.

"If I understand correctly, this query is the source for the first data grid."

That is correct. First grid is securities exchange, then I'm trying to filter it to the company " x.SecCompanies.Where(c => c.SecID == 1)" in the second related sub grid of companies.

Answer 3

Then you need to create another query of the entity set that the second data grid uses and have it look something like:

query = from x in query where x.SecId == 1 && x.SXWithFilter.sxID = SXID;

where SXID is a parameter for the query. SXWithFilter is the navigation property from the child entity instance back to the parent entity instance. Set this query as the source of the collection to which the second data grid is bound. Then, bind the SXID parameter of the collection to the sxID of the selected item in the first collection.

---

Justin Anderson, LightSwitch Development Team

Answer 4

It is not possible to filter the first query? That would work for my screen so I marked that answer, but I would still like to know if drilling and filtering down the first query is possible or not in "_PreprocessQuery". Sounds like an interesting problem if it has a solution and would make my life much easier for some aggregate values in the first query.

Many thanks for your answer :)

Answer 5

If that rule was global biz logic, I think you could also filter the children using the new Filter interceptor ability in V2. If V1, then have to setup seperate query in screen as Justin said to further reduce result set or related children.  Or maybe an RIA service could munge the data you need.