Violation of PRIMARY KEY constraint Cannot insert duplicate key in object

1

I have a problem I am using lazy loading and virtual, but when using this it generates the following error

Violation of PRIMARY KEY constraint Cannot insert duplicate key in object

How can I solve it?

These are my classes:

public class Producto
{
    [Key]
    public Guid ProductoId { get; set; }
    public Guid InquilinoId { get; set; }
    public string Nombre { get; set; }
    public decimal Precio_Publico { get; set; }
    public string Detalle_producto { get; set; }
    public DateTime? Fecha_publicacion { get; set; }
    public bool Activo { get; set; }    
    public string Img_Producto { get; set; }
    public string CodigoBarras { get; set; }

    public virtual Concepto VigenciaPrecio { get; set; }
    public virtual ICollection<Precio> Precios { get; set; }     

    public bool Es_Almacenable { get; set; }
    public int Dias_de_Garantia { get; set; }
    public bool Es_Importado { get; set; }

    public virtual List<Categoria> Categoria { get; set; } = new List<Categoria>();
    public virtual Impuesto Impuesto { get; set; }
    public virtual Precio Precio { get; set; }
}

public class Categoria
{
    public Guid CategoriaId { get; set; }       
    public string Nombre { get; set; }
    public virtual Producto Producto { get; set; }
}

[HttpPost]
public async Task<ActionResult<Guid>> Post(Producto producto)
{
    var user = await userManager.GetUserAsync(HttpContext.User);
    var usercontodo = context.Users.Where(x => x.Id == user.Id).Include(x => x.InquilinoActual).FirstOrDefault();

    if (!string.IsNullOrWhiteSpace(producto.Img_Producto))
    {
        var imgProducto = Convert.FromBase64String(producto.Img_Producto);
               
        producto.Img_Producto = await almacenadorDeArchivos.GuardarArchivo(imgProducto, "jpg", "productos");
    }
           
    producto.InquilinoId = usercontodo.InquilinoActual.ClienteId;         

    context.Add(producto);
    await context.SaveChangesAsync();

    return producto.ProductoId;
}

This is the table categorias:

enter image description here

This is the table productos:

enter image description here

This is the error:

enter image description here

Error:

Microsoft.EntityFrameworkCore.DbUpdateException: An error occurred while updating the entries. See the inner exception for details.

Microsoft.Data.SqlClient.SqlException (0x80131904): Violation of PRIMARY KEY constraint 'PK_Categorias'. Cannot insert duplicate key in object 'dbo.Categorias'. The duplicate key value is (1737b24b-93a4-4ad9-4d8b-08d85a75ca8e)

c#
entity-framework
blazor
asked on Stack Overflow Sep 16, 2020 by Jasiel Torres • edited Sep 16, 2020 by marc_s

2 Answers

3

Your classes mean that, using the standard conventions, a Category can only belong to 1 Product, and a Product has many Categories.

You didn't include the code that builds up a Product (before it is Posted) but the error means that you try to add an existing Category to a new Product.

I think you want to use Categoria as a link table, this should more or less work:

public class Categoria
{
    [Key]
    public Guid CategoriaId { get; set; }       
    [Key]
    public Guid ProductoId { get; set; }

    public string Nombre { get; set; }
    public virtual Producto Producto { get; set; }
}

but you get more control by mapping it in OnModelCreating of the DbContext class.

answered on Stack Overflow Sep 16, 2020 by Henk Holterman • edited Sep 16, 2020 by Henk Holterman
1

This error is usually the result of passing detached entity graphs, or entity graphs that are constructed in a client to a server to be added/updated.

Producto has a set of categories. If EF is trying persist a category, this means that the Producto you sent it and added to the context had at least one Category in it.

Lets say for example in my client code (JavaScript or some other server code calling the API) I create a new Producto:

// C# Pseudo
var producto = new Producto 
{
   // set up product fields...
   // I have a list of categories pre-loaded, so I want to assign one of them...
   Categorias = (new [] { new Categoria { CategoriaId = 3, Nombre = "Three" }}).ToList()
}

Now I pass that Producto to my service. That request has its own scoped DbContext that I set up some info on the Producto and Add it to the context's Producto DbSet. We can assume that the database already has a Category with an ID of 3, but the context isn't aware of it because Producto.Categorias has a reference to a new entity that just happens to have the ID of 3. EF will treat that Category as a new entity and try to insert it along-side the Product. Hence, FK violation as EF tries to insert another Category ID=3.

The complex solution is to attach entities to the current DbContext. As a simple example, with the above Producto coming in:

foreach(var categoria in producto.Categorias)
    context.Attach(categoria); // which will treat the category as unmodified, but expect it to be an existing record.

context.Producto.Add(producto);
context.SaveChanges();

This would have to be done for every existing entity associated with and referenced by the Product. This gets complicated because it assumes that each associated entity is unknown by the DbContext. If you have a scenario where you are dealing with multiple Producto objects, or the Category could be referenced by the Producto and another new row under the Producto, or it's possible that the categories could have been read by the DbContext prior to saving the Producto, attempting to Attach the category could fail if EF is already tracking one with the same ID. This can lead to intermittent errors.

To be safer, before attaching an entity, you should test that the Context isn't already tracking it. If it is already tracking a reference, then you need to replace the reference in Producto:

foreach(var categoria in producto.Categorias)
{
    var existingCategoria = context.Categorias.Local.SingleOrDefault(x => x.CategoriaId == categoria.CategoriaId);
    if (existingCategoria != null)
    {   // Context is tracking one already, so replace the reference in Producto
        product.Categorias.Remove(categoria);
        product.Categorias.Add(existingCategoria);
    }
    else
       context.Attach(categoria); // context isn't tracking it yet.

context.Producto.Add(producto);
context.SaveChanges();

Again, that needs to be done for EVERY reference to safely save a detached entity.

The better solution is to avoid passing entity structures between client and server, and instead pass View Models or DTOs which are POCO (Plain Old C# Objects) containing just the details needed to build an entity.

Given a Producto ViewModel like this:

[Serializable]
public class NewProductoViewModel
{
    public string Nombre { get; set; }
    public ICollection<Guid> CategoriaIds { get; set; } = new List<Guid>();
    //  ... Other details needed to create a new Producto
}

When we go to add this new Producto:

[HttpPost]
public async Task<ActionResult<Guid>> Post(NewProductoViewModel viewModel)
{
    var user = await userManager.GetUserAsync(HttpContext.User);
    var usercontodo = context.Users.Where(x => x.Id == user.Id).Include(x => x.InquilinoActual).FirstOrDefault();

    var producto = new Producto(); // Here is our new, fresh entity..

    // TODO: Here we would copy across all non-reference data, strings, values, etc. from the ViewModel to the Entity...

    if (!string.IsNullOrWhiteSpace(viewModle.Img_Producto))
    {
        var imgProducto = Convert.FromBase64String(viewModel.Img_Producto);
               
        producto.Img_Producto = await almacenadorDeArchivos.GuardarArchivo(imgProducto, "jpg", "productos"); // This is fine, we get our object from the DbContext.
    }
           
    producto.InquilinoId = usercontodo.InquilinoActual.ClienteId;         

    // Now handle the Categorias....
    foreach(var categoriaId in viewModel.CategoriaIds)
    {
        var categoria = context.Categorias.Single(categoriaId);
        producto.Categorias.Add(categoria)
    }

    context.Add(producto);
    await context.SaveChangesAsync();

    return producto.ProductoId;
}

Much of your code is pretty much left as-is, but what it accepts is a deserialized view model, not to be confused with a deserialized block of data that can be confused with an entity. The method constructs a new Entity, then would copy across any details from the view model, before performing a lookup against the Context for any references to associate to the new entity. In the above example I use .Single() which would throw an exception if we passed a CategoriaId that didn't exist. Alternatively you could use .SingleOrDefault() and ignore CategoriaIds that don't exist.

The added benefit of using ViewModels is that you can minimize the amount of data being sent to just the fields needed. We don't send entire Categoria classes to the server, just the IDs that were associated. Most cases where I've seen people passing entities around, the reason is to avoid re-loading the entities more than once. (once when the categorias were read to send to the client, and again when the Producto is saved) This rationale is flawed because what gets sent back to the server may "look" like the entity that the server would have sent the client, but it is not an entity. It is a deserialized block of JSON with the same signature of an entity. It is also "stale" in the sense that any data sent to the server may be many minutes old. When updating entities, the first thing you should check is whether the row version of the data coming from the client matches what is in the server. (Has the server data been updated since?) This means touching the database anyways. We also shouldn't trust anything coming from the server. It is tempting to Attach entities, set a Modified State and call SaveChanges but this will overwrite every field on that entity. Clever people can intercept requests from their browsers and modify the data that is serialized into the request which means that data you don't intend to allow to be updated can be replaced if you merely attach that entity.

answered on Stack Overflow Sep 16, 2020 by Steve Py

User contributions licensed under CC BY-SA 3.0