Batched query breaks after upgrade to 7.0 - The UPDATE statement conflicted with the FOREIGN KEY constraint

[cornem]

After upgrading to version 7.0 of EntityFramework, we are encountering an error. The error thrown is The UPDATE statement conflicted with the FOREIGN KEY constraint.

Minimal extract from actual code:

foreach (var salesOrder in salesOrders)
{
    var delivery = new Delivery
    {
        AddressId = salesOrder.ShippingAddressId,
	CustomerName = salesOrder.Name,
	DeliveryDate = date,
	Position = -1,
	Route = route,
	TimeslotEnd = deliveryDay?.DeliveryTo,
	TimeslotStart = deliveryDay?.DeliveryFrom
    };

    Context.Deliveries.Add(delivery);

    // add reference from PackingSlip to Delivery
    if (source == ConfigureDeliverySource.PackingSlips)
    {
 	var packingSlip = packingSlips.First(p => salesOrder.PackingSlipNumber == p.Number);
	delivery.PackingSlips.Add(packingSlip);
   }
}

await Context.SaveChangesAsync().ConfigureAwait(false);

This works OK as long as there are only a few Delivery entities to insert, however, after 42 entities, EF creates multiple inserts, and that's when it apparently goes wrong. When it subsequently tries to update the PackingSlip entities to set the DeliveryId reference, there is now an incorrect reference (see @p651):

[Parameters=[@p652='1054315', @p651='-2147482352' (Nullable = true), @p654='1054316', @p653='283504' (Nullable = true), @p656='1054317', @p655='283505' (Nullable = true), @p658='1054318', @p657='283505' (Nullable = true), @p660='1054319', @p659='283504' (Nullable = true), @p662='1054320', @p661='283504' (Nullable = true), @p664='1054321', @p663='283504' (Nullable = true), @p666='1054322', @p665='283504' (Nullable = true), @p668='1054323', @p667='283504' (Nullable = true), @p670='1054324', @p669='283504' (Nullable = true), @p672='1054325', @p671='283504' (Nullable = true), @p674='1054326', @p673='283504' (Nullable = true), @p676='1054327', @p675='283504' (Nullable = true), @p678='1054328', @p677='283504' (Nullable = true), @p680='1054329', @p679='283504' (Nullable = true), @p682='1054330', @p681='283504' (Nullable = true), @p684='1054331', @p683='283504' (Nullable = true), @p686='1054332', @p685='283504' (Nullable = true), @p688='1054333', @p687='283504' (Nullable = true), @p690='1054334', @p689='283504' (Nullable = true), @p692='1054335', @p691='283504' (Nullable = true), @p694='1054336', @p693='283504' (Nullable = true), @p696='1054337', @p695='283504' (Nullable = true), @p698='1054338', @p697='283504' (Nullable = true), @p700='1054339', @p699='283506' (Nullable = true), @p702='1054340', @p701='283506' (Nullable = true), @p704='1054341', @p703='283506' (Nullable = true), @p706='1054342', @p705='283507' (Nullable = true), @p708='1054343', @p707='283508' (Nullable = true), @p710='1054344', @p709='283509' (Nullable = true), @p712='1054345', @p711='283509' (Nullable = true), @p714='1054346', @p713='283509' (Nullable = true), @p716='1054347', @p715='283509' (Nullable = true), @p718='1054348', @p717='283509' (Nullable = true), @p720='1054349', @p719='283509' (Nullable = true), @p722='1054350', @p721='283509' (Nullable = true), @p724='1054351', @p723='283509' (Nullable = true), @p726='1054352', @p725='283510' (Nullable = true), @p728='1054353', @p727='283511' (Nullable = true), @p730='1054354', @p729='283512' (Nullable = true), @p732='1054355', @p731='283513' (Nullable = true), @p734='1054356', @p733='283514' (Nullable = true)], CommandType='Text', CommandTimeout='300']
      SET NOCOUNT ON;
      UPDATE [PackingSlips] SET [DeliveryId] = @p651
      OUTPUT INSERTED.[Checksum]
      WHERE [PackingSlipId] = @p652;
      UPDATE [PackingSlips] SET [DeliveryId] = @p653
      OUTPUT INSERTED.[Checksum]
      WHERE [PackingSlipId] = @p654;
     
     // shortened for brevity

Include provider and version information

EF Core version: 7.0.0
Database provider: Microsoft.EntityFrameworkCore.SqlServer
Target framework: net7.0
Operating system: Windows 11
IDE: Visual Studio 2022 17.4

[cornem]

Possibly related to #29356, but that looks like it was already closed and is included in 7.0 GA?

[ajcvickers]

Possible duplicate of #29539, and maybe related to #29502.

/cc @roji

[roji]

@cornem are you able to submit a minimal code sample which reproduces the error?

[cornem]

@roji I've been trying that for the last few hours. If I reduce the source code (it's a complex data context with dozens of cross-referenced tables) to about three entities, the error occurs every time I run it against a copy of the database with actual production data. When I copy the same data out of the original database to a new database with a simple structure, the problem goes away. Also downgrading Microsoft.EntityFrameworkCore back to 6.0.11 makes the problem go away.

So it must be something in v7 that's triggering the error, I'm just not able yet to isolate exactly how to reproduce it.

[roji]

@cornem thanks for the effort you're putting into this - it's not suprising that specific data is needed in the database in order to trigger this.

Is it an option for you to send the data along with the code sample (hopefully reducing it to a minimum before doing so)? You can share it via my email (on my github profile).

[cornem]

@roji I've been trying; but even then I run into the strange phenomenon that the issue somehow gets resolved.

• If I reduce the database to only a few tables with sample data, I can replicate the crash. As soon as I then try to export a bacpac or bak file from SSMS, and then re-import, the same code now works on the imported database (keeps breaking on the original).
• Also, if I remove and re-create foreign keys in the database on table [dbo].[Deliveries], the problem goes away.

Not sure if that helps to narrow down what's going on, but I'll keep trying to come up with a reproducible sample 😅

[roji]

@cornem thanks again for spending time on this - it's unfortunate it's so difficult. I'll try to to inspect the code today, but a repro would obviously be the best way forward. The moment you get anything reliable together, please feel free to send it to me.

[cornem]

I've emailed you a sample that should fail under 7.0.0.

[roji]

Thanks @cornem, I'll look into it.

[roji]

Duplicate of #29502

[roji]

@cornem thanks again for the precise repro (I wish all users submitted such repros).

This turned out to be a duplicate of #29502 - when running against the commit of the fix, the error no longer occurs (but does one commit before). The fix has already been merged into main (for 8.0), and a PR is out for backporting it to 7.0.x as well (#29590).

Can you try running with the latest daily build (docs) and confirm that the issue goes away for you? Unfortunately, it may take quite some time for a 7.0 patch version to come out with the fix; in the meantime you can either use a 8.0 daily build (which should be fine - almost identical to 7.0.0), or add HasTrigger() to the target table as per these docs, to make EF switch back to the older and less efficient insertion strategy. To do the latter, add the following to OnModelCreating (I've confirmed this makes the error go away):

modelBuilder.Entity<Delivery>().ToTable(t => t.HasTrigger("foo"));

[cornem]

Thanks for your efforts @roji and glad to hear the issue was already known and possibly fixed.

I've tried your suggestions:

• The problem goes away using 7.0.0 packages with HasTrigger().
• However, if I use the daily builds with versions 8.0.0-alpha.1.22415.13 or the current latest 8.0.0-alpha.1.22416.5 I still get the error.

[roji]

@cornem see #29539 (comment) for how to get the actual latest 8.0 build (or daily build docs aren't yet up to date for 8.000). Can you try with 8.0.0-alpha.1.22567.1 or above and report whether that helps?

[cornem]

After updating the NuGet.config file to the dotnet8 feed, I tested it against version 8.0.0-alpha.1.22570.1 which does indeed work. Thanks again for your efforts. For now we'll use the workaround so we can use the stable 7.0.0 packages until this fix is included in a release.

[roji]

Thanks for confirming!