Linq To Entities - how to filter on child entities

Question

I have entities Group and User.
the Group entity has Users property which is a list of Users.
User has a property named IsEnabled.

I want to write a linq query that returns a list of Groups, which only consists of Users whose IsEnabled is true.

so for example, for data like below
AllGroups
Group A
User 1 (IsEnabled = true)
User 2 (IsEnabled = true)
User 3 (IsEnabled = false)

Group B
User 4 (IsEnabled = true)
User 5 (IsEnabled = false)
User 6 (IsEnabled = false)

I want to get
FilteredGroups
Group A
User 1 (IsEnabled = true)
User 2 (IsEnabled = true)

Group B
User 4 (IsEnabled = true)

I tried the following query, but Visual Studio tells me that
[Property or indexer 'Users' cannot be assigned to -- it is read only]

FilteredGroups = AllGroups.Select(g => new Group()
                    {
                        ID = g.ID,
                        Name = g.Name,
                        ...
                        Users = g.Users.Where(u => u.IsInactive == false)
                    });

thank you for your help!

Answer 1

I managed to do this by turning the query upside down:

var users = (from user in Users.Include("Group")
             where user.IsEnabled
             select user).ToList().AsQueryable()

from (user in users
      select user.Group).Distinct()

By using the ToList() you force a roundtrip to the database which is required because otherwise the deferred execution comes in the way. The second query only re-orders the retrieved data.

Note: You might not be able to udpate your entities afterwards!