Linq To Entities - how to filter on child entities

I have entities Group and User.

the Group entity has Users property which is a list of Users.

User has a prop开发者_Go百科erty 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!

---

There is no "nice" way of doing this, but you could try this - project both, Group and filtered Users onto an anonymous object, and then Select just the Groups:

var resultObjectList = AllGroups.
                       Select(g => new
                               {
                                   GroupItem = g,
                                   UserItems = g.Users.Where(u => !u.IsInactive)
                               }).ToList();

FilteredGroups = resultObjectList.Select(i => i.GroupItem).ToList();

This isn't a documented feature and has to do with the way EF constructs SQL queries - in this case it should filter out the child collection, so your FilteredGroups list will only contain active users.

If this works, you can try merging the code:

FilteredGroups = AllGroups.
                 Select(g => new
                               {
                                   GroupItem = g,
                                   UserItems = g.Users.Where(u => !u.IsInactive)
                               }).
                 Select(r => r.GroupItem).
                 ToList();

(This is untested and the outcome depends on how EF will process the second Select, so it would be nice if you let us know which method works after you've tried it).

---

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!

---

try something like this and you'll still have your entities:

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

That way you should still be able to use Group.Users

---

If you want to retain your entity structure, try this:

var userGroups = context.Users.Where(u => !u.IsInactive).GroupBy(u => u.Group);

foreach (var userGroup in userGroups)
{
    // Do group stuff, e.g.:
    foreach (var user in userGroup)
    {
    }
}

And you certainly can modify your entities!

---

Use inner linq query

var FilteredGroups = (from g in AllGroups
                      select new Group()
                        {
                            ID = g.ID,
                            Name = g.Name,
                            ...
                            Users = (from user in g.Users
                                     where user.IsInactive == false
                                     select user).ToList()
                        });