Filtering Entity Collections

Question

I want to be able to filter a child collection and only return items in that child collection which match certain conditions. Here's the code that I have now:

var q = from u in context.DbContext.Users select u;

q = q.Include(u => u.UserRoles.Select(ur => ur.Role))
    .Where(u=> u.UserRoles.Any(ur=> ur.EnvironmentId == environmentId)
);

My issue with this code is that this is also returning UserRole objects in the UserRole collection that do not match.

For example, if my environmentId variable has a value of 1, in only want the UserRoles returned in the collection if they have a value of 1 for the EnvironmentId property.

As of right now, it is returning every UserRole regardless of the EnvironmentId value.

Edit

This is not a duplicate question as Gert Arnold has suggested. I do not want to create new or anonymous objects, and the solution i proposed below solves this problem, whereas the article linked to by Gert Arnold does not.

this is not a duplicate, as i do not want to end up with new or anonymous objects. the solution i proposed solves this problem, and the other topic you linked to does not. — KC Coder, Aug 10 '16 at 20:31
The anon. types are only an intermediary result. The problem with Load() is that you can only use it to load a partial collection of *one* entity. It does work, of course, but it's hard to reuse the code for selection of multiple entities. The real issue here, by the way, is that EF never implemented filtered Includes even though it's a virtually indispensable feature. — Gert Arnold, Aug 11 '16 at 09:04

Noémie Lord · Answer 1 · 2016-08-10T16:37:23.927

0

Your Where condition is not applied to the right collection. Here, you are applying the Where to the User collection so that it will only return users that have at least one role where EnvironmentId is 1. What you want to do instead is apply that to your Role collection to only join the ones you want. this doesn't work~~I believe something like this should work:~~

q = q.Include(
    u => u.UserRoles.Where(ur => ur.EnvironmentId == environmentId)
        .Select(ur => ur.Role))

What you can do instead would be to return a new object via a select (I'm getting into unsure territory right now :)

q = q.Select(u => 
    new {
        User = u, 
        Roles = u.UserRoles.Where(ur => ur.EnvironmentId == environmentId)
    };

Now here comes the weird part... this will return you an anonymous object where the User propertie is your returned user, and Roles, your returned roles. If you wish you create a new class so that you can carry that value around outside of the scope of that block.

new class

public class UserWithRoles
{
    Public User User {get; set;}
    IEnumarable<Roles> Roles {get; set;}
}

query

q => q.Select(u => 
    new UserWithRoles() {
        User = u,
        Roles = u.UserRoles.Where(ur => ur.EnvironmentId == environmentId)
    };

That way you can declare a List<UserWithRoles> UserList and you could do UserList = q.ToList(); This might not be (probably is not) the best way to do it, but it is one way I believe it will work. If anyone is moire knowledgeable than me in LINQ's Include and knows how to make this work better, please post another answer or comment this one, I'd like to know too :)

edited Aug 10 '16 at 16:37

answered Aug 10 '16 at 16:07

Noémie Lord

751
4
22

That appears to be putting me in the right direction, but when running this code i'm getting the error: The Include path expression must refer to a navigation property defined on the type. Use dotted paths for reference navigation properties and the Select operator for collection navigation properties. I believe this is because the Role object is not included? how do i include it before selecting it back out? – KC Coder Aug 10 '16 at 16:15
@KCCoder I'm not too familiar with the `Include` function, but I would try Including the UserRoles, then Including the Roles. I'm really not sure this would work however, you might have to do a `Select` instead. – Noémie Lord Aug 10 '16 at 16:18
I'm not sure what needs to be done here to change it to a `Select` instead. – KC Coder Aug 10 '16 at 16:23
I looked at your updated example, and i appreciate your efforts but yes, i would need to do this without returning a new/different object. I would need to return the same Users object collection with just the matching UserRoles. If someone can provide an example without having to declare a new object that would be great. – KC Coder Aug 10 '16 at 16:43
Unfortunately I tried looking online for other ways to filter an `Include` and I have not be able to find anything apart from why I did in my updated answer. Otherwise my advice would be to do 2 separate queries and use them side by side. – Noémie Lord Aug 10 '16 at 16:50
There really has to be a more elegant solution to this if anyone else can provide some input. I would like to return a User object that has a UserRole collection which only contains the UserRole objects which match in the Where clause. thanks! – KC Coder Aug 10 '16 at 19:11
Another thing you can consider is to use a stored procedure instead. This would allow you to code your join condition in the SQL and then retreive the final results through your Entity Framework context. – Noémie Lord Aug 10 '16 at 22:07

score 0 · Answer 2 · answered Aug 10 '16 at 19:12

you might consider just returning a list of userroles and you can select the users from this list if you need the user objects

var roles = from ur in context.DbContext.UsersRoles.Include("User")
    where ur.EnvironmentId == environmentId
    select ur;

var users = roles.SelectMany(a => a.Users).Distinct();

score 0 · Answer 3 · answered Aug 10 '16 at 20:18

using the example provided here Filtering Related Entity Collections I came up with what seems to be a clear and elegant solution to the problem. this only loads the items in the collection if they match, and doesn't require creating any anonymous objects. (note: LazyLoading must be turned off for explicit loading to work)

User user;

var data = from u in context.DbContext.Users select u;

user = data.FirstOrDefault();

// load UserRoles and UserRoles.Role
context.Entry(user)
    .Collection(u => u.UserRoles)
    .Query()
    .Include(ur => ur.Role)
    .Where(ur => ur.EnvironmentId == environmentId)
    .Load()
;

Filtering Entity Collections

Edit

3 Answers3