Detached entities causing duplicate ID issues

Question

I have very limited experience using EF4. I am successfully deserializing entities from a webservice in a detached state, and I now would like to save to the database. When SaveChanges is used I get the following exception:

System.Data.UpdateException: An error occurred while updating the entries. See the inner exception for details. ---> System.Data.SqlClient.SqlException: Violation of PRIMARY KEY constraint '[Primary key constraint name]'. Cannot insert duplicate key in object '[Related Table Name]'. The duplicate key value is (1). The statement has been terminated.

the entity I am trying to save has related entities as properties and properties that are a collection of entities.

The IDs from the web service are used as the primary key for the tables, so no automatically generated ID's are used.

The following test illustrates the issue that i'm trying to resolve:

    [TestMethod]
    public void SaveRelatedDetachedEntitiesWithoutDuplicatesTest(){
        using (var db = ProductEntities()){ 
            //testing pre-saved existing category
            if (!db.CategoryGroups.Any(e => e.Id == 3)){
                db.CategoryGroups.AddObject(new Database.CategoryGroupEntity(){
                                                                                Id = 3,
                                                                                Name = "test group 3"
                                                                            });
                db.SaveChanges();
            }

            var categoryList = new List<CategoryEntity>(){
               new CategoryEntity(){
                    Id = 1,
                    Name = "test category 1",
                    Groups =  new List<CategoryGroupEntity> (){new CategoryGroupEntity(){
                                                                                    Id = 1,
                                                                                    Name = "test group 1"
                                                                                },//duplicate
                                                                                new CategoryGroupEntity(){
                                                                                    Id = 2,
                                                                                    Name = "test group 2"
                                                                                }
                                                                            }
                },      
                new CategoryEntity(){
                    Id = 2,
                    Name = "test category 2",
                    Groups =  new  List<CategoryGroupEntity>{
                                                                            new CategoryGroupEntity(){
                                                                                Id = 1,
                                                                                Name = "test group 1"
                                                                            },//duplicate
                                                                            new CategoryGroupEntity(){
                                                                                Id = 3,
                                                                                Name = "test group 3"
                                                                            }//already in db
                                                                        }
                }
            };

            var product = new ProductEntity(){          
                Categories = categoryList,          
                Id = 1,
                Name = "test product 1",            
                Type = new TypeEntity { Id = 1, Name = "test type" }
            };
//all above code cannot be altered as it reflects what results from the deserialization.
            db.Products.AddObject(product); 

//need code here to handle the duplicates
            db.SaveChanges();

            var test = db.Products.Where(e => e.Id == 1).FirstOrDefault();
            Assert.IsNotNull(test);
            Assert.IsTrue(test.Categories.Count() == 2, "missing categories from product");
            Assert.IsTrue(test.Categories.ElementAt(0).Groups.Any(e => e.Id == 1), "missing group from category 1");
            Assert.IsTrue(test.Categories.ElementAt(1).Groups.Any(e => e.Id == 1), "missing group from category 2");
        }
    }

Your help is appreciated.

Edit: I can get a list of groups that are duplicates using the following code

                var added = db.ObjectStateManager.GetObjectStateEntries(System.Data.EntityState.Added)
                    .Where(e => !e.IsRelationship).Select(e => e.Entity)
                    .OfType<CategoryGroupEntity>();
                var duplicates = added.GroupBy(e => e.Id)
                    .Where(g => g.Count() > 1)
                    .SelectMany(g => g.Where(e => e != g.First())

Things I have tried but did not work:

-attaching entities that are duplicates to the Data Context with an unchanged state. as attaching a CategoryGroupEntity causes all related entities to be attached the duplicate key issue remains

-removing entity instances from the Categories collection and replacing them with the CategoryGroupEntity instance that was first created results in the same issue

-detaching the duplicate entity instances results in the second category losing group id 1

As a side note, I also need to avoid the duplicate key issue when a particular CategoryGroupEntity already existing in the database and an attempt is made to save an entity with the same id.

So, I need to avoid the duplicate key issue both when an entity with that ID exists both in the database or in an added state in the ObjectStateManager. the test I included above incorporates both cases.

Answer 1

In Entity Framework if that entity you wanted to add is already exist then just giving its primary key wont work , you need to load that entity and assign it

var product = new ProductEntity(){          
                Categories = categoryList,          
                Id = 1,
                Name = "test product 1",            
                Type = new TypeEntity { Id = 1, Name = "test type" }
         };

So in your code for example is TypeEntity with Id=1 is already present then you need to change above code to something like

var product = new ProductEntity(){          
                Categories = categoryList,          
                Id = 1,
                Name = "test product 1",            
                Type = db.TypeEntity.Find(1);
         };

EF works on object so in order say that you are not modifying that object but is just using it as a relation ship you need to find that object and assign it where you want to use.

Update Something similar e.g.

 public class Princess 
 { 
     public int Id { get; set; } 
     public string Name { get; set; } 
     public virtual ICollection<Unicorn> Unicorns { get; set; } 
 }

var princess = context.Princesses.Find(#id);

 // Load the unicorns related to a given princess using a string to 
// specify the relationship 
context.Entry(princess).Collection("Unicorns").Load();

Source: http://blogs.msdn.com/b/adonet/archive/2011/01/31/using-dbcontext-in-ef-feature-ctp5-part-6-loading-related-entities.aspx

Answer 2

I got it working but it's definitely not the best way to do it.

save method I use has been included below:

  public static void SaveProduct(ProductEntity product) {
        using (var db = ProductEntities()) {


            //stored references to duplicate entities added to the objectContext
            var duplicateGroupsAdded = new List<Tuple<CategoryEntity, GroupEntity>>();
            var duplicateCategoriesAdded = new List<Tuple<ProductEntity, CategoryEntity>>();

            //using existing instace of entities includes associated parent into db update causing duplicate product insert attempt.
            //entities saved must be newly instantiated with no existing relationships.
            var categories = product.Categories.ToList();
            var type = new TypeEntity() {
                Id = product.Type.Id,
                Name = product.Type.Name
            };

            //empty the  collection 
            product.Categories.ToList().ForEach(category => {
                product.Categories.Remove(category);
            });
            //start off with clean product that we can populate with related entities
            product.Type = null;
            product.Group = null;

            //add to db

            db.Products.AddObject(product);

            categories.ForEach(oldCategory => {
                //new cloned category free of relationships
                var category = new CategoryEntity() {
                    Id = oldCategory.Id,
                    Name = oldCategory.Name

                };
                //copy accross Groups as clean entities free of relationships
                foreach (var group in oldCategory.Groups) {
                    category.Groups.Add(new GroupEntity() {
                        Id = group.Id,
                        Name = group.Name
                    });
                }
                //if the cat is alreay in the db use reference to tracked entity pulled from db
                var preexistingCategory = db.Categories.SingleOrDefault(e => e.Id == category.Id);
                if (preexistingCategory != null)
                    product.Categories.Add(preexistingCategory);
                else {
                    //category not in database, create new
                    var Groups = category.Groups.ToList();
                    category.Groups.ToList().ForEach(group => category.Groups.Remove(group));
                    Groups.ForEach(Group => {
                        //if the group is alreay in the db use reference to tracked entity pulled from db
                        var preexistingGroup = db.Groups.SingleOrDefault(e => e.Id == Group.Id);
                        if (preexistingGroup != null)
                            category.Groups.Add(preexistingGroup);
                        else
                            category.Groups.Add(Group);
                    });
                    product.Categories.Add(category);
                }
            });
            //if the type is alreay in the db use reference to tracked entity pulled from db
            var preexistingType = db.Types.SingleOrDefault(e => e.Id == type.Id);
            if (preexistingType != null)
                product.Type = preexistingType;
            else
                product.Type = type;

            //get lists of entities that are to be added to the database, and have been included in the update more than once (causes duplicate key error when attempting to insert).
            var EntitiesToBeInserted = db.ObjectStateManager.GetObjectStateEntries(System.Data.EntityState.Added)
                                 .Where(e => !e.IsRelationship).Select(e => e.Entity).ToList();
            var duplicateGroupInsertions = EntitiesToBeInserted
                                   .OfType<GroupEntity>()
                                   .GroupBy(e => e.Id)
                                   .Where(g => g.Count() > 1)
                                   .SelectMany(g => g.Where(e => e != g.First()));

            var duplicateCategoryInsertions = EntitiesToBeInserted
                               .OfType<CategoryEntity>()
                               .GroupBy(e => e.Id)
                               .Where(g => g.Count() > 1)
                               .SelectMany(g => g.Where(e => e != g.First()));

            foreach (var category in product.Categories) {
                //remove duplicate insertions and store references to add back in later
                var joinedGroups = duplicateGroupInsertions.Join(category.Groups, duplicateGroupInsertion => duplicateGroupInsertion, linkedGroup => linkedGroup, (duplicateGroupInsertion, linkedGroup) => duplicateGroupInsertion);
                foreach (var duplicateGroupInsertion in joinedGroups) {
                    if (category.Groups.Contains(duplicateGroupInsertion)) {
                        category.Groups.Remove(duplicateGroupInsertion);
                        db.Groups.Detach(duplicateGroupInsertion);
                        duplicateGroupsAdded.Add(new Tuple<CategoryEntity, GroupEntity>(category, duplicateGroupInsertion));
                    }
                }
            }
            //remove duplicate insertions and store references to add back in later
            var joinedCategories = duplicateCategoryInsertions.Join(product.Categories, duplicateCategoryInsertion => duplicateCategoryInsertion, linkedCategory => linkedCategory, (duplicateCategoryInsertion, linkedCategory) => duplicateCategoryInsertion);
            foreach (var duplicateCategoryInsertion in joinedCategories) {
                if (product.Categories.Contains(duplicateCategoryInsertion)) {
                    product.Categories.Remove(duplicateCategoryInsertion);
                    db.Categories.Detach(duplicateCategoryInsertion);
                    duplicateCategoriesAdded.Add(new Tuple<ProductEntity, CategoryEntity>(product, duplicateCategoryInsertion));
                }
            }
            db.SaveChanges();

            //entities not linked to product can now be added using references to the entities stored earlier
            foreach (var duplicateGroup in duplicateGroupsAdded) {
                var existingCategory = db.Categories.SingleOrDefault(e => e.Id == duplicateGroup.Item1.Id);
                var existingGroup = db.Groups.SingleOrDefault(e => e.Id == duplicateGroup.Item2.Id);
                existingCategory.Groups.Add(existingGroup);
            }
            foreach (var duplicateCategory in duplicateCategoriesAdded) {
                product = db.Products.SingleOrDefault(e => e.Id == duplicateCategory.Item1.Id);
                var existingCategory = db.Categories.SingleOrDefault(e => e.Id == duplicateCategory.Item2.Id);
                product.Categories.Add(existingCategory);
            }

            db.SaveChanges();

        }
    }

Any further suggestions are welcome