Self referencing entity & insert order - using Entity Framework Code First

Question

I have two entities looking like this:

public class AssetSession
{
   [Key]
   public Guid Id { get; set; }
   public string RoomNumber { get; set; }
   public Contact Contact { get; set; }
   public virtual List<Asset> Assets { get; set; }
}

public class Asset
{
   [Key]
   public Guid Id { get; set; }
   public Guid? ParentId { get; set; }
   [ForeignKey("ParentId")]
   public Asset Parent { get; set; }
   public string Barcode { get; set; }
   public string SerialNumber { get; set; }
   public Guid AssetSessionId { get; set; }
   [ForeignKey("AssetSessionId")]
   public AssetSession AssetSession { get; set; }
}

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
   modelBuilder.Entity<Asset>()
      .HasOptional(t => t.Parent)
      .WithMany()
      .HasForeignKey(t => t.ParentId);
}

AssetSession having a to-many relationship with Asset. Everything worked fine until recently when I introduced the self referencing entity on Asset (called Parent).

My problem is that after some SQL profiling when inserting new AssetSession records it seems that EF now tries to first insert the Asset referencing a non-existing FK on AssetSession hence the reason why I get the following error:

The INSERT statement conflicted with the FOREIGN KEY constraint "FK_dbo.Assets_dbo.AssetSessions_AssetSessionId"

The error is pretty self-explanatory but what I don't get is why the order of the INSERT statement does not start by creating the AssetSession first in order for Assets to reference the correct AssetSession.

My code for inserting looks like this:

using (var context = new AssetContext())
{
   var assetSession = jsonObject; // jsonObject being passed into the method

   var existingSession = context.AssetSessions.FirstOrDefault(c => c.Id == assetSession.Id);

   if (existingSession == null)
   {
      var existingContact = context.Contacts.FirstOrDefault(c => c.Id == assetSession.Contact.Id);

      if (existingContact != null)
      {
         context.Contacts.Attach(existingContact);
         assetSession.Contact = existingContact;
      }

      context.Entry(assetSession).State = EntityState.Added;
      context.SaveChanges();    
   }
}

First, I think there is a typo: if (existingSession != null)? Second, is a contact found and attached or does it happen with and without contact? Third, which insert statements are sent to the database? Maybe an existing Asset (without AssetSession) is added inadvertently on top of the new Asset. — Gert Arnold

Gram Gram · Accepted Answer · 2014-02-19T23:10:05

I have had the same error with foreign keys using EF. It may not be related however the answer that was provided helped me understand why I was getting the error.

Issue with EF and Foreign keys

In summary of Slauma's answer I was clearing the vertical navigation values before my EF insert. This caused an issue, all-though it is a different issue it may help.

Potential Solution

If you where to all your AssetSession object to your Asset object and then do one root add that should allow EF to insert both correctly.

Note: To do this you may need to generate your GUID in advance of inserting your objects.

Self referencing entity & insert order - using Entity Framework Code First

1 Answers