EF Core - The MERGE statement conflicted with the FOREIGN KEY constraint

Question

I need some help understanding the error I'm getting when I try to update a product.

I have read this similar question, and tried the accepted answer (placing a _context.SaveChanges() after each table, before the final saving of the complete product), but I still get the same error as described below.

These are the involved models:

public class Product
{
    public int Id { get; set; }
    // some more properties
    public ICollection<IdentifierForProduct> Identifiers { get; set; }
}

public class IdentifierForProduct
{
    public int Id { get; set; }
    public int ProductId { get; set; }
    public int ProductIdentifierId { get; set; }
    public string Value { get; set; } // E.g. "4902505154881"

    public ProductIdentifier Identifier { get; set; }
    public Product Product { get; set; }
}

public class ProductIdentifier
{
    public int Id { get; set; }
    public string Label { get; set; } // E.g. "EAN"

    public ICollection<IdentifierForProduct> ProductIdentifiers { get; set; }
}

Initially, after form post, the Identifiers are set (VMProduct is the product view model):

List<IdentifierForProduct> Identifiers = new List<IdentifierForProduct>();
if (VMProduct.Identifiers != null)
{
    for (var i = 0; i < VMProduct.Identifiers.Count; i++)
    {
        Identifiers.Add(new IdentifierForProduct
        {
            ProductId = VMProduct.Id,
            ProductIdentifierId = VMProduct.Identifiers[i].Id,
            Value = VMProduct.Identifiers[i].Value
        });
    }
}

Then the product properties are altered according to the changes made in the form:

Product DbM = await GetProduct(VMProduct.Id);
// some more properties are set
DbM.Identifiers = Identifiers;
_context.Update(DbM);
await _context.SaveChangesAsync();

This exception is thrown on await _context.SaveChangesAsync();:

SqlException: The MERGE statement conflicted with the FOREIGN KEY constraint "FK_IdentifiersForProducts_ProductIdentifiers_ProductIdentifierId". The conflict occurred in database "MyStore", table "dbo.ProductIdentifiers", column 'Id'. The statement has been terminated. System.Data.SqlClient.SqlCommand+<>c.b__108_0(Task result)

DbUpdateException: An error occurred while updating the entries. See the inner exception for details. Microsoft.EntityFrameworkCore.Update.ReaderModificationCommandBatch+d__32.MoveNext()

This is the GetProduct() method:

public async Task<Product> GetProduct(int Id)
{
    Product DbM = await _context.Products
        .Include(ic => ic.InCategories)
            .ThenInclude(pc => pc.ProductCategory)
        .Include(t => t.Type)
            .ThenInclude(i => i.Identifiers) // ProductIdentifiersInTypes
                .ThenInclude(i => i.Identifier)
            .Include(t => t.Type)
                .ThenInclude(p => p.Properties) // ProductPropertiesInTypes
                    .ThenInclude(p => p.Property)
                        .ThenInclude(o => o.Options)
        .Include(p => p.ProductPropertyOptions)
        .Where(p => p.Id == Id)
        .SingleOrDefaultAsync();
    return DbM;
}

Answer 1

The reason why this error happens is because, your foreign key 'ProductIdentifierId' in 'IdentifierForProduct' probably has value 0 at here:

List<IdentifierForProduct> Identifiers = new List<IdentifierForProduct>();
if (VMProduct.Identifiers != null)
{
    for (var i = 0; i < VMProduct.Identifiers.Count; i++)
    {
        Identifiers.Add(new IdentifierForProduct
        {
            ProductId = VMProduct.Id,
            ProductIdentifierId = VMProduct.Identifiers[i].Id, //here, your id should be 0
            Value = VMProduct.Identifiers[i].Value
        });
    }
}

When entity framework core encounters value 0 for foreign key, it throws this kind of error, because it cannot insert foreign value 0 which is the primary key of some object. Obviously, primary keys cannot be value 0.