Entity Framework returns null for Include properties

Question

I got 3 entities(tables) that have many to many connections:

public class AccUserRole
{
    public long Id { get; set; }
    public string RoleName { get; set; }
    public List<AccAdGroup> Groups { get; set; }
    public List<AccScreen> Screens { get; set; }
}

public class AccAdGroup
{
    public long Id { get; set; }
    public string AdIdent { get; set; }
    public List<AccUserRole> Roles { get; set; }
}



public class AccScreen
{
    public long Id { get; set; }
    public string ScreenIdent { get; set; }
    public List<AccUserRole> Roles { get; set; }
}

I wanted to get all Roles(including their screens and groups) that has at least one of specified list of groups(the groups of the current user). So I used this query:

List<AccUserRole> userRoles = (from ur in db.AccUserRoles.Include("Groups").Include("Screens")
                               from g in ur.Groups
                               where user.Groups.Contains(g.AdIdent)
                               select ur).ToList();

It gets the right roles, but the Groups and Screens properties are null. Looks like EF has a problem with using Include and second from. Any help on how to include the properties or rewrite the query will be appreciated.

Answer 1

Eager Loading

The reason for this is that you have specified only one level of include, while your query is asking for something on the second level.

Your include lets you ask for ur.Groups and ur.Screens. The next level is from g in ur.Groups, and you haven't included that level. (This is probably unexpected for you, since you already have asked for all AccUserRoles in the first part of the query.)

To make your query run, you could add another .include at the start, going two levels deep:

from ur in db.AccUserRoles
             .Include("Groups")
             .Include("Groups.Roles")
             .Include("Screens")

If you need to go yet another level, you'd just add yet another include:

from ur in db.AccUserRoles
             .Include("Groups")
             .Include("Groups.Roles")
             .Include("Groups.Roles.Groups")
             .Include("Screens")

...etc.

This might become cumbersome if you have a whole lot of levels to nest, so an alternative would be to use Lazy Loading instead, as Praval 'Shaun' Tirubeni suggests, by adding the virtual keyword to the collections in your entities.

Answer 2

Move the include before ToList().

select ur).Include("Groups").Include("Screens").ToList();

The subselect can remove the Include effect.

If you are doing eager loading, the virtual keyword is not needed. By adding virtual, you are using lazy loading, not eager loading.

Answer 3

Try adding the virtual key word to your class properties like so:

public class AccUserRole
{
    public long Id { get; set; }
    public string RoleName { get; set; }
    public virtual List<AccAdGroup> Groups { get; set; }
    public virtual List<AccScreen> Screens { get; set; }
}

public class AccAdGroup
{
    public long Id { get; set; }
    public string AdIdent { get; set; }
    public virtual List<AccUserRole> Roles { get; set; }
}



public class AccScreen
{
    public long Id { get; set; }
    public string ScreenIdent { get; set; }
    public virtual List<AccUserRole> Roles { get; set; }
}