Custom class Identity / Entity Framework many-to-many relationship

Question

I'm using Entity Framework, Asp.net Identity and MVC in c# in order to create my website.

Trying to implement a "group" feature I've been facing a problem... I implemented the casual (based on this : Create code first, many to many, with additional fields in association table):

public class ApplicationUser : IdentityUser
{

    [...]

    public virtual List<Group> Groups { get; set; }
}

And :

public class Group
{
    public int GroupID { get; set; }
    [Required]
    [StringLength(100, MinimumLength = 2)]
    public string Name { get; set; }

    public virtual List<ApplicationUser> Members { get; set; }
}

And the link class with an extra attribute (status) :

public class ApplicationUserGroup
{
    [Key, Column(Order = 0)]
    public string ApplicationUserId { get; set; }
    [Key, Column(Order = 1)]
    public int GroupId { get; set; }

    public virtual ApplicationUser ApplicationUser { get; set;}
    public virtual Group Group { get; set; }

    // 1 : simple member
    // 2 : administrator
    public int Status { get; set; }

    public ApplicationUserGroup()
    {
        Status = 1;
    }
}

I managed to get my table into my DB as ApplicationUserGroups but Entity keep generating another table GroupApplicationUsers without my status field...

I believe the problem comes from Identity, anyone to help me ?

PS: Sorry for my english, I'm french ^^

Answer 1

You need to decorate your entity properties with appropriate attributes or use fluent api to make EF understand what you are trying to do. So using the fluent api, in your DbContext class, you should override OnModelCreating method. Then use the following code:

protected override void OnModelCreating(DbModelBuilder mb)
{
    mb.Entity<ApplicationUser>()
        .HasMany(au => au.Groups)
        .WithMany(grp => grp.Members)
        .Map(m =>
        {
            m.ToTable("ApplicationUserGroup");
            m.MapLeftKey("ApplicationUserId");
            m.MapRightKey("GroupId");
        });
}

This tells EF to use your ApplicationUserGroup entity/table as a junction table for many-to-many relationship.

EDIT. According to your provided link, The above solution will only work if you let EF handle the join table itself as a hidden table. You cannot have it in your model. So the correct solution is to use two one-to-many relationships to your join table which in this case can exist in your model.

First change your entity classes as below:

public class ApplicationUser : IdentityUser
{

    [...]

    public virtual List<ApplicationUserGroup> UserGroups { get; set; }
}

public class Group
{
    [Key] // *** add this attribute
    public int GroupId { get; set; } // *** Change D to d to match ApplicationUserGroup property (this is just to be consistent in your naming conventions!)
    [Required]
    [StringLength(100, MinimumLength = 2)]
    public string Name { get; set; }

    public virtual List<ApplicationUserGroup> UserGroups { get; set; } // *** this has changed also
}

Then to configure these relationships using fluent api, add following code in your DbContext class:

protected override void OnModelCreating(DbModelBuilder mb)
{
    mb.Entity<ApplicationUser>()
            .HasMany(au => au.UserGroups)
            .WithRequired(ug => ug.ApplicationUser)
            .HasForeignKey(ug => ug.ApplicationUserId)
            .WillCascadeOnDelete(false);

    mb.Entity<Group>()
            .HasMany(grp => grp.UserGroups)
            .WithRequired(ug => ug.Group)
            .HasForeignKey(ug => ug.GroupId)
            .WillCascadeOnDelete();
}

In this case to add a user in a group, you need to add a record in ApplicationUserGroup table that tells which user is added in which group (using ApplicationUserId column and GroupId column), and addordingly to check that a user belongs to which groups, you need to select all records in ApplicationUserGroup where their ApplicationUserId column matches the Id of that user.

Hope this helps.