EF Core 2.0 Many to Many relationship

Question

I am trying to make my many to many relationship work in my application.

My models:

public class BaseEntity
{
    [Key, Required]
    public Guid Id { get; set; }

    public DateTime Created { get; set; }

    #region reference
    public Guid CreatedBy { get; set; }
    #endregion

    public BaseEntity()
    {
        Id = Guid.NewGuid();
        Created = DateTime.Now;
    }
}

public class Person : BaseEntity
{
    /// <summary>
    /// Firstname of the person. Required
    /// </summary>
    [Required]
    public string FirstName { get; set; }

    /// <summary>
    /// Middlename of the contact person. Not required
    /// </summary>
    public string MiddleName { get; set; }

    /// <summary>
    /// Lastname of the contact person. Required
    /// </summary>
    [Required]
    public string LastName { get; set; }

    public Gender Gender { get; set; }

    [NotMapped]
    public string FullName
    {
        get
        {
            StringBuilder sb = new StringBuilder();

            sb.Append(FirstName);
            if (!string.IsNullOrEmpty(MiddleName))
                sb.Append($" {MiddleName}");
            sb.Append($" {LastName}");

            return sb.ToString().TrimEnd(' ');
        }
    }
}

public class User : Person
{
    #region public properties
    [Required]
    public string UserName { get; set; }
    [Required]
    public string Email { get; set; }
    [Required]
    public byte[] Password { get; private set; }
    public byte[] Salt { get; set; }
    public int WorkFactor { get; set; }

    public virtual ICollection<UserRole> UserRoles { get; set; }

    #endregion

    #region constructor

    public User() : base()
    {
        //security settings
        Salt = SecurityHelper.GenerateSalt(12);
        WorkFactor = 5;
        UserRoles = new List<UserRole>();
    }
    #endregion

    #region public methods

    public void SetPassword(string password)
    {
        Password = SecurityHelper.GenerateHash(Encoding.UTF8.GetBytes(password), Salt, WorkFactor, 128);
    }
    #endregion

    public bool ComparePassword(string password)
    {
        return StructuralComparisons.StructuralEqualityComparer.Equals(Password, SecurityHelper.GenerateHash(Encoding.UTF8.GetBytes(password), Salt, WorkFactor, 128));
    }
}

public class Role : BaseEntity
{
    #region public properties
    [Required]
    public string ReadableId { get; set; }
    [Required]
    public string Name { get; set; }
    public string Description { get; set; }

    public virtual ICollection<SecurityPrivilege> Privileges { get; set; } = new List<SecurityPrivilege>();
    public virtual ICollection<UserRole> UserRoles { get; set; }

    #endregion

    #region constructor

    public Role() : base()
    {
        UserRoles = new List<UserRole>();
    }
    #endregion
}

public class UserRole
{
    public Guid UserId { get; set; }
    public Guid RoleId { get; set; }

    [ForeignKey("UserId")]
    public User User { get; set; }
    [ForeignKey("RoleId")]
    public Role Role { get; set; }
}

Now, what I am trying to accomplish, is Users being able to belong to any number of Roles.

In my seeder, I add records to the UserRoles list of the User, which works as long as storing the data in the table.

I am just not able to retrieve this data again, when I query in my controller (in an API):

                var user = _context.Users.Include(x => x.UserRoles).FirstOrDefault(u => u.UserName == request.UserName);

Here, the UserRoles list are always empty, even though I can see in the tables, that I ought to have rows, as the UserId fields matches the User object.

The context definition:

public class TMContext : DbContext
{
    public TMContext(DbContextOptions<TMContext> options) : base(options)
    {

    }

    #region system sets
    public DbSet<ServerSetting> Settings { get; set; }
    public DbSet<User> Users { get; set; }
    public DbSet<Role> Roles { get; set; }
    public DbSet<SecurityItem> SecurityItems { get; set; }
    public DbSet<UserSession> UserSessions { get; set; }
    public DbSet<SecurityRight> SecurityRights { get; set; }
    public DbSet<SecurityOrgGroup> SecurityOrgGroups { get; set; }
    public DbSet<SecurityPrivilege> Privileges { get; set; }
    #endregion

    #region Core sets
    public DbSet<Tournament> Tournaments { get; set; }
    public DbSet<TournamentSetting> TournamentSettings { get; set; }
    public DbSet<Club> Clubs { get; set; }
    public DbSet<Team> Teams { get; set; }
    public DbSet<TournamentClass> Classes { get; set; }
    public DbSet<Group> Groups { get; set; }
    public DbSet<MatchSet> MatchSets { get; set; }
    #endregion

    #region global sets
    public DbSet<GeoPoint> GeoPositions { get; set; }
    public DbSet<Country> Countries { get; set; }
    #endregion

    protected override void OnModelCreating(ModelBuilder builder)
    {
        base.OnModelCreating(builder);

        builder.Entity<UserRole>()
            .HasKey(t => new { t.UserId, t.RoleId });

        builder.Entity<UserRole>()
            .HasOne(ur => ur.User)
            .WithMany(l => l.UserRoles)
            .HasForeignKey(ur => ur.UserId);

        builder.Entity<UserRole>()
            .HasOne(ur => ur.Role)
            .WithMany(l => l.UserRoles)
            .HasForeignKey(ur => ur.RoleId);

        builder.Entity<ItemRight>()
            .HasKey(t => new {t.SecurityItemId, t.SecurityRightId});

    }
}

My seeder code:

public static class DbContextExtension
{

    public static bool AllMigrationsApplied(this DbContext context)
    {
        var applied = context.GetService<IHistoryRepository>()
            .GetAppliedMigrations()
            .Select(m => m.MigrationId);

        var total = context.GetService<IMigrationsAssembly>()
            .Migrations
            .Select(m => m.Key);

        return !total.Except(applied).Any();
    }

    public static void EnsureSeeded(this TMContext context)
    {
        #region users
        //add admin user
        if (!context.Users.Any())
        {
            User admin = new User
            {
                UserName = "admin",
                FirstName = "Admin",
                LastName = "User",
                Email = "mail@email.org",
            };
            admin.CreatedBy = admin.Id;
            admin.SetPassword("admin");
            context.Users.Add(admin);
            context.SaveChanges();
        }
        Guid adminId = context.Users.First(x => x.UserName == "admin").Id;
        #endregion

        #region server settings
        //server settings
        if (!context.Settings.Any())
        {
            context.Settings.Add(new ServerSetting
            {
                Key = "ServerType",
                Section = "Public",
                SortOrder = 0,
                TargetDataType = "System.String",
                Value = "TMLocalServer"
            });
            context.Settings.Add(new ServerSetting
            {
                Key = "Instance",
                Section = "Public",
                SortOrder = 1,
                TargetDataType = "System.Guid",
                Value = Guid.NewGuid().ToString()
            });
            context.SaveChanges();
        }
        #endregion

        #region security rights
        //security rights
        if (!context.SecurityRights.Any())
        {
            context.SecurityRights.Add(new SecurityRight { Name = "Read", CreatedBy = adminId });
            context.SecurityRights.Add(new SecurityRight { Name = "Insert", CreatedBy = adminId });
            context.SecurityRights.Add(new SecurityRight { Name = "Update", CreatedBy = adminId });
            context.SecurityRights.Add(new SecurityRight { Name = "Delete", CreatedBy = adminId });
            context.SaveChanges();
        }
        #endregion

        #region org groups
        //org groups
        if (!context.SecurityOrgGroups.Any())
        {
            #region security groups

            context.SecurityOrgGroups.Add(new SecurityOrgGroup
            {
                ReadableId = "SecurityManagement",
                ParentId = null,
                Text = "Security Management",
                Description = "All seetings concerning basic security",
                CreatedBy = adminId
            });
            context.SaveChanges();
            #endregion

            context.SecurityOrgGroups.Add(new SecurityOrgGroup
            {
                ReadableId = "MasterFiles",
                ParentId = null,
                Text = "Master Files",
                Description = "Management of the Master Files for the application",
                CreatedBy = adminId
            });

            #region operations
            context.SecurityOrgGroups.Add(new SecurityOrgGroup
            {
                ReadableId = "Operations",
                ParentId = null,
                Text = "Operations",
                Description = "Security settings for operations functions",
                CreatedBy = adminId
            });
            context.SaveChanges();
            #endregion
        }
        #endregion

        #region security items
        //security items
        if (!context.SecurityItems.Any())
        {
            SecurityItem item = new SecurityItem { ReadableId = "UserManagement", Name = "User Management", Description = "User management and role membership", CreatedBy = adminId };
            AddItemToGroup(context, "SecurityManagement", item);
            AddAvailableRights(context, item, new List<string> { "Read", "Insert", "Update", "Delete" });
            context.SecurityItems.Add(item);

            item = new SecurityItem { ReadableId = "RoleManagement", Name = "Role Management", Description = "Manage roles and their configuration", CreatedBy = adminId };
            AddItemToGroup(context, "SecurityManagement", item);
            AddAvailableRights(context, item, new List<string> { "Read", "Insert", "Update", "Delete" });
            context.SecurityItems.Add(item);
            context.SaveChanges();

            item = new SecurityItem { ReadableId = "CountryManagement", Name = "Country Management", Description = "Manage list of countries", CreatedBy = adminId };
            AddItemToGroup(context, "MasterFiles", item);
            AddAvailableRights(context, item, new List<string> { "Read", "Insert", "Update", "Delete" });
            context.SecurityItems.Add(item);
            context.SaveChanges();

        }
        #endregion

        #region security roles
        //security roles
        if (!context.Roles.Any())
        {
            context.Roles.Add(new Role
            {
                ReadableId = "SysAdmin",
                Name = "System Administrator",
                Description = "Users can do everything",
                CreatedBy = adminId
            });
            context.SaveChanges();

            AddUserToRoles(context, "admin", new List<string> { "SysAdmin" });
            AddPrivilegesToRole(context, "SysAdmin", "UserManagement", new List<string> { "Read", "Insert", "Update", "Delete" });
            AddPrivilegesToRole(context, "SysAdmin", "RoleManagement", new List<string> { "Read", "Insert", "Update", "Delete" });
            AddPrivilegesToRole(context, "SysAdmin", "CountryManagement", new List<string> { "Read", "Insert", "Update", "Delete" });
            context.SaveChanges();
        }
        #endregion


    }

    #region security helpers

    private static void AddAvailableRights(TMContext dbcontext, SecurityItem item, List<string> rights)
    {
        foreach (string right in rights)
        {
            var r = dbcontext.SecurityRights.FirstOrDefault(x => x.Name == right);
            if (r != null)
            {
                ItemRight ir = new ItemRight { SecurityItemId = item.Id, SecurityRightId = r.Id };
                item.ItemRights.Add(ir);
            }
        }
    }

    private static void AddPrivilegesToRole(TMContext dbcontext, string roleid, string itemid, List<string> rights)
    {
        SecurityItem item = dbcontext.SecurityItems.FirstOrDefault(x => x.ReadableId == itemid);
        Role role = dbcontext.Roles.FirstOrDefault(x => x.ReadableId == roleid);
        if (item != null && role != null)
            foreach (string right in rights)
            {
                var r = dbcontext.SecurityRights.FirstOrDefault(x => x.Name == right);
                if (r != null)
                {
                    SecurityPrivilege privilege = new SecurityPrivilege { CreatedBy = item.CreatedBy, SecurityItemId = item.Id, SecurityRightId = r.Id, RoleId = role.Id, SortOrder = rights.IndexOf(right)};
                    role.Privileges.Add(privilege);
                }
            }
    }

    private static void AddItemToGroup(TMContext dbcontext, string groupid, SecurityItem item)
    {
        SecurityOrgGroup group = dbcontext.SecurityOrgGroups.FirstOrDefault(x => x.ReadableId == groupid);
        if (group != null)
            item.OrgGroupId = group.Id;
    }

    private static void AddOrgGroupToParent(TMContext dbcontext, string parentid, SecurityOrgGroup newgroup)
    {
        SecurityOrgGroup parent = dbcontext.SecurityOrgGroups.FirstOrDefault(x => x.ReadableId == parentid);
        if (parent != null)
        {
            newgroup.ParentId = parent.Id;
            dbcontext.SecurityOrgGroups.Add(newgroup);
        }
    }

    private static void AddUserToRoles(TMContext dbcontext, string username, List<string> roles)
    {
        User user = dbcontext.Users.FirstOrDefault(x => x.UserName == username);
        if (user != null)
        {
            foreach (string readablerole in roles)
            {
                Role role = dbcontext.Roles.FirstOrDefault(x => x.ReadableId == readablerole);
                if (role != null)
                    user.UserRoles.Add(new UserRole { User = user, Role = role });
            }
        }
    }

    private static void AssignRightsToItem()
    {

    }
    #endregion
}

Answer 1

I can't really see any errors in your code. Perhaps there are problems when seeding or the request.UserName contains an non-existant username.

If you consider the following seeding code (somewhat adapted to fit your entities):

var users = new User[]
{
    new User{ Id = Guid.NewGuid(), FirstName= "Bob", LastName ="b", UserName = "bob" },
    new User{ Id = Guid.NewGuid(), FirstName= "Alice", LastName ="a", UserName = "alice" }
};

var roles = new Role[]
{
    new Role{ Id = Guid.NewGuid(), CreatedBy = users[0].Id, Name = "role1", ReadableId="role1" },
    new Role{ Id = Guid.NewGuid(), CreatedBy = users[0].Id, Name = "role2", ReadableId="role2" }
};

_context.Users.AddRange(users);
_context.Roles.AddRange(roles);
_context.SaveChanges();

var userroles = new UserRole[]
{
    new UserRole{ User = users[0], Role = roles[0] }, 
    new UserRole{ User = users[0], Role = roles[1] }, //bob is assigned 2 roles
    new UserRole{ User = users[1], Role = roles[1] }, 
};

and you later call the following method:

var user = _context.Users.Include(x => x.UserRoles).FirstOrDefault(u => u.UserName == "bob");

then the user will contain 2 UserRoles. Verified in Watch window:

== Edit ==

You say the Role property of your UserRoles equals null. You'll need to include these by using the ThenInclude() extension method. This method allows you to include navigation properties that are several levels deeper, like so:

var user = _context.Users
        .Include(u => u.UserRoles)
        .ThenInclude(ur => ur.Role)
        .FirstOrDefault(u => u.UserName == "bob");