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!

are you using EF4? If so, are you using POCO's? How is `Group.Users` declared? — RPM1984, Jan 18 '11 at 10:47
You could achieve this easily with filtered Include. Vote for the feature [here](https://entityframework.codeplex.com/workitem/47)! — Chris, Sep 27 '13 at 07:35

score 17 · Answer 1 · edited Nov 20 '16 at 22:42

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).

The second Select doesn't appear to do what is intended and filter the UserItems on the GroupItem itself, unfortunately — Stuart Dobson, Mar 15 '23 at 23:41

riezebosch · Accepted Answer · 2011-05-13T14:34:48.553

10

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!~~

edited May 13 '11 at 14:34

answered May 13 '11 at 13:37

riezebosch

1,950
16
29

can you help me with this linq query:http://stackoverflow.com/questions/38120664/how-to-group-by-on-2-child-entities-and-get-total-of-both-this-child-entities – I Love Stackoverflow Jun 30 '16 at 13:16

score 1 · Answer 3 · answered May 13 '11 at 14:47

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

score 0 · Answer 4 · answered May 16 '13 at 12:23

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!

score 0 · Answer 5 · answered Apr 25 '19 at 11:35

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()
                        });

Linq To Entities - how to filter on child entities

5 Answers5

Linked