CODEWITHSUNDEEP
c#entity-frameworkentity-framework-6
zameb
zameb

Reputation: 840

Conflict with foreign key when the related entity is added before SaveChanges

I expect EF 6.2.0 to fail if I try to add a related entity which doesn't exists at all. But in this case, the problematic related entity (Person) is added to the context before SaveChanges (is not in the Db yet, but I see is in ChangeManager). But for some reason, EF is telling me: "The INSERT statement conflicted with the FOREIGN KEY constraint \"FK_AuditPerson_Person\". The conflict occurred in database \"Person\", table \"person.Person\", column 'Id'"

I have reproduced my problem with few classes:

public class Person
{
    public Guid Id { get; set; }
}

And also:

public class AuditPerson
{
    public Guid AuditId { get; set; }
    public Guid PersonId { get; set; }
    public DateTime Timestamp { get; set; }
}

The context is:

public class PersonTestContext : DbContext
{
    public DbSet<Person> Packages { get; set; }
    public DbSet<AuditPerson> PersonAudits { get; set; }

    public PersonTestContext() : base("PersonTestDb")
    {
        Database.SetInitializer<PersonTestContext>(null);
    }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Person>()
            .ToTable("Person", "person")
            .HasKey(t => t.Id);

        modelBuilder.Entity<AuditPerson>()
            .ToTable("AuditPerson", "dbo")
            .HasKey(ap => new { ap.AuditId, ap.PersonId });

        base.OnModelCreating(modelBuilder);
    }
}

And the problem throws with the following sample:

    static void Main(string[] args)
    {
        using (var context = new PersonTestContext())
        {
            var person = new Person { Id = Guid.NewGuid() };
            context.Persons.Add(person);

            var audit = new AuditPerson { AuditId = Guid.NewGuid(), Timestamp = DateTime.UtcNow, PersonId = package.Id };

            context.PersonAudits.Add(audit);

            context.SaveChangesAsync().Wait();
        }
        Console.ReadKey();
    }

What are the available alternatives to tell EF that PersonId is referring to an object that will be saved on the same SaveChanges call?

For now the problem is gone when I add:

public virtual Person Person { get; set; }

to the AuditPerson class. But wouldn't be EF able to solve this without a navigation property?

The Db already exists and have the "FK_AuditPerson_Person" as:

ALTER TABLE [dbo].[AuditPerson]  WITH CHECK 
  ADD CONSTRAINT [FK_AuditPerson_Person] 
  FOREIGN KEY([PersonId])
  REFERENCES [person].[Person] ([Id])

I have the feeling that I have seen it done before without a navigation property.

Final thoughts

As explained by Steve, EF does not guarantee the order of inserts. If we use the same code, but Instead of Person, the class name is Appointment, no issues are noticed: the records are added without any FK conflicts. The same occurred with real production code (now fixed). Apparently EF makes the inserts ordering by class name (a peek into the code could clarify), so Person inserts are done after Audits, but Appointments are done before and thus the behavioral difference

Upvotes: 3

Views: 5266

Answers (1)

Steve Py
Steve Py

Reputation: 34698

The reason you are getting this issue is that without the relationships between entities, EF does not guarantee that the order of insert will match your code. With the FK constraint EF is trying to insert the AuditPerson record first and tripping the FK. By specifying the navigation properties EF can work out the order to insert the records.

If you don't typically access Audits from Person then I'd suggest putting the Person reference in Audit and map as:

HasRequired(x => x.Person)
  .WithMany()
  .HasForeignKey(x => x.PersonId);

Then when setting up your entities:

context.Persons.Add(person);
var audit = new AuditPerson { AuditId = Guid.NewGuid(), Timestamp = DateTime.UtcNow, Person = person };

context.PersonAudits.Add(audit);
context.SaveChangesAsync().Wait();

Also, if your DB is SQL Server I'd recommend utilizing the database to manage the PKs using newsequentialId() and set EF to recognize them as Identity columns. Alternatively you can generate GUIDs using the same hi/lo byte order to make UUIDs that match SQL's sequential ID. These IDs are more index-friendly.

Upvotes: 3

Related Questions