Variable names must be unique within a query batch or stored procedure

[andy-clymer]

Description

There is an issue when adding new entities of different types to the context and calling save changes at the end when one entity only has a single record to add and another entity has more than two records to add. I followed a variation of this example to produce the issue, and have included the demo application for reproducing the problem.

Problem Code Snippet

var students = new Student[]
{
    new Student{FirstMidName="Carson",LastName="Alexander",EnrollmentDate=DateTime.Parse("2019-09-01")},
    new Student{FirstMidName="Meredith",LastName="Alonso",EnrollmentDate=DateTime.Parse("2017-09-01")},
    new Student{FirstMidName="Arturo",LastName="Anand",EnrollmentDate=DateTime.Parse("2018-09-01")},
    new Student{FirstMidName="Gytis",LastName="Barzdukas",EnrollmentDate=DateTime.Parse("2017-09-01")},
    new Student{FirstMidName="Yan",LastName="Li",EnrollmentDate=DateTime.Parse("2017-09-01")},
    new Student{FirstMidName="Peggy",LastName="Justice",EnrollmentDate=DateTime.Parse("2016-09-01")},
    new Student{FirstMidName="Laura",LastName="Norman",EnrollmentDate=DateTime.Parse("2018-09-01")},
    new Student{FirstMidName="Nino",LastName="Olivetto",EnrollmentDate=DateTime.Parse("2019-09-01")}
};

// SUCCESS - Works whenever there is more than one student being added and a collection of courses are added.

// context.Students.AddRange(students);

// OR

// context.Students.Add(students[0]);
// context.Students.Add(students[1]);

// FAILS - Fails whenever there is only one student being added and three or more courses being added to the context at the same time.

// context.Students.AddRange(students[0]);

 // OR

context.Students.Add(students[0]);

var courses = new Course[]
{
    new Course{Title="Chemistry",Credits=3},
    new Course{Title="Microeconomics",Credits=3},
    new Course{Title="Macroeconomics",Credits=3},
    new Course{Title="Calculus",Credits=4},
    new Course{Title="Trigonometry",Credits=4},
    new Course{Title="Composition",Credits=3},
    new Course{Title="Literature",Credits=4}
};

// NOTE: If you add a single student and only two courses, then save changes will succeed.
context.Courses.AddRange(courses);

// Save changes must be called at the end to have both types of entities saved to the database at the same time for this issue to occur.
context.SaveChanges();

Stack trace

Exception: An error occurred while saving the entity changes. See the inner exception for details.

   at Microsoft.EntityFrameworkCore.Update.ReaderModificationCommandBatch.Execute(IRelationalConnection connection)
   at Microsoft.EntityFrameworkCore.Update.Internal.BatchExecutor.Execute(IEnumerable`1 commandBatches, IRelationalConnection connection)
   at Microsoft.EntityFrameworkCore.Storage.RelationalDatabase.SaveChanges(IList`1 entries)
   at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChanges(IList`1 entriesToSave)
   at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChanges(StateManager stateManager, Boolean acceptAllChangesOnSuccess)
   at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.<>c.<SaveChanges>b__104_0(DbContext _, ValueTuple`2 t)
   at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerExecutionStrategy.Execute[TState,TResult](TState state, Func`3 operation, Func`3 verifySucceeded)
   at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChanges(Boolean acceptAllChangesOnSuccess)
   at Microsoft.EntityFrameworkCore.DbContext.SaveChanges(Boolean acceptAllChangesOnSuccess)
   at Microsoft.EntityFrameworkCore.DbContext.SaveChanges()
   at Sample.Api.Data.DbInitializer.Initialize(SchoolContext context) in C:\source\repos\Sample.Api\Sample.Api\Data\DbInitializer.cs:line 62
   at Program.<Main>$(String[] args) in C:\source\repos\Sample.Api\Sample.Api\Program.cs:line 35

Inner Exception: The variable name '@instered0' has already been declared. Variable names must be unique withing a query batch or stored procedure.

   at Microsoft.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at Microsoft.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   at Microsoft.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
   at Microsoft.Data.SqlClient.SqlDataReader.TryConsumeMetaData()
   at Microsoft.Data.SqlClient.SqlDataReader.get_MetaData()
   at Microsoft.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption, Boolean shouldCacheForAlwaysEncrypted)
   at Microsoft.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean isAsync, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest)
   at Microsoft.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry, String method)
   at Microsoft.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
   at Microsoft.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior)
   at Microsoft.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
   at System.Data.Common.DbCommand.ExecuteReader()
   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReader(RelationalCommandParameterObject parameterObject)
   at Microsoft.EntityFrameworkCore.Update.ReaderModificationCommandBatch.Execute(IRelationalConnection connection)

Include provider and version information

EF Core version: 6.0.0 Database provider: Microsoft.EntityFrameworkCore.SqlServer Target framework: .NET 6.0 Operating system: Windows 11 Pro IDE: Visual Studio Enterprise 2022 (64-bit) 17.0.0

Sample.Api.zip

[tommywiberg]

Same here since upgrading from 5 to 6.

[mahyar1386]

Is this fixed in the latest EF Core 6.0.0 for sql-server? Couse I'm facing it too since I upgraded to 6

[ajcvickers]

@mahyar1386 The fix will be included in the 6.0.1 patch, as indicated by the milestone. The fix is already included in the latest daily build.

[failwyn]

@ajcvickers I'm not seeing any 6.0.1-* builds in the dotnet6 or dotnet7 nuget repositories, can you clarify how we can get the fix for this issue; also, is there an estimate for when 6.0.1 will be available?

[andy-clymer]

To temporarily work around this issue until the release of 6.0.1 is made available, I set the MaxBatchSize to 1 in the Sql Server configuration for EF.

[ajcvickers]

@failwyn Unfortunately, patches are not included in the daily builds. The 7.0.0 builds contain this fix. The patch is scheduled for release in the coming days.

[brbarnett]

To temporarily work around this issue until the release of 6.0.1 is made available, I set the MaxBatchSize to 1 in the Sql Server configuration for EF.

For anyone else struggling with this, @andy-clymer's workaround worked for us as well. Add o => o.MaxBatchSize(1) to your AddSqlServer configuration. Thanks, @andy-clymer!

[ajcvickers]

FYI for those impacted by this issue: EF Core 6.0.1 is now available from NuGet.