How do I filter out child collection in EF6.1

Question

I have a Apps, AppRoles, UserAppRoles and Users. I'm trying to Get All Users but only want the AppRoles where AppId = 1. How do I filter the child collection?

using (var context = new dbContext())
            {
                var rv = context.Users
                       .Include(u => u.AppRoles);                           

            }

I tried this but throws and exception: 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

public static async Task<List<User>> GetAllAsync()
        {
            var rv = new List<User>();

            using (var context = new dbContext())
            {
                rv = await (context.Users.AsNoTracking()
                    .Include(a => a.AppRoles.Where(a2 => a2.AppId == 1)).ToListAsync());
            }

           return rv;

        }

The only way I could figure out how to get it to work is like this which I might as well just use a stored procedure at that point:

var rv = new List<User>();

            using (var context = new dbContext())
            {
               rv = context.Users.AsNoTracking()
                    .Include(a => a.AppRoles).ToList();


            }

            foreach (var user in rv)
            {
                if (user.AppRoles.Any())
                {
                    user.AppRoles = user.AppRoles.Where(r2 => r2.AppId == 1).ToList();
                }
            }

How do I write this in EF?

SELECT        
Users.UserId, 
Users.UserName
FROM          
Users 

INNER JOIN UserAppRoles ON Users.UserId = UserAppRoles.UserId 
INNER JOIN AppRoles ON UserAppRoles.AppRoleId = AppRoles.AppRoleId
WHERE AppRoles.AppId = 1

Perhaps a [Linq join](http://stackoverflow.com/questions/2767709/c-sharp-joins-where-with-linq-and-lambda). — Jasen, May 18 '17 at 19:22
Can you provider a snippet of code using the above, I'm still having issues getting this to work. thanks — Fab, May 18 '17 at 20:14
How are your relations set up? If you can write the join in SQL then the Linq query would be very similar. The lambda style is just masochistic. — Jasen, May 18 '17 at 20:26
I have Apps, AppRoles, UserAppRoles and Users table, the UserAppRoles joins the AppRoles and Users table (UserId and AppRoleId both pk) the AppsRoles is child of Apps and 1:m to UserAppRoles. — Fab, May 18 '17 at 20:35

score 0 · Answer 1 · answered May 18 '17 at 20:15

Try this:

context.Entry(user) 
        .Collection(b => b.AppRoles) 
        .Query() 
        .Where(r => r.AppId==1) 
        .Load();

where user is an AppUser entity from the context (like in your foreach example).

More info here:

https://msdn.microsoft.com/en-us/library/jj574232(v=vs.113).aspx ("Applying filters when explicitly loading related entities" section)

score 0 · Answer 2 · answered May 18 '17 at 21:40

0

You might subquery the AppRoles

var arQuery = from ar in context.AppRoles
              where ar.AppId == 1
              select ar;

var query = from u in context.Users
            join uar in context.UserAppRoles on u.UserId equals uar.UserId
            join ar in arQuery on uar.AppRoleId equals ar.AppRoleId
            select u;

answered May 18 '17 at 21:40

Jasen

14,030
3
51
68

There's no UserAppRoles object, UserAppRoles is only a navigation property on Users and on the AppRoles objects. Is it really that complicated to do an inner join with EF? I might be better off using stored procedures and ado.net and not using EF, no wonder it doesn't have a good reputation...l would be long done with this not using EF. That's also going to query the database twice which doesn't make any sense to me and seems inefficient. – Fab May 19 '17 at 12:15
It generates one SQL query. You can log the requests or watch the profiler. – Jasen May 19 '17 at 17:44

score 0 · Accepted Answer · answered May 19 '17 at 13:43

I have this working like this but still seems inefficient but guess it's better than round trips to database. Isn't this a common pattern? I can't believe a simple inner join isn't included in EF but I'm mapping to DTOs anyways so guess this will work for now until I look into a few open source Include filters that support filtering.

var rv = context.Users.Include(r => r.AppRoles).ToList().Select(u => new User()
                {
                    UserId = u.UserId,
                    AppRoles = u.AppRoles.Where(x=>x.AppId == 1).ToList()


                });

Jonathan Magnan · Answer 4 · 2018-03-06T15:56:31.200

0

Disclaimer: I'm the owner of the project Entity Framework Plus

The EF+ Query IncludeFilter allow easily filter included entities.

public static async Task<List<User>> GetAllAsync()
{
    var rv = new List<User>();

    using (var context = new dbContext())
    {
        rv = await (context.Users.AsNoTracking()
            .IncludeFilter(a => a.AppRoles.Where(a2 => a2.AppId == 1)).ToListAsync());
    }

   return rv;
}

Wiki: EF+ Query Include Filter

edited Mar 06 '18 at 15:56

answered May 19 '17 at 14:30

Jonathan Magnan

10,874
2
38
60

awesome, I'll check it out, that's exactly what I'm looking for! – Fab May 19 '17 at 15:01
It's still not working for me, roles are empty. I'm using via web api so not sure that's an issue, It's taking to long anyways so resorted to stored procedures and async ado.net which is working out much better. – Fab May 19 '17 at 19:09

How do I filter out child collection in EF6.1

4 Answers4