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.

Try removing the whitespace of the name of the Navegation Property in the FK attribute-`[ForeignKey("ScheduledBy")]` — ocuenca, Feb 13 '15 at 04:23
Sorry, I copied over incorrectly. My code states exactly what you've specified above. — RizJa, Feb 13 '15 at 14:11

score 2 · Accepted Answer · answered Feb 19 '15 at 14:33

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.

score 1 · Answer 2 · answered Feb 13 '15 at 04:51

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; }
}

Sorry my mistake, I accidentally left a whitespace in there when I copied the code over. There is in fact no space between the two words. It appears just as you've specified above. Will edit the question right now. — RizJa, Feb 13 '15 at 14:08

score 0 · Answer 3 · answered Feb 13 '15 at 16:29

0

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; }       
}

answered Feb 13 '15 at 16:29

Iraj

1,492
5
18
42

Is setting an inverse property really necessary? My guess at why this isn't working has something to do with one of the context files and how it's laid out. – RizJa Feb 13 '15 at 16:41

Insert statement conflicts with FK constraint on ApplicationUsers

3 Answers3