Filter on collection inside collection

Question

I have some classes modeling a guest user account and among others the guest's state and the guest's host:

public class GuestUser
{
    public string LoginID {get; set;}

    public List<GuestDetails> GuestDetailsList {get; set;}
}

public class GuestDetails
{
    public GuestGroupUserState State {get; set;}

    public GuestCreator GuestCreator {get; set;}
    public GuestUser GuestUser {get; set;}
}

public class GuestCreator
{
    public string CreatorLoginID {get; set;}

    public List<GuestDetails> GuestDetailsList {get; set;}
}

public enum GuestGroupUserState 
{
    Unknown = 0,
    Active = 1,
    Locked = Active << 1,
    Deleted = Locked << 1
}

I want to find all GuestUsers and the respective GuestDetails for a given GuestCreator gc that are not in Deleted-State.

So far, I tried

var guestUsers = gc.GuestDetailsList
    .Where(gd => gd.State != GuestGroupUserState.Deleted)
    .Select(gd => gd.GuestUser);

but this obviously gives me all the GuestDetails for the users, also the ones in Deleted state.

Next, I tried with

var guestUsers = userRepo.GuestUsers
    .Where(gu => gu.GuestDetails
        .Any(gd => gd.State != GuestGroupUserState.Deleted && gd.GuestCreator.Equals(gc)));

but again this gives me all GuestDetails for these users, including the ones in Deleted-state.

Is there a way to get all GuestUsers that have a GuestDetail in non-deleted state and for these users, get only the non-deleted GuestDetails? If a GuestUser has only deleted details, it shouldn't appear in the list at all. At the moment, I'm foreach()ing through my user lists generated with the code snippets above and removing the mismatched details entries from each guest - not the most efficient way I think.

@IainGalloway Atm, this is in memory. In the future, I'll query against a database using EF. — Thaoden, May 06 '15 at 16:06

Iain Galloway · Accepted Answer · 2015-05-06T16:33:55.520

2

This "isn't possible" in a strict sense. You can't "filter" the child collections; you need to allocate new ones:-

var guestUsers = ...; // Wherever they're coming from
var filteredUsers = guestUsers
        .Where(x => x.GuestDetails.Any(y => y.State != GuestGroupUserState.Deleted))
        .Select(x => new GuestUser()
                {
                  LoginId == x.LoginId,
                  GuestDetails == x.GuestDetails
                                   .Where(y => y.State != GuestGroupUserState.Deleted)
                                   .ToList()
                });

Or you might instead want to add a property to your GuestUser object:-

public List<GuestDetails> ActiveGuestDetailsList
{
  get { return this.GuestDetailsList.Where(y => y.State != GuestGroupUserState.Deleted); }
}

If you're using EF or similar you'll want to perform the filtering on the database and project onto a DTO instead.

edited May 06 '15 at 16:33

answered May 06 '15 at 16:09

Iain Galloway

18,669
6
52
73

How did you compare `y.State != GuestGroupUserState.Deleted` . I've just tried and it says that it doesn't implement operator == – Stanimir Yakimov May 06 '15 at 16:13
What do you mean by "perform the filtering on the database"? – Thaoden May 06 '15 at 16:15
The OP performs the comparison `gd.State != GuestUserState.Deleted` in his example code. I assume that either it's a typo, or there's an implicit conversion. – Iain Galloway May 06 '15 at 16:15
It's a typo... Should be `GuestGroupUserState` everywhere, I'll fix it in the post. – Thaoden May 06 '15 at 16:16
@Thaoden: I mean, if the records are coming from the database then you don't want to pull all the records into memory and then filter them in memory. You want to perform the query *on the database*. – Iain Galloway May 06 '15 at 16:16
@IainGalloway Well, that's what Linq is for, I thought? `dbContext.GuestUsers.Select(...)` should retrieve only the items I asked for and not perform the filtering in memory. Or am I wrong here? – Thaoden May 06 '15 at 16:18
Untill you say `ToList()`, the collection is still `IQueryable` which is a bit faster. – Stanimir Yakimov May 06 '15 at 16:19
Well, first of all, if you're using `new` inside your query, you won't be able to [track changes](https://msdn.microsoft.com/en-us/library/vstudio/dd456848%28v=vs.100%29.aspx) because the objects you have in memory are a projection rather than an attached entity. – Iain Galloway May 06 '15 at 16:22
More importantly, if you're performing this kind of query with *any* O/RM you need to be acutely aware of the [N+1 selects problem](http://stackoverflow.com/questions/97197/what-is-the-n1-selects-issue) (which my second example would trigger). – Iain Galloway May 06 '15 at 16:23
If you can't afford to use a projection, you'll need to use something like https://github.com/jbogard/EntityFramework.Filters – Iain Galloway May 06 '15 at 16:26
related:- http://stackoverflow.com/questions/7079378/how-to-filter-nested-collection-entity-framework-objects – Iain Galloway May 06 '15 at 16:26
Is there a way in this solution to avoid having guests with empty `GuestDetails`? Something better than a `Where(gu => gu.GuestDetails.Count() != 0)`? – Thaoden May 06 '15 at 16:32
So if I got it right, assuming I don't worry wether these entities are attached to the context or not, possibly using `AsNoTracking`, I can safely use your code snippet without blowing up my database or the access times to it? – Thaoden May 06 '15 at 16:41
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/77114/discussion-between-iain-galloway-and-thaoden). – Iain Galloway May 06 '15 at 17:29

Alexandre Severino · Answer 2 · 2015-05-06T16:17:33.567

1

With your current model architecture, you can't get rid of GuestUser's deleted GuestDetails on the GuestDetailsList. To achieve your goal, you'll need to change the

public List<GuestDetails> GuestDetailsList {get; set;}

to:

public GuestDetails GuestDetails {get; set;}

on your GuestUser class. With a one-to-one relationship between GuestUser and GuestDetails you won't have such problems and will be able to get the correct information via:

var guestUsers = gc.GuestDetailsList
    .Where(g => g.State != GuestGroupUserState.Deleted)
    .Select(g => g.GuestUser);

edited May 06 '15 at 16:17

answered May 06 '15 at 16:06

Alexandre Severino

1,563
1
16
38

That gives me all `GuestUser`s that have a non-deleted state, along with all their `GuestDetail`s - even the deleted ones. – Thaoden May 06 '15 at 16:07
I've edited it with a more appropriated answer. If you really need to stick with the one-to-many relationship between `GuestUser` and `GuestDetails`, then you should go with Iain Galloway's answer. – Alexandre Severino May 06 '15 at 16:19
Yes, the one-to-many is a requirement, I didn't like it when it came up :/ Thanks anyway! – Thaoden May 06 '15 at 16:21
Can you please elucidate why you need the one-to-many relationship? Maybe we can find a work-around on that. – Alexandre Severino May 06 '15 at 16:23
It has to be possible that a single `GuestUser` can be in more than one group. I left the groups out for brevity, but in addition to the creator, the `GuestDetail` has a reference to the group. – Thaoden May 06 '15 at 16:25

Filter on collection inside collection

2 Answers2