MERGE INTO on temporal table fails with Attempting to set a non-NULL-able column's value to NULL

[IT-CASADO]

File a bug

Foreword:
I think this is an SQL Server issue with MERGE statement.
But because of EF Core (as an abstraction layer) is using MERGE instead of a simple INSERT INTO I decided to report this here too.
(BTW: EF Core and SQL Server are living in the same house)

I have a very simple EF use case. One table with SQL SYSTEM-VERSIONED TEMPORAL TABLE enabled. The temporal has an non-clustered index!
EF Core shouldn't see anything about TEMPORAL feature.

But because of this bug https://feedback.azure.com/d365community/idea/6716844a-5b25-ec11-b6e6-000d3a4f0da0 (broken link: https://feedback.azure.com/forums/908035-sql-server/suggestions/35519209-merge-not-working-when-index-created-on-temporal-t) i cannot insert multiple records (> 330 records) to my database.

Unfortunately this bug is reported in 2018 and not fixed by SQL Server Team until today :(

I created a small SQL fiddle that shows the error only with SQL Server involved.

For my model EF generates something like this above:

exec sp_executesql N'SET NOCOUNT ON;
DECLARE @inserted0 TABLE ([ImpactId] uniqueidentifier, [ImpactValueTypeId] int, [Date] DATE, [ImpactPeriodId] int, [_Position] [int]);
MERGE [ImpactValue] USING (
VALUES (@p0, @p1, @p2, @p3, 0),
(@p4, @p5, @p6, @p7, 1),
(@p8, @p9, @p10, @p11, 2),
(@p12, @p13, @p14, @p15, 3),
(@p16, @p17, @p18, @p19, 4),
(@p20, @p21, @p22, @p23, 5),

... MORE PARAMETERS

(@p1316, @p1317, @p1318, @p1319, 329),
(@p1320, @p1321, @p1322, @p1323, 330)) AS i ([ImpactId], [ImpactValueTypeId], [Date], [ImpactPeriodId], _Position) ON 1=0
WHEN NOT MATCHED THEN
INSERT ([ImpactId], [ImpactValueTypeId], [Date], [ImpactPeriodId])
VALUES (i.[ImpactId], i.[ImpactValueTypeId], i.[Date], i.[ImpactPeriodId])
OUTPUT INSERTED.[ImpactId], INSERTED.[ImpactValueTypeId], INSERTED.[Date], INSERTED.[ImpactPeriodId], i._Position
INTO @inserted0;

SELECT [t].[ValidFrom], [t].[ValidTo] FROM [ImpactValue] t
INNER JOIN @inserted0 i ON ([t].[ImpactId] = [i].[ImpactId]) AND ([t].[ImpactValueTypeId] = [i].[ImpactValueTypeId]) AND ([t].[Date] = [i].[Date]) AND ([t].[ImpactPeriodId] = [i].[ImpactPeriodId])
ORDER BY [i].[_Position];

',N'@p0 uniqueidentifier,@p1 int,@p2 date,@p3 int,@p4 uniqueidentifier,@p5 int,@p6 date,@p7 int,@p8 uniqueidentifier,@p9 int,@p10 date,@p11 int,@p12 uniqueidentifier,@p13 int,@p14 date,@p15 int,@p16 uniqueidentifier,@p17 int,@p18 date,@p19 int,@p20 uniqueidentifier,@p21 int,@p22 date,@p23 int,@p24 uniqueidentifier,@p25 int,@p26 date,@p27 int,@p28 uniqueidentifier,@p29 int,@p30 date,@p31 int,@p32 uniqueidentifier,@p33 int,@p34 date,@p35 int,@p36 uniqueidentifier,@p37 int,@p38 date,@p39 int,@p40 uniqueidentifier,@p41 int,@p42 date,@p43 int,@p44 uniqueidentifier,@p45 int,@p46 date,@p47 int,@p48 uniqueidentifier,@p49 int,@p50 date,@p51 int,@p52 uniqueidentifier,@p53 int,@p54 date,@p55 int,@p56 uniqueidentifier,@p57 int, ...

Include your code

You can find a working repro here: https://github.com/IT-CASADO/ef-core-3-non-null-able-bug

Please run the only unit test!

Include stack traces

Message: 
    Microsoft.EntityFrameworkCore.DbUpdateException : An error occurred while updating the entries. See the inner exception for details.
    ---- Microsoft.Data.SqlClient.SqlException : Attempting to set a non-NULL-able column's value to NULL.
  Stack Trace: 
    ReaderModificationCommandBatch.Execute(IRelationalConnection connection)
    BatchExecutor.Execute(IEnumerable`1 commandBatches, IRelationalConnection connection)
    RelationalDatabase.SaveChanges(IList`1 entries)
    StateManager.SaveChanges(IList`1 entriesToSave)
    StateManager.SaveChanges(DbContext _, Boolean acceptAllChangesOnSuccess)
    SqlServerExecutionStrategy.Execute[TState,TResult](TState state, Func`3 operation, Func`3 verifySucceeded)
    StateManager.SaveChanges(Boolean acceptAllChangesOnSuccess)
    DbContext.SaveChanges(Boolean acceptAllChangesOnSuccess)
    DbContext.SaveChanges()
    UnitTest1.Test() line 43
    ----- Inner Stack Trace -----
    SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
    SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
    TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
    TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
    SqlDataReader.TryConsumeMetaData()
    SqlDataReader.get_MetaData()
    SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption, Boolean shouldCacheForAlwaysEncrypted)
    SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean isAsync, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest)
    SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry, String method)
    SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
    SqlCommand.ExecuteReader(CommandBehavior behavior)
    SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
    DbCommand.ExecuteReader()
    RelationalCommand.ExecuteReader(RelationalCommandParameterObject parameterObject)
    ReaderModificationCommandBatch.Execute(IRelationalConnection connection)

Include provider and version information

EF Core version: 3.1.8
Database provider: (Microsoft.EntityFrameworkCore.SqlServer)
Target framework: (.NET 3.1)
Operating system: WIndows
IDE: (Visual Studio 2019 16.6.3)

[ErikEJ]

Have you tried with:

options.MaxBatchSize(1)

[IT-CASADO]

I tried this option and it is working with that.

But to be honest, this can only be a temporal workaround not a solution to this problem.

Settings this option (especially to 1) increase the used commands/connections dramatically.
For now I'm setting this to 330, but I doesn't know if this value is constant in each and every environment !?

Is there another way to configure EF to not using MERGE statements at all?

[ajcvickers]

@IT-CASADO In EF Core 5.0, we have changed the default max batch size to 42--see #9270. In addition, this issue should go away entirely once we start using the new ADO.NET batching API--see #18990. We are considering this for EF Core 6.0.

Closing as a duplicate of #18990.

[IT-CASADO]

https://stackoverflow.com/questions/70734060/why-does-a-merge-into-a-temporal-table-with-a-nonclustered-index-in-the-history

[dazinator]

Any update on this? I see closed as not planned so does that mean there is no switch planned to utilise the new batching api?

[ajcvickers]

@dazinator This is closed because it is a duplicate issue.