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.

Answer 2

I've had to deal with the same exact problem, and the zeros where 'must have' in my scenario. If that's the case for you, you can always disable the foreign key constraint: