How to add entities to DbContext with recursive relationships while avoiding duplicate additions?

Question

I'm using Entity Framework 4.4 and I have a One-To-Many relationship model like this:

class Item { 
   public string keyPart1 { get; set; }
   public string keyPart2 { get; set; }

   public virtual Container container { get; set; }
   public string ContainerId { get; set; }
}

// Idea is that many Items will be assigned to a container
class Container {
   public string ContainerId { get; set; }

   private ICollection<Item> _Items;
   public virtual ICollection<Item> As
   {
      get { return _Items ?? (_Items = new HashSet<A>()); }
      protected set { _Items = value; }
   }
}

Now, here's the DbContext:

public class StorageContext : DbContext
{
    public DbSet<Item> Items { get; set; }
    public DbSet<Bucket> Buckets { get; set; }

    public override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        base.OnModelCreating(modelBuilder);
        modelBuilder.Entity<Item>().HasKey( i => new { i.keyPart1, i.keyPart2 } ).HasRequired( i => i.Container );
    }
}

Now, supposing I have N Item instances. Each Item belongs to a container, which contains multiple item instances, each of which belongs to a container, and so the model recurses endlessly.

I want to then cycle through my current list of Item instances and add each to the db context:

foreach (var i in LocalItemList)
{ 
   IDbSetExtensions.AddOrUpdate<Item>(db.Items, i);
}
dbContext.SaveChanges();

The problem that I can't figure out is how to tell the context to AddOrUpdate the Container so that I don't get primary key duplicate exceptions. At some point we'll run into an Item that has the same Container as another, but I'll get a duplicate primary key exception on SaveChanges().

If I Add a Container to the DbSet, are the Items added to the Set as well? How can I make that an AddOrUpdate instead?

Answer 1

I am not sure if you want to insert the related Containers into the database along with the Items, or if you only want to create a relationship to already existing Containers. The possible case that the Container entities do not exist in the database and in your object graph you only have one Container instance per key (multiple references to those instances are allowed) should not be a problem at all and a simple code like...

    foreach (var i in LocalItemList)
    {
        dbContext.Items.Add(i);
    }
    dbContext.SaveChanges();

...should actually work without exception. So, you probably have one of the following two situations which would explain the primary key constraint violation:

• The Container entities already exist in the database and in your object graph you only have one Container instance per key (multiple references to those instances are allowed again). This is the easy case and you can solve it by using:

  foreach (var i in LocalItemList)
  {
      dbContext.Containers.Attach(i.Container);
      dbContext.Items.Add(i);
  }
  dbContext.SaveChanges();
  

  If you have multiple Container instances for the same key this won't work and throw an "...an object with the same key already exists in the ObjectContext..." (or similar) exception. It also won't work if the Containers do not exist yet in the database (you'll probably get a foreign key constraint violation then).

• If you have multiple Container object instances with the same key in your object graph you must replace the duplicates by one unique instance per key before you attach or add the entities to the context. To make this process simpler I would remove the circular references first and then use the Local collection to figure out if a Container with the same key is already attached:

  foreach (var i in LocalItemList)
  {
      i.Container.Items = null;
  
      var attachedContainer = dbContext.Containers.Local
          .SingleOrDefault(c => c.ContainerId == i.Container.ContainerId);
  
      if (attachedContainer != null)
          i.Container = attachedContainer;
      else
          dbContext.Containers.Attach(i.Container);
          // or dbContext.Containers.Add(i.Container);
          // depending on if you want to insert the Container or not
  
      dbContext.Items.Add(i);
  }
  dbContext.SaveChanges();
  

Answer 2

It seems to work ok, as long as you make EntityFramework aware of any pre-exisitng Containers.

For instance, this test is run on an empty database. Both items are in the same Container but EF only inserts the Container once.

    [TestMethod]
    public void Populate()
    {
        const string conStr = "Data Source=(local);Initial Catalog=EfExperiment1; Integrated Security=True";
        var context = new MyDbContext(conStr);

        var container = new Container { ContainerId = "12345" };
        var item1 = new Item { keyPart1 = "i1k1", keyPart2 = "i1k2", Container = container };
        var item2 = new Item { keyPart1 = "i2k1", keyPart2 = "i2k2", Container = container };
        context.Items.Add(item1);
        context.Items.Add(item2);
        context.SaveChanges();
    }

This test is run when the container already exists. By attempting to read the container from the db, we make EF aware of any existing instance.

    [TestMethod]
    public void PopulateSomeMore()
    {
        const string conStr = "Data Source=(local);Initial Catalog=EfExperiment1; Integrated Security=True";
        var context = new MyDbContext(conStr);

        var container = context.Buckets.FirstOrDefault(c => c.ContainerId == "12345") ??
                        new Container { ContainerId = "12345" };

        var item3 = new Item { keyPart1 = "i3k1", keyPart2 = "i3k2", Container = container };
        var item4 = new Item { keyPart1 = "i4k1", keyPart2 = "i4k2", Container = container };
        context.Items.Add(item3);
        context.Items.Add(item4);
        context.SaveChanges();
    }

This test doesn't load up the existing container, so EF thinks it needs to insert and fails with a duplicate key error.

    [TestMethod]
    public void PopulateSomeMoreAgain()
    {
        const string conStr = "Data Source=(local);Initial Catalog=EfExperiment1; Integrated Security=True";
        var context = new MyDbContext(conStr);

        var container = new Container { ContainerId = "12345" };
        var item5 = new Item { keyPart1 = "i5k1", keyPart2 = "i5k2", Container = container };
        var item6 = new Item { keyPart1 = "i6k1", keyPart2 = "i6k2", Container = container };
        context.Items.Add(item5);
        context.Items.Add(item6);
        context.SaveChanges();
    }