SQL Server bulk insert broken when new command applies pending commands and goes over the batch parameter limit

[gaglia]

Hi,

I'm experiencing strange behavior using EF 7 with the generated SQL to perform the insert on database.

When I go to save a series of tables through SaveChanges two records of a table are not inserted in the SQL Server database.
From my first analysis it seems that the problem occurs when there is a consistent number of records to insert for the same table so that the SQL generated by EF 7 is broken into different inserts.

Analyzing with SQL Server Profiler I found that two of these inserts are not created correctly in the SQL generated by EF 7
therefore only 64 records are inserted on 66 entities of type veDocumenti_Righe

I have attached the detail of the DbContext before the SaveChanges where you can see that there are all 66 entities in the state of Added

However, analyzing the generated SQL code, I saw that for the two rows that are not actually inserted, the insert statement in the database is missing.
If you analyze the sql code that I attached in the SQLInsert01.sql file you will see that for example the @p2165 parameter is declared and valued correctly but is not then used in the insert

I await your kind feedback
Thank you

Provider and version information

EF Core version: 7.0
Database provider: Microsoft.EntityFrameworkCore.SqlServer
Target framework: .NET 7.0
Operating system: Win11
IDE: Visual Studio 2022 17.4

SQLServerProfiler_Insert.zip

[roji]

@gaglia I'll take a look, but it's typically difficult to get to the root cause of a bug from a few SQL statements.

Are you able to submit a minimal, runnable code sample that reproduces the error?

[gaglia]

@roji I guess it's not easy I understand very well.
And that unfortunately it is not even that simple to be able to extrapolate a test from my project but I try!
I hope to be able to give it to you as soon as possible
Thank you

[roji]

@gaglia it's likely that this is a duplicate of #29502 (also raised in #29562).

I've fixed this in our main branch; can you please try the latest daily build and report whether the problem still occurs?

[gaglia]

Hi @roji

I'm trying but I still get the problem.
I have a doubt to use the latest daily build correctly but I think I'm doing it correctly:

I created the Nuget.config file as mentioned in the guide:

<?xml version="1.0" encoding="utf-8"?>
<configuration>
  <disabledPackageSources>
   <clear />
  </disabledPackageSources>
  <packageSources>
    <clear />
    <add key="dotnet7" value="https://pkgs.dev.azure.com/dnceng/public/_packaging/dotnet7/nuget/v3/index.json" />
    <add key="nuget.org" value="https://api.nuget.org/v3/index.json" />
  </packageSources>
</configuration>

I then used wildcards in the PackageReference:

<ItemGroup>
  <PackageReference Include="Microsoft.EntityFrameworkCore" Version="7.0.0-*" />
  <PackageReference Include="Microsoft.EntityFrameworkCore.Design" Version="7.0.0-*">
  <PrivateAssets>all</PrivateAssets>
    <IncludeAssets>runtime; build; native; contentfiles; analyzers; buildtransitive</IncludeAssets>
  </PackageReference>
  <PackageReference Include="Microsoft.EntityFrameworkCore.SqlServer" Version="7.0.0-*" />
</ItemGroup>

Is that enough? Or should I use version 8.0.0-alpha.1.22416.5 ?

Using 7.0.0-* the problem still exists, I checked what you indicated here #29502 and here #29562 but I was just using HasTrigger():

entity.ToTable(tb => tb.HasTrigger("TR_veDocuments_Rows__Insert"));

The model was generated using EF Core Power Tools and it had already automatically entered the indication of the trigger

On SaveChanges I don't get any error but 2 entities out of 66 of type veDocumenti_Righe are not inserted because they are "ignored" by the SQL code generated by EF as indicated above.

I'm trying to create a mini project by pulling it out of my main project but I can't seem to replicate the problem right now.
The problem I can only replicate in my main project when I have to insert more than 40 entities of type veDocumenti_Righe at the same time

Let me know if I have to try version 8.0.0-alpha.1.22416.5, meanwhile I'll continue to see if I can create a project for you to replicate the problem

Thank you

[roji]

@gaglia the fix hasn't been merged to 7.0 yet, and we don't generally publish daily build packages for patch releases; so yes, you need to use the latest 8.0.0-alpha daily build.

If you're not sure which version you're building, do dotnet restore and then check obj/project.assets.json for the precise version.

[gaglia]

@roji unfortunately the problem persists even with version 8.0.0-alpha.1.22416.5

The problem keeps happening on my main project which has a model with about 800 mapped tables

I'm trying to replicate the problem with a small model of my project to give you the solution to analyze but I haven't been able to yet. It seems that with the smaller model the problem does not arise.

I'm trying to replicate the same situation I hope to succeed as soon as possible

I am attaching the project.assets.json file to make sure I tried with the right version :)
project.assets.zip

Thank you

[roji]

8.0.0-alpha.1.22416.5 corresponds to commit a999af7, which is from August; this is likely because our daily build still point to the dotnet7 feed. Change your nuget.config to point to the dotnet8 feed instead:

<add key="dotnet8" value="https://pkgs.dev.azure.com/dnceng/public/_packaging/dotnet8/nuget/v3/index.json" />

After that, I'm getting package 8.0.0-alpha.1.22567.1, which corresponds to commit 8fdc756 which has the fix.

[gaglia]

Sorry @roji now I corrected the NuGet.config and I tried both with the version indicated by you 8.0.0-alpha.1.22567.1, and with the latest available yesterday 8.0.0-alpha.1.22570.1 but unfortunately the problem in my project is still present.

I'm trying to make a test project that I can give you where to replicate the problem but as mentioned earlier I'm having trouble replicating the problem in another simpler project.

[gaglia]

Hi @roji I finally managed to replicate the problem in a smaller project that I can give you.

I tried to put in the code comments what happens.

Quickly summarize in this case I have 80 entities of type veDocumenti_Righe in Added state that should be saved but after the SaveChanges only 57 are inserted the other 23 remain in an incorrect state with the primary key at 0

I'm trying with version 8.0.0-alpha.1.22571.6
In the SQLScripts folder of the WpfApp.DataAccess project I placed the SQL scripts to create the tables and data to start the project.

If you need any other information, I'm available

A thousand thanks

EFCoreTest.Solution.zip

[roji]

@gaglia thank you, I will look at your repro very soon.

[roji]

@gaglia I took a look at your repro, but was unable to get it to work; it's a pretty big WPF application without clear instructions on how to create and/or seed the database. I added db.Database.EnsureCreated() in MainWindow.xaml.vb.ButtnoNewDoc_Click but got some NullReferenceException.

Can you please narrow this down to a minimal repro, ideally in a simple, console program (no need for WPF), without extra code that isn't necessary for the repro, and with clear instructions on how to reproduce the exception?

[gaglia]

Hi @roji I have created a new console application as you suggested.

To create the database I created a sql script to be run manually: CreateDatabase.sql
You can find it inside the solution in the App project

At that point launching the execution of the program generates the problem.

I tried to describe to you what happens in the code comments:

var varEntities = db.ChangeTracker.Entries().Where(entry => entry.State == EntityState.Modified || entry.State == EntityState.Added);

//80 rows of type veDocuments_Rows to added
var linesToAdded = varEntities.Where(e => e.Entity.GetType().Name == "veDocuments_Lines").Count();

db.SaveChanges();

//After SaveChanges see in sql management studio 57 rows added no 80
//SELECT *
//FROM veDocuments_Rows
//WHERE IdDoc_T = (SELECT TOP 1 IdDoc_T FROM veDocuments ORDER BY IdDoc_T DESC)

// 57 rows added instead of 80
int linesAdded = document.veDocuments_Lines.Where(r => r.IdDoc_R > 0).Count();

// 23 rows not insert with identity primary key IdDoc_R = 0
int linesNoIsert = document.veDocuments_Lines.Where(r => r.IdDoc_R == 0).Count();

I hope so you can be able to test the application.

Thanks again

EFCoreTest.SolutionNew.zip