How to filter on child entities in Linq To Entities

entity-framework linq linq-to-entities wcf-ria-services

Question

I have things.Group and User .
the Group company hasUsers which has a list of Users as a property.
The property called "User"IsEnabled .

The linq query I want to create should return a list ofGroup s, which solely includesUser Who isIsEnabled is real.

therefore, given the data below, as an example
AllGroups
A Group
(IsEnabled = true) User 1
(IsEnabled = true) User 2
(IsEnabled = false for User 3)

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

I wish to acquire
FilteredGroups
A Group
(IsEnabled = true) User 1
(IsEnabled = true) User 2

B Group
(IsEnabled = true) User 4

The following is what I attempted, however Visual Studio informs me that
Users is a read-only property that cannot be assigned to.

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

I appreciate your support.

1
15
1/18/2011 4:46:47 AM

Accepted Answer

I was able to achieve this by reversing the question:

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

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

The postponed execution might interfere if you didn't induce a roundtrip to the database by utilizing the ToList() method. Only the obtained data is reordered by the second query.

It's possible that you won't be able to update your entities after that!

9
5/13/2011 2:34:48 PM

Popular Answer

Although there is no "good" method to achieve this, you may try projecting both,Group with filtersUsers upon a hidden item, after whichSelect just theGroups :

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

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

This is an undocumented feature that has to do with how EF builds SQL queries; in this instance, it should filter out the child collection so that your query doesn't return any results.FilteredGroups There will only be active users on the list.

If it succeeds, try merging the code:

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

(This has not been tested; the result will depend on how EF handles the secondSelect , therefore it would be wonderful if you could let us know after trying it which approach worked).



Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow