Group Identity Users in many-to-many relationship and limit to a resource based on role

Question

I'm trying to setup my database using code-first to acheieve the following. A user signs up to the site and becomes an admin using built in roles, and has access to a single Instance class (using ApplicationUserInstance). The admin can then add another Admin/Manager/User who become part of the same user group, and also have access to the same Instance.

There will be higher role called SuperAdmin, who will beable to acess multiple Instances rather than just a single like above.

So far i'm adding an Admin, and creating an instance fine and then assigning the admin to the instance. When i then try and create a group and assign the user to the group i got a foreign key error like shown below.

I'm not sure this is even the best way to achieve what i require, any help would be appreicated. Thanks

DbContext:

public class ApplicationUser : IdentityUser
{
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public string Company { get; set; }
    public string Telephone { get; set; }
    public string Postcode { get; set; }
    public virtual ICollection<ApplicationUserInstance> ApplicationUserInstances { get; set; }
    public virtual ICollection<UserGroup> UserGroups { get; set; }
}

public class UserGroup
{
    public string ApplicationUserId { get; set; }
    public virtual ApplicationUser ApplicationUser { get; set; }
    public string GroupId { get; set; }
    public virtual Group Group { get; set; }
}

public class Group
{
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    [Key]
    public string GroupId { get; set; }
    [MaxLength(50)]
    public string Company { get; set; }
    public virtual ICollection<UserGroup> UserGroups { get; set; }
}

public class ApplicationUserInstance
{
    public string ApplicationUserId { get; set; }
    public virtual ApplicationUser ApplicationUser { get; set; }
    public string InstanceId { get; set; }
    public virtual Instance Instance { get; set; }
}

public class Instance
{
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    [Key]
    public string InstanceId { get; set; }
    [MaxLength(100)]
    public string Company { get; set; }
    public virtual ICollection<ApplicationUserInstance> ApplicationUserInstances { get; set; }
    public virtual ICollection<GSMSite> GSMSites { get; set; }
}

builder.Entity<ApplicationUserInstance>().HasKey(aui => new { aui.ApplicationUserId, aui.InstanceId });
builder.Entity<UserGroup>().HasKey(ug => new { ug.ApplicationUserId, ug.GroupId });

User Service:

var newInstance = new Instance
    {
       Company = request.Company
    };
var createInstance =  await _dbContext.Instances.AddAsync(newInstance);

var createInstanceUser = await _dbContext.ApplicationUserInstances.AddAsync(new ApplicationUserInstance { ApplicationUserId = newUser.Id, InstanceId = newInstance.InstanceId });

 var newGroup = new Group
     {
       Company = request.Company
     };
 var createGroup = await _dbContext.Groups.AddAsync(newGroup);

 var createUserGroup = await _dbContext.UserGroups.AddAsync(new UserGroup { ApplicationUserId = newUser.Id, GroupId = newGroup.GroupId });

  await _dbContext.SaveChangesAsync();

Error:

Microsoft.EntityFrameworkCore.DbUpdateException: An error occurred while saving the entity changes. See the inner exception for details.

---> Microsoft.Data.SqlClient.SqlException (0x80131904): The INSERT statement conflicted with the FOREIGN KEY constraint "FK_UserGroups_AspNetUsers_GroupId". The conflict occurred in database "GSMProfile", table "dbo.AspNetUsers", column 'Id'.

The statement has been terminated.

Answer 1

Adding the following Fluent API seemed to do the job, it now works:

 builder.Entity<UserGroup>()
        .HasOne(c => c.ApplicationUser)
        .WithMany(e => e.UserGroups)
        .HasForeignKey(c => c.ApplicationUserId);

 builder.Entity<UserGroup>()
        .HasOne(c => c.Group)
        .WithMany(e => e.UserGroups)
        .HasForeignKey(c => c.GroupId);