分离的实体导致重复的ID问题

我使用EF4的经验非常有限。 我成功地从分离状态的web服务反序列化实体,现在我想保存到数据库。 当使用SaveChanges时,我得到以下异常:

System.Data.UpdateException:更新条目时发生错误。 详情请参阅内部例外。 ---> System.Data.SqlClient.SqlException:违反PRIMARY KEY约束'[主键约束名]'。 无法在对象'[关联表名]'中插入重复键。 重复的键值是(1)。 该语句已终止。

我试图保存的实体将相关实体作为实体集合的属性和属性。

来自Web服务的ID用作表的主键,因此不使用自动生成的ID。

以下测试说明了我试图解决的问题:

    [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");
        }
    }

感谢您的帮助。

编辑:我可以使用下面的代码得到重复组的列表

                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())

我尝试过的东西,但没有奏效:

- 以不变的状态复制到数据上下文的实体。 由于附加CategoryGroupEntity会导致所有相关实体被连接,因此重复的关键问题依然存在

从Categories集合中删除实体实例并将它们替换为首次创建的CategoryGroupEntity实例会导致相同的问题

- 重复实体实例的结果导致第二个类别丢失组id 1

作为一个方面说明,当数据库中已经存在特定的CategoryGroupEntity并且尝试保存具有相同ID的实体时,我还需要避免重复的关键问题。

所以,我需要避免在具有该ID的实体存在于数据库中或在ObjectStateManager中添加状态时出现重复键问题。 上面包含的测试包含了两种情况。


在实体框架中,如果你想添加的实体已经存在,那么只需要给它的主键不起作用,你就需要加载该实体并赋值它

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

所以在你的代码中,例如Id = 1的TypeEntity已经存在,那么你需要将上面的代码改成类似的东西

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

EF在对象上工作,以便说您不修改该对象,而只是将其用作需要查找该对象并将其分配到要使用的关系的关系。

更新类似的例如

 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();

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


我得到它的工作,但它绝对不是最好的方式来做到这一点。

我使用的保存方法已包括在下面:

  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();

        }
    }

欢迎任何进一步的建议

链接地址: http://www.djcxy.com/p/33569.html

上一篇: Detached entities causing duplicate ID issues

下一篇: To detach or not to detach? entity framework IBindingList issue