CODEWITHSUNDEEP
c#entity-frameworkmany-to-manyef-fluent-api
Gareth
Gareth

Reputation: 5243

Entity Framework Many-Too-Many Not Populating Results

I'm trying to map a many-too-many relationship in C# EF with code first migrations.

The issue that I'm having is that no results are being populated from the related table. Take this piece of code for example, _role contains no permissions whereas it should contain 4 results:

foreach (Role _role in _dbContext.Roles) //_dbContext.Roles.Count = 2
{
    foreach(Permission _permission in _role.Permissions) //_role.Permissions.Count = 0
    {
        //Do something with each _permission
    }
}

The classes look like this:

[Table("Roles")]
public partial class Role
{
    public Role()
    {
        Permissions = new HashSet<Permission>();
    }

    [Key]
    public int RoleId { get; set; }

    public string RoleName { get; set; }
    public ICollection<Permission> Permissions { get; set; }
}

[Table("Permissions")]
public partial class Permission
{
    public Permission()
    {
        Roles = new HashSet<Role>();
    }

    [Key]
    public int PermissionId { get; set; }

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

And finally the Fluent API:

modelBuilder.Entity<Permission>()
    .HasMany(e => e.Roles)
    .WithMany(e => e.Permissions)
    .Map(m => m
        .ToTable("_Lnk_Role_Permission")
        .MapLeftKey("PermissionId")
        .MapRightKey("RoleId"));

After inspecting the database, the tables, keys and data are all in order and manually adding and querying data produces the correct results. When trying to access the data, roles are present but no permissions.

Can anybody see where it could be going wrong?

Upvotes: 0

Views: 311

Answers (2)

scottt732
scottt732

Reputation: 3937

I think you are looking for the Include statement.

foreach (var role in _dbContext.Roles.Include(x => x.Permissions)) 
{
    ...
}

The virtual keyword is good practice and plays a role in how the data is loaded. If you aren't careful, you can end up doing 1+N queries (1 for the Role object + N more for each Permission). The Include statement tells EF to effectively join the tables in the DB query so that the data is available in memory for handling your nested for loops.

Apologies for linking elsewhere, but MSDN links should be here for a while. This is a pretty good read to understand the implications of virtual/non-virtual and with/without the Include statement: https://msdn.microsoft.com/en-us/data/jj574232.aspx

Upvotes: 1

Gareth
Gareth

Reputation: 5243

Well after hours of head scratching, I missed out Virtual from the ICollection. The modified class looks like so:

public partial class Role
{
    public Role()
    {
        Permissions = new HashSet<Permission>();
    }

    [Key]
    public int RoleId { get; set; }

    public string RoleName { get; set; }
    public Virtual ICollection<Permission> Permissions { get; set; } //Notice the virtual?!
}

Upvotes: 0

Related Questions