LINQ .NET can't filter on .include .select .where to filter collection on deeply nested object

Question

I am stuck on filtering a returning collection with deeply nested objects with the the following code

var my collection = await myRepository.GetDBSet<MyObjectType>()
                    .include(x => Table1)
                    .include(x => Table2)
                    .include(x => Table2.Select(y => SubTable1))
                    .include(x => Table2.Select(y => SubTable1.Users))
                    .include(x => Table2.Select(y => SubTable1.Users.Addressess))
                    .include(x => Table2.Select(y => SubTable1.Users.Roles))
                    .include(x => Table2.Select(y => SubTable1.Users.Departments))
                    .Where(w => w.id1 = _id && w.isActive = t);

This example query works, please ignore the .where clause

I want to filter the SubTable1.Users.Departments and not bring back any departs with deptId != 1

Here is what I tried on the .where section

.Where(w => w.id1 = _id && w.isActive = t &&
w.Table2.where(s => s.SubTable1.Users.Departments.First().DeptId != 1))

This does not work, the error is saying it cannot convert int to bool. I had to add the .First() since it would not allow the DeptId. I am trying to filter out any dept record that has the deptId but not sure how to do this on a deeply nested linq statement.

Thanks in advance for any help.

What is `w.Table2.where` doing? You could simply replace `where` with `Any`, and `First` with `Any` as well, and move `DeptId != 1` under second `Any` — Irakli, Jan 24 '23 at 14:01

score 0 · Answer 1 · answered Jan 24 '23 at 13:59

0

Can you try using LINQ's Any?

From Microsoft's documentation:

Determines whether any element of a sequence exists or satisfies a condition.

In your example it shuld be something like this:

.Where(w => w.id1 = _id && w.isActive = t &&
w.Table2.where(s => s.SubTable1.Users.Departments.Any(x => x.DeptId != 1))

answered Jan 24 '23 at 13:59

Emiliano Montesdeoca

364
1
9

This would filter the base objects (`MyObjectType`) on the given condition, but still include all the Departments as long as one of them has ID of 1. – Sergey Kudriavtsev Jan 24 '23 at 14:00
yes, get an error Operator && cannot be applied to operands of type bool and IEnumerable – c0micrage Jan 24 '23 at 14:07

score 0 · Answer 2 · answered Jan 24 '23 at 14:07

(Below I assume that your question contains pseudocode and answer accordingly, since your code normally wouldn't compile. I also assume you're using EF Core.)

You can theoretically do it within the Include statement like this:

var my collection = await myRepository.GetDBSet<MyObjectType>()
                    .Include(...)
                    ...
                    .Include(x => x.Table2)
                        .ThenInclude(y => y.SubTable1)
                        .ThenInclude(z => z.Users)
                        .ThenInclude(t => t.Departments.Where(d => d.Id == 1))
                    ...
                    .Where(w => w.id1 = _id && w.isActive = t);

From my experience the support for such syntax in EF Core is a bit underwhelming - for example, it only allows one such condition per LINQ query - but this is still the simplest way to filter out the related records that you don't need and select the original, tracked entities from the database.

This feature is called Filtered Include and is available starting from EF Core 5.0 (documentation)

I did try .include(x => Table2.Select(y => SubTable1.Users.Departments.where(s => s.deptId != 1))) but that did not work either — c0micrage, Jan 24 '23 at 14:26
@c0micrage Then you might want to check this question and answers: https://stackoverflow.com/questions/39636952/how-to-filter-include-entities-in-entity-framework — Sergey Kudriavtsev, Jan 24 '23 at 14:50

LINQ .NET can't filter on .include .select .where to filter collection on deeply nested object

2 Answers2