Explicit Loading nested related models in Entity Framework

Question

I'm working on an ASP.NET MVC5 project using EF6. I have 3 models: user, role and permission.

The relation between user and role is many to many. The relation between role and permission is many to many.

LazyLoadingEnabled is disabled in the database context.

public class Permission
{
    public int ID { get; set; }
    public string Name { get; set; }
    public virtual ICollection<Role> Roles { get; set; }
}

public class Role
{
    public int ID { get; set; }
    public string Name { get; set; }
    public virtual ICollection<Permission> Permissions { get; set; }
    public virtual ICollection<User> Users { get; set; }
}

public class User
{
    public int ID { get; set; }
    public string Username { get; set; }
    public string DisplayName { get; set; }

    public virtual ICollection<Role> Roles { get; set; }
}

public class TaskManagerDB : DbContext
{
    public TaskManagerDB() : base()
    {
        Configuration.LazyLoadingEnabled = false;
    }

    public DbSet<Role> Roles { get; set; }
    public DbSet<Permission> Permissions { get; set; }
    public DbSet<User> Users { get; set; }
}

At an earlier point, I fetch a given user:

User user = db.Users.Find(1);

Now: since I already have the model, how can I load the user roles with their permissions?

I have tried:

db.Entry(user).Collection(x => x.Roles).Query().Include(y => y.Permissions).Load();

But it's not working - user.Roles is still null.

The following solution is not acceptable because I already have the user Model:

User user = db.Users.Include("Roles.Permissions").Where(x => x.ID == 1).FirstOrDefault();

Answer 1

What you have tried

db.Entry(user).Collection(x => x.Roles).Query()
    .Include(y => y.Permissions)
    .Load();

is indeed the intended way. And it works for everything else except the many-to-many relationship with implicit junction table, which is the case here.

I don't know if it's a bug or "by design", but the solution is to Include the other (calling) end of the relationship, e.g.

db.Entry(user).Collection(x => x.Roles).Query()
    .Include(y => y.Users) // <--
    .Include(y => y.Permissions)
    .Load();