CODEWITHSUNDEEP
linqentity-frameworklinq-to-entitieswcf-ria-services
Yeonho
Yeonho

Reputation: 3623

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

Upvotes: 15

Views: 25465

Answers (5)

Mofaggol Hoshen
Mofaggol Hoshen

Reputation: 738

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

Upvotes: 0

Yakimych
Yakimych

Reputation: 17752

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

Upvotes: 17

Tim Williams
Tim Williams

Reputation: 883

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!

Upvotes: 0

moi_meme
moi_meme

Reputation: 9318

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

Upvotes: 1

riezebosch
riezebosch

Reputation: 2026

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!

Upvotes: 10

Related Questions