Is it possible to filter child collections during a Linq.Where()?

Question

I have an object heirarchy:

DeathStar.Floors.Departments.Rooms

At the moment, I'm only selecting floors that contain departments that have a room containing rebel scum being intimidated or injected by a droid:

var rebelScum = deathStar.Floors.Where(
                f=> f.Departments.Any(
                    d => d.Rooms.Any(
                        r => r.Occupant.Allegiance  == "Rebel"
                            && (r.InterrogationState == Interrogation.Intimidation
                                || r.InterrogationState == Interrogation.FloatyStabbyDroid)
                        )
                    )
                );

However, rebelScum will contain empty rooms in the same department as any rebel scum being interrogated.

Can I filter inside this .Where() to only return the occupied rooms?

Can you not tack on an `&& r.Rooms.Occupied` or whatever inside the second `Any()` after you check the rebel allegiance (lol) — DGibbs, Dec 16 '15 at 11:14
Search "entity framework filter child collection". E.g. http://stackoverflow.com/questions/7079378/how-to-filter-nested-collection-entity-framework-objects. Has been answered plenty of times before. You're getting the exact results of what you query: all departments where _any_ of the rooms fulfill the rest of the query. The departments will then contain _all_ their rooms, which you have to filter yourself. — CodeCaster, Dec 16 '15 at 11:15
What happens if r.Occupant is null? That might be your empty condition. — Philip Rowlands, Dec 16 '15 at 11:16
@DGibbs I can, but it will still return the `Floors` where that condition is true, which will contain the departments and the empty rooms. — StuperUser, Dec 16 '15 at 11:16
"However, rebelScum will contain empty rooms" - no, it will only contain the *floors*. Your query is for floors, not rooms. If you only want rooms (or departments) that's a different matter. — Jon Skeet, Dec 16 '15 at 11:18
@JonSkeet What I want is floors with departments and only the occupied rooms. It feels like I'm going to have to do a few `.Select()`s. — StuperUser, Dec 16 '15 at 11:21
@StuperUser: Yes, because you want a **projection** of "floors including empty rooms" to "floors not including empty rooms". That projection itself contains filtering, but it's still a projection. — Jon Skeet, Dec 16 '15 at 11:22

Kaido · Answer 1 · 2015-12-16T11:35:49.570

3

  IEnumerable<Room> roomsOccupiedByRebelScum = deathStar.Floors.SelectMany(f => f.Departments)
                                                .SelectMany(d => d.Rooms)
                                                .Where(r => r.Occupant != null && r.Occupant.Allegiance == "Rebel" && 
                                                (r.InterrogationState == Interrogation.Intimidation || r.InterrogationState == Interrogation.FloatyStabbyDroid)
                                                );

If you want to filter rooms, they must form the result of the query

edited Dec 16 '15 at 11:35

answered Dec 16 '15 at 11:25

Kaido

3,383
24
34

Thanks Kaido, I need floors with departments and only the occupied rooms. So, I'm going to have to do a few `Select()`s to filter and end up with an `IEnumerable` – StuperUser Dec 16 '15 at 11:26
1

You change the where clause as you see fit now that rooms are the result, I updated my answer to include a null check on occupant (this would be my definition of 'occupied only') – Kaido Dec 16 '15 at 11:36

Is it possible to filter child collections during a Linq.Where()?

1 Answers1