Insert statement conflicts with FK constraint on ApplicationUsers

Question

I've got an Entity as follows:

public class EntityX {
    public int Id { get; set; }
    ...
    [ForeignKey("Scheduled By")]
    public string ScheduledById { get; set; }
    public virtual ApplicationUser ScheduledBy { get; set; }
}

When I try to insert a value into the table, I get the following error:

"The INSERT statement conflicted with the FOREIGN KEY constraint "FK_dbo.EntityX_dbo.ApplicationUsers_ScheduledById". The conflict occurred in database "DB", table "dbo.ApplicationUsers", column 'Id'. The statement has been terminated."

The first thing that comes to mind is that the ApplicationUser table is empty because the IdentityUser table (AspNetUsers) holds all the values. However, its TPH and has a Discriminator column populated with the ApplicationUser table name.

I've verified that the correct Id is being populated when sent in the DB (i.e. it corresponds to an actual User ID) but can't figure out why this is happening.

Thank you in advance. Cheers!

UPDATE:

The space in "Scheduled By" was a typo. It was copied over incorrectly. The actual code has it written as pointed out "ScheduledBy".

UPDATE 2:

The problem it seems lies in the contexts somewhere. I've got two, one DataContext that extends from DbContext as follows:

public class DataContext : DbContext
{
   public DbSet<EntityX> EntityXs { get; set; }
   ...
}

static DataContext()
{
     Database.SetInitializer<DataContext> (new CreateInitializer ());
}

public DataContext()
    : base("DataContext")
{

}

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
    base.OnModelCreating(modelBuilder);

    modelBuilder.Entity<IdentityUserLogin>().HasKey<string>(l => l.UserId);
    modelBuilder.Entity<IdentityRole>().HasKey<string>(r => r.Id);
    modelBuilder.Entity<IdentityUserRole>().HasKey(r => new { r.RoleId, r.UserId });
    ...

}

And another, extending from IdentityDbContext as follows:

public class SecurityContext : IdentityDbContext<ApplicationUser>
{
    static SecurityContext()
    {
        Database.SetInitializer<SecurityContext> (new CreateInitializer ());
    }

    public SecurityContext()
        : base("SecurityContext")
    {
        Database.Initialize(force: true);
    }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        base.OnModelCreating(modelBuilder);
        modelBuilder.Entity<IdentityUser>()
            .ToTable("AspNetUsers");
        modelBuilder.Entity<ApplicationUser>()
            .ToTable("AspNetUsers");

        modelBuilder.Entity<IdentityUserLogin>().HasKey<string>(l => l.UserId);
        modelBuilder.Entity<IdentityRole>().HasKey<string>(r => r.Id);
        modelBuilder.Entity<IdentityUserRole>().HasKey(r => new { r.RoleId, r.UserId });

        base.OnModelCreating(modelBuilder);
    }

With this, the configuration works... but I'm presented with an issue where I've got an extra IdentityRole_Id appearing in the AspNetUserRoles table as described at this post: EF Code First Migration unwanted column IdentityRole_Id. To work around that issue, I followed Hao Kung's advice here: Create ASP.NET Identity tables using SQL script and changed my contexts' OnModelCreating methods this way:

DataContext:

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
    //base.OnModelCreating(modelBuilder);

    modelBuilder.Entity<IdentityUserLogin>().HasKey<string>(l => l.UserId);
    modelBuilder.Entity<IdentityRole>().HasKey<string>(r => r.Id);
    modelBuilder.Entity<IdentityUserRole>().HasKey(r => new { r.RoleId, r.UserId });
}

And SecurityContext...

protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        base.OnModelCreating(modelBuilder);

        var user = modelBuilder.Entity<IdentityUser>()
        .ToTable("AspNetUsers");
        user.HasMany(u => u.Roles).WithRequired().HasForeignKey(ur => ur.UserId);
        user.HasMany(u => u.Claims).WithRequired().HasForeignKey(uc => uc.UserId);
        user.HasMany(u => u.Logins).WithRequired().HasForeignKey(ul => ul.UserId);
        user.Property(u => u.UserName).IsRequired();

        modelBuilder.Entity<ApplicationUser>().ToTable("AspNetUsers"); //Needed?

        modelBuilder.Entity<IdentityUserRole>()
            .HasKey(r => new { r.UserId, r.RoleId })
            .ToTable("AspNetUserRoles");

        modelBuilder.Entity<IdentityUserLogin>()
            .HasKey(l => new { l.UserId, l.LoginProvider, l.ProviderKey })
            .ToTable("AspNetUserLogins");

        modelBuilder.Entity<IdentityUserClaim>()
            .ToTable("AspNetUserClaims");

        var role = modelBuilder.Entity<IdentityRole>()
            .ToTable("AspNetRoles");
        role.Property(r => r.Name).IsRequired();
        role.HasMany(r => r.Users).WithRequired().HasForeignKey(ur => ur.RoleId);
}

Although doing this builds the DB correctly, with a Discriminator column in the AspNetUsers table populating with "ApplicationUser" as the value and without the extra columns in AspNetUserRoles, any attempt at inserting the user's Id value into EntityX as FK fails.

I'm completely lost.

Answer 1

So, it turns out that there wasn't any problem with how the entity was set up. It was due to some issue that was arising when migrating to the same DB via the two contexts. Merging them into one fixed the issue. I've posted how I resolved things below. Hopefully this saves someone else time and torment.

public class SecurityContextContext : IdentityDbContext<ApplicationUser>
{
    public DbSet<EntityX> EntityX { get; set; }
    ...

    static SecurityContext()
    {
        Database.SetInitializer<SecurityContext> (new CreateInitializer());
    }

    public SecurityContext()
        : base("SecurityContext")
    {
        Database.Initialize(force: true);
    }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        base.OnModelCreating(modelBuilder);

        var user = modelBuilder.Entity<IdentityUser>()
        .ToTable("AspNetUsers");
        user.HasMany(u => u.Roles).WithRequired().HasForeignKey(ur => ur.UserId);
        user.HasMany(u => u.Claims).WithRequired().HasForeignKey(uc => uc.UserId);
        user.HasMany(u => u.Logins).WithRequired().HasForeignKey(ul => ul.UserId);
        user.Property(u => u.UserName).IsRequired();
        user.HasKey(u => u.Id);

        var appUser = modelBuilder.Entity<ApplicationUser>().ToTable("AspNetUsers"); //Needed?
        appUser.HasMany(u => u.Roles).WithRequired().HasForeignKey(ur => ur.UserId);
        appUser.HasMany(u => u.Claims).WithRequired().HasForeignKey(uc => uc.UserId);
        appUser.HasMany(u => u.Logins).WithRequired().HasForeignKey(ul => ul.UserId);
        appUser.Property(u => u.UserName).IsRequired();

        modelBuilder.Entity<IdentityUserRole>()
            .HasKey(r => new { r.UserId, r.RoleId })
            .ToTable("AspNetUserRoles");

        modelBuilder.Entity<IdentityUserLogin>()
            .HasKey(l => new { l.UserId, l.LoginProvider, l.ProviderKey })
            .ToTable("AspNetUserLogins");

        modelBuilder.Entity<IdentityUserClaim>()
            .ToTable("AspNetUserClaims");

        var role = modelBuilder.Entity<IdentityRole>()
            .ToTable("AspNetRoles");
        role.Property(r => r.Name).IsRequired();
        role.HasMany(r => r.Users).WithRequired().HasForeignKey(ur => ur.RoleId);

    }

This configuration works for me. Although the ApplicationUser table does not generate via this mapping, there is still a Discriminator column created in the AspNetUsers table filled with "ApplicationUser". The AspNetUsers table also gets the extra fields that I defined in the ApplicationUser class. The IdentityRole_Id is eliminated and I'm able to assign roles and get them successfully. The FK issue is also resolved. Everything works as intended.

Answer 2

use this:

public class EntityX
{
  public int Id { get; set; }

  [ForeignKey("ScheduledBy")]
  public string ScheduledById { get; set; }


    [ForeignKey("ScheduledById")]
    [InverseProperty("EntityX_1")]
    public virtual ApplicationUser ScheduledBy{ get; set; }

}

public class ApplicationUser
{
   public string ScheduledById { get; set; }

   .
   .
   .
    [InverseProperty("ScheduledBy")]
    public virtual ICollection<EntityX> EntityX_1{ get; set; }       
}

Answer 3

As the error message indicates, your foreign key needs to be associated with a valid entity property. If you place the ForeignKey attribute on a foreign key property the string parameter represents the name of the associated navigation property. Remove the whitespace to match the name of the navegation property:

public class EntityX
{
    public int Id { get; set; }
     ...
    [ForeignKey("ScheduledBy")]
    public string ScheduledById { get; set; }

    public virtual ApplicationUser ScheduledBy { get; set; }
}