Adding entity to Identity model

Question

I am currently getting an error I understand the error but I don't know where I am going wrong

The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK_dbo.AspNetUsers_dbo.CompanyDetails_userCompanyID". The conflict occurred in database "PXWHITESPIDERDEV", table "dbo.CompanyDetails", column 'companyID'.

Within the IdentityModel autogenerated when you create an MVC application

public class ApplicationUser : IdentityUser
{

    public async Task<ClaimsIdentity> GenerateUserIdentityAsync(UserManager<ApplicationUser> manager)
    {
        // Note the authenticationType must match the one defined in CookieAuthenticationOptions.AuthenticationType
        var userIdentity = await manager.CreateIdentityAsync(this, DefaultAuthenticationTypes.ApplicationCookie);
        // Add custom user claims here
        return userIdentity;
    }


    public int userCompanyID { get; set; }

    [ForeignKey("userCompanyID")]
    public CompanyDetails company { get; set; }
}

and here is the entity I am trying to create

 public class CompanyDetails
 {
    [Key, DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int companyID { get; set; }

    [StringLength(100, ErrorMessage = "The {0} must be at least {2} characters long.", MinimumLength = 1)]
    [Display(Name = "Company Name")]
    public string CompanyName { get; set; }
 }

and within the RegisterViewModel class

public class RegisterViewModel
{
    [Required]
    [EmailAddress]
    [Display(Name = "Email")]
    public string Email { get; set; }

    [Required]
    [StringLength(100, ErrorMessage = "The {0} must be at least {2} characters long.", MinimumLength = 6)]
    [DataType(DataType.Password)]
    [Display(Name = "Password")]
    public string Password { get; set; }

    [DataType(DataType.Password)]
    [Display(Name = "Confirm password")]
    [Compare("Password", ErrorMessage = "The password and confirmation password do not match.")]
    public string ConfirmPassword { get; set; }

    public CompanyDetails company { get; set; }
}

before the companyID's were the same within the ApplicationUser class and the CompanyDetails class as in they had the same variable name. I thought this was the problem so changed the variable name within the ApplicationUser class, until I tried to update the database again and found out this was not the case.

Answer 1

When you added the new companyID property, Entity Framework obviously needed to add a new column to the dbo.AspNetUsers table to represent it. Since this column did not exist before and is non-nullable, some default value needed to be set in the column for existing records. For an int type, the default value is 0. However, 0 is unacceptable as a foreign key, so when Entity Framework attempts to attach that constraint, it fails.

The easiest way to solve the problem is either 1) remove all existing rows from dbo.AspNetUsers or 2) add the column and manually update the values to actual CompanyDetails ids before attaching the foreign key constraint.

Alternatively, you can also make companyId a nullable int, which would allow Entity Framework to null it out in the database when adding the column. Foreign key constraints can be added to columns with nulls, so everything should work then. However, that means the relationship will now be optional. If every user should always have a related CompanyDetails, then find another way to fix the issue.