“违反主键约束"...".无法在对象中插入重复键 [英] "Violation of PRIMARY KEY constraint '...'. Cannot insert duplicate key in object
问题描述
我刚刚开始使用Codefirst-Approach和C#，Linq和MSSQLSERVER创建我的第一个项目，并且在尝试插入一个包含对另一个表中已有元素的引用的新DB条目时遇到了问题.
I just started to make my first project with Codefirst-Approach with C#, Linq and MSSQLSERVER and run into an problem when trying to insert a new DB-entry that contains an reference to an already existing element from another table.
InnerException {违反主键约束'PK_dbo.Manufacturers'.无法在对象'dbo.Manufacturers'中插入重复键.重复键值为(1d262e43-b9b6-4752-9c79-95d955d460ab).\ r \ nThe语句已终止.} System.Exception {System.Data.SqlClient.SqlException}
InnerException {"Violation of PRIMARY KEY constraint 'PK_dbo.Manufacturers'. Cannot insert duplicate key in object 'dbo.Manufacturers'. The duplicate key value is (1d262e43-b9b6-4752-9c79-95d955d460ab).\r\nThe statement has been terminated."} System.Exception {System.Data.SqlClient.SqlException}
我将问题分解为一个简单的项目，然后将其上传到共享中.我的数据结构包含一个class.Product，它链接到Manufacturer对象和一个可能的供应商列表.
I broke the problem down to a simple project that I will upload to a share. My data structure contains a class.Product that links to a Manufacturer object and a List of possible Suppliers.
public class Product
{
[Key]
public Guid Id { get { return _id; } set { _id = value; } }
private Guid _id = Guid.NewGuid();
public string Name { get; set; }
public Manufacturer Manuf { get; set; }
public List<Supplier> PossibleSupplier { get { return _possibleSupplier; } set { _possibleSupplier = value; } }
private List<Supplier> _possibleSupplier = new List<Supplier>();
}
public class Supplier
{
[Key]
public Guid Id { get { return _id; } set { _id = value; } }
private Guid _id = Guid.NewGuid();
public string Name { get; set; }
}
public class Manufacturer
{
[Key]
public Guid Id { get { return _id; } set { _id = value; } }
private Guid _id = Guid.NewGuid();
public string Name { get; set; }
}
我现在生成2个产品.
- 两种产品均由同一制造商生产.
- 可能的供应商列表中也包含相同的供应商
private void GenerateProducts()
{
Manufacturer manufactuer1 = new Manufacturer() { Name = "mainManuf 1" };
Supplier supplier1 = new Supplier() { Name = "first Supplier" };
Supplier supplier2 = new Supplier() { Name = "second Supplier" };
Product firstProduct = new Product() { Name = "Product 1", Manuf = manufactuer1, PossibleSupplier = new List<Supplier>() { supplier1, supplier2 } };
Product secondProduct = new Product() { Name = "Product 2", Manuf = manufactuer1, PossibleSupplier = new List<Supplier>() { supplier1 } };
productList_ = new List<Product>() { firstProduct, secondProduct };
}
以下方法用于存储/更新数据库条目
The following method is used for storing/updating the DB entries
public static class DbHandler
{
public static bool StoreProduct(Product product)
{
using (ProductDbContext dbObject = new ProductDbContext())
{
try
{
dbObject.Products.AddOrUpdate(product);
dbObject.SaveChanges();
}
catch (Exception ex)
{
//
return false;
}
}
return true;
}
}
public class ProductDbContext : DbContext
{
public ProductDbContext()
{
Database.SetInitializer<ProductDbContext>(new DropCreateDatabaseAlways<ProductDbContext>());
this.Database.Connection.ConnectionString = sqlConnection.ConnectionString;
}
public DbSet<Product> Products { get; set; }
public DbSet<Supplier> Suppliers { get; set; }
public DbSet<Manufacturer> Manufacturers { get; set; }
private static SqlConnectionStringBuilder sqlConnection = new SqlConnectionStringBuilder()
{
DataSource = "localhost\\MSSQLSERVER2019", // update me
UserID = "", // update me
Password = "", // update me
InitialCatalog = "ProductDb",
IntegratedSecurity = true
};
}
可以成功插入第一个产品.
The insertion of the first product can be done without problems.
插入其他具有独特制造商和供应商的产品也将毫无问题.**因此，我没有主键唯一性的问题.**
Also inserting additional products that will have unique manufacturers and suppliers will work without problem. **So I do not have the problem of uniqueness of my primary keys. **
当我想向现有条目添加具有外键的新条目时，只会收到此错误.
I only receive this error, when I like to add a new entry that has a foreign key to an already existing entry.
使用 dbObject.Products.AddOrUpdate(product); 代替 dbObject.Products.Add(product); 并没有解决我的问题.在添加第二个产品之前，我也无法删除制造商条目，因为这将违反我的第一个产品的外键…我找到了可能的解决方案为制造商(通过为ManufacturerId添加其他属性)
Using dbObject.Products.AddOrUpdate(product); instead of dbObject.Products.Add(product); have not solved my problem.
I am also not able to remove the manufacturer entry before adding the second product, because this will violate the foreign key of my first product…
I found a possible solution for manufacturer by adding an additional property for ManufacturerId
public Guid? ManuId { get; set; }
[ForeignKey("ManuId")]
public Manufacturer Manuf { get; set; }
到我的数据对象，但是我不知道如何使用我的List PossibleSupplier来做到这一点?
to my data object, but I would not have an idea how to do this with my List PossibleSupplier??
有人可以将我推向正确的方向吗?
Can someone please push me into the right direction?
!!非常感谢您的快速重播！
!!Many thanks for the fast replays!!
我已经更新了我的DataStructure，如下所示:
I have updated my DataStructure as following:
public class Product
{
[Key]
public Guid Id { get { return _id; } set { _id = value; } }
private Guid _id = Guid.NewGuid();
public string Name { get; set; }
public virtual Manufacturer Manufacturer { get; set; }
public virtual ICollection<Supplier> PossibleSupplier { get; set; }
}
public class Supplier
{
[Key]
public Guid Id { get { return _id; } set { _id = value; } }
private Guid _id = Guid.NewGuid();
public string Name { get; set; }
[ForeignKey("Product")]
public Guid ProductId { get; set; }
public virtual Product Product { get; set; }
}
public class Manufacturer
{
[Key]
public Guid Id { get { return _id; } set { _id = value; } }
private Guid _id = Guid.NewGuid();
public string Name { get; set; }
[ForeignKey("Product")]
public Guid ProductId { get; set; }
public virtual ICollection<Product> Product { get; set; }
}
但是在尝试插入第二个条目时，我仍然收到违反PRIMARY KEY约束'PK_dbo.Manufacturers'.无法插入重复的键..."的错误.
But I still get the "Violation of PRIMARY KEY constraint 'PK_dbo.Manufacturers'. Cannot insert duplicate key..." error while trying to insert the second entry.
推荐答案
好的，我相信我知道您的问题是什么.这部分与这里有关:
Okay so I believe I know what your issue is. It lies somewhat with this portion here:
private void GenerateProducts()
{
Manufacturer manufactuer1 = new Manufacturer() { Name = "mainManuf 1" };
Supplier supplier1 = new Supplier() { Name = "first Supplier" };
Supplier supplier2 = new Supplier() { Name = "second Supplier" };
Product firstProduct = new Product() { Name = "Product 1", Manuf = manufactuer1, PossibleSupplier = new List<Supplier>() { supplier1, supplier2 } };
Product secondProduct = new Product() { Name = "Product 2", Manuf = manufactuer1, PossibleSupplier = new List<Supplier>() { supplier1 } };
productList_ = new List<Product>() { firstProduct, secondProduct };
}
在下面的两个部分中分配Manuf = Manufacturer1时，由于制造商尚不存在，它将对第一个插入内容起作用.现在，在第二个插入上不起作用的原因是由于以下代码:
When you assign Manuf = manufacturer1 in both portions below it will work for the first insert because the manufacturer does not exist yet. Now the reason why on the second insert it does not work is because of your code below:
using (ProductDbContext dbObject = new ProductDbContext())
{
try
{
dbObject.Products.AddOrUpdate(product);
dbObject.SaveChanges();
}
catch (Exception ex)
{
//
return false;
}
}
现在，当您插入第二个产品时，它将抛出重复键异常，因为您没有在上下文中引用现有实体.您应该将其更改为以下内容:
Right now when you go to insert the second product, it will throw the duplicate key exception because you are not referencing the existing entity within your context. You should change it to something like the following:
using (ProductDbContext dbObject = new ProductDbContext())
{
try
{
//Need to check if the manufacturer already exists in the db, if it does
//make sure your project references the EXISTING entity within your context
var check = dbObjec.Manufacturer.Where(x => x.Id == product.Manufacturer.Id).FirstOrDefault();
if (check != null)
product.Manufacturer = check;
dbObject.Products.Add(product);
dbObject.SaveChanges();
}
catch (Exception ex)
{
//
return false;
}
}
如果您没有在上下文中引用现有制造商，然后进行分配，EF将假定您尝试添加一个不引用现有制造商的新制造商.
If you don't reference the existing manufacturer within the context and then assign it, EF will assume you are trying to add a new one not reference the existing one.
这篇关于“违反主键约束"...".无法在对象中插入重复键的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
