Adding ApplicationUser to database results in Foreign Key conflict

Question

I have a webapplication with two ApplicationUsers, Cursist and Lector:

public class ApplicationUser : IdentityUser
{
    public string Sex { get; set; }
    public string FName { get; set; }
    public string LName { get; set; }
    //email inherited by IdentityUser
    ....
}

public class Cursist: ApplicationUser
{
    public Group Group { get; set; }
    public int GroupId { get; set; }
    ....
}

 public class Lector: ApplicationUser
{
    private IEnumerable<Group> _supervising;
    ....
}

It's the first time I'm using inheritance with ApplicationUser, which resulted in a constant error:

System.Data.SqlClient.SqlException: The INSERT statement conflicted with the FOREIGN KEY constraint "FK_AspNetUserClaims_AspNetUsers_UserId". The conflict occurred in database "cvodb", table "dbo.AspNetUsers", column 'Id'. The statement has been terminated.

The origin of the error seems to come from this code snippet:

 public async Task InitializeData()
    {
        _dbContext.Database.EnsureDeleted();
        if (_dbContext.Database.EnsureCreated())
        {
            await InitializeUsers();
            _dbContext.SaveChanges();
        }
    }

    private async Task InitializeUsers()
    {
        ApplicationUser user = new Cursist("Male", "fname", "lname", "b@b.b");
        await _userManager.CreateAsync(user, "password");
        await _userManager.AddClaimAsync(user, new Claim(ClaimTypes.Role, "Cursist"));
    }

EDIT after changes I managed to resolve the UserId conflict by adding a UserName to the ApplicationUserId. The the classes now look like this:

public class ApplicationUser : IdentityUser
{
    public string Sex { get; set; }
    public string FName { get; set; }
    public string LName { get; set; }
    //email inherited by IdentityUser

    public ApplicationUser(string Sex, string fname, string lname, string email)
    {
        base.Email = email;
        //username fixes foreign key conflict?
        base.UserName = email;
        Sex = sex;
        FName = fname;
        LName = lname;
    }
}
 public class Cursist: ApplicationUser
{
    public Group Group { get; set; }
    // can be null
    public int? GroupId { get; set; }
}
//Lector class hasn't changed.

I get this output in the console (disregard the spelling differences):

ALTER TABLE [AspNetUsers] ADD CONSTRAINT [FK_AspNetUsers_Groep_GroepId] FOREIGN KEY ([GroepId]) REFERENCES [Groep] ([GroepId]) ON DELETE NO ACTION; Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommandBuilderFactory:Information: Executed DbCommand (0ms) [Parameters=[], CommandType='Text', CommandTimeout='30']

ALTER TABLE [AspNetUsers] ADD CONSTRAINT [FK_AspNetUsers_Groep_GroepId1] FOREIGN KEY ([GroepId1]) REFERENCES [Groep] ([GroepId]) ON DELETE NO ACTION; Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommandBuilderFactory:Information: Executed DbCommand (0ms) [Parameters=[], CommandType='Text', CommandTimeout='30']

ALTER TABLE [AspNetUsers] ADD CONSTRAINT [FK_AspNetUsers_Groep_GroepId2] FOREIGN KEY ([GroepId2]) REFERENCES [Groep] ([GroepId]) ON DELETE NO ACTION;

I don't know why AspNetUsers is creating 3 instances of groupIds? The first instance and second instance generate no errors, but the last one does:

{Microsoft.EntityFrameworkCore.DbUpdateException: An error occurred while updating the entries. See the inner exception for details. ---> System.Data.SqlClient.SqlException: The INSERT statement conflicted with the FOREIGN KEY constraint "FK_AspNetUsers_Groep_GroepId2". The conflict occurred in database "cvodb", table "dbo.Groep", column 'GroepId'. The statement has been terminated.

I have a feeling it's trying to link the the Group object, however a group can be a null value. I'm still unsure why there is a groupId, groupId1 and groupId2.

I have a class called Group and two subclasses ClosedGroup and OpenGroup. They should be using the same key GroupId, there are no instances of Group. Only ClosedGroups and OpenGroups.

Answer 1

I bet this question is the most hit for this issue. The conflict is brought about by one or more model errors when trying to add a claim. Most times the user is created successfully but the process of adding a claim breaks due to a model error. First, instead of a fire & forget on CreateAsync, assign it to a variable as follows:

    var user  = await _userManager
                        .CreateAsync(superadmin, "Password");

Hover over the user variable to determine the model errors encountered as shown in the image below:

In my case, I hadn't assigned the required Email field. You can then fix the model errors and the conflict will be gone. Cheers!