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 {"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}
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; }
}
I now generate 2 products.
- Both products are produced by the same manufacturer.
- The List of PossibleSuppliers does also contain same suppliers
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.
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; }
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!!
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; }
}
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.
I have attached how the DB looks in SQL-Server
