EF Core error when inserting new records with multiple relation with another entity.
hennyjoh opened this issue · 2 comments
File a bug
I have tried to find existing issues and found issue 9655 which sound a lot like my problem, but it was closed as a misconfiguration of relations. I believe that is not the issue, since I have reproduced in our production code as well as in a completely new project for reproducing the situation.
Model description:
I have an Item and an ItemDetail record.
An Item can have multible ItemDetails.
An ItemDetail may also point to two different Item records by the FKs ItemId and ReferenceItemID.
The error comes when adding the second FK ReferenceItemID with a navigation property.
Source code to reproduce:
Two console application solutions are attached.
One working OK before the relation is added and one where I get the error.
EFCore9_PK_BeforeError.zip
EFCore9_PKError.zip
This is the exception I get:
Microsoft.EntityFrameworkCore.DbUpdateException: 'An error occurred while saving the entity changes. See the inner exception for details.'
Inner exception
SqlException: The MERGE statement conflicted with the FOREIGN KEY constraint "FK_ItemDetail_Item_ReferenceItemID". The conflict occurred in database "EFCore9_PKError", table "dbo.Item", column 'ItemId'.
Environment:
EF Core version: 9.0.0
Database provider: Microsoft.EntityFrameworkCore.SqlServer
Target framework: .NET 9.0
Operating system: Win 11
IDE: Visual Studio 2022 17.12.3
I just realized that I have used int instead of int? on the foreign keys to Item on ItemDetails. I thought I had reproduced the problem we have in our real application, where the foreign keys are int?, and the problem came after adding the second FK.
The error looks the same. When changing the FK to int? in the code I submitted it works.
Closing this bug report.
Perhaps you could throw an exception with an understandable error message when a mandatory related entity is missing, instead of just failing on the SQL Server with an invalid foreign key? Would help a great deal to realize what is really the problem.