SqlServer BulkInsert into TemporalTable with RowVersion column and SetOutputIdentity = true

[rafal1104]

when I have table defined: _public class TestEntity { public int Field1 { get; set; } public string Fiield2 { get; set; } [Key, DatabaseGenerated(DatabaseGeneratedOption.Identity)] public int ID { get; set; } [Timestamp] public byte[] RowVersion { get; set; } } and after make await _dbContext.BulkInsertAsync(lista, options => options.SetOutputIdentity = true); then I have error from SQL: Microsoft.Data.SqlClient.SqlException (0x80131904): Operand type clash: timestamp is incompatible with datetime2 and I think this is because of wrong order of columns in queries what executed by this operations, this is from profiler:

**SELECT TOP 0 T.[ID], T.[Field1], T.[Fiield2], [RowVersion] = CAST('' AS varbinary(8)) INTO [dbo].[testEntitiesTempad84a315] FROM [dbo].[testEntities] AS T LEFT JOIN [dbo].[testEntities] AS Source ON 1 = 0;

select @@trancount; SET FMTONLY ON select * from [dbo].[testEntitiesTempad84a315] SET FMTONLY OFF exec ..sp_tablecollations_100 N'[dbo].[testEntitiesTempad84a315]'

insert bulk [dbo].[testEntitiesTempad84a315] ([ID] Int, [Field1] Int, [Fiield2] NVarChar(max) COLLATE Polish_CI_AS)

SELECT TOP 0 T.[ID], T.[Field1], T.[Fiield2], T.[PeriodStart], T.[PeriodEnd], [RowVersion] = CAST('' AS varbinary(8)) INTO [dbo].[testEntitiesTempad84a315Output] FROM [dbo].[testEntities] AS T LEFT JOIN [dbo].[testEntities] AS Source ON 1 = 0;

MERGE [dbo].[testEntities] WITH (HOLDLOCK) AS T USING (SELECT TOP 1000 * FROM [dbo].[testEntitiesTempad84a315] ORDER BY [ID]) AS S ON T.[ID] = S.[ID] WHEN NOT MATCHED BY TARGET THEN INSERT ([Field1], [Fiield2]) VALUES (S.[Field1], S.[Fiield2]) OUTPUT INSERTED.[ID], INSERTED.[Field1], INSERTED.[Fiield2], INSERTED.[RowVersion], INSERTED.[PeriodStart], INSERTED.[PeriodEnd] INTO [dbo].[testEntitiesTempad84a315Output];**

and here we can see in select before merge we have fields: T.[PeriodStart], T.[PeriodEnd], [RowVersion] but after in merge we have that: INSERTED.[RowVersion], INSERTED.[PeriodStart], INSERTED.[PeriodEnd] then I think it try to insert "RowVersion" value into "PeriodStart" column and give us this error message.

[rafal1104]

HOW TO REPRODUCE IN TESTS: When in tests will changed name of class: [ItemHistory] to example [ItemHist] and add in both class [Item] and [ItemHist] fields: [Timestamp] public byte[]? RowVersion { get; set; } and after in OnModelCreating in SqlServer secion add: modelBuilder.Entity().ToTable(nameof(Item), b => b.IsTemporal()); modelBuilder.Entity().ToTable(nameof(ItemHist), b => b.IsTemporal());

Then in EFCoreBulkTest.OperationsTest will appear error what I descibe in post before.

[rafal1104]

solution (not production but work and you can FIX this) in file EFCore.BulkExtensions.SqlAdapters.SqlQueryBuilder in function CreateTableCopy when changed code: public virtual string CreateTableCopy(string existingTableName, string newTableName, TableInfo tableInfo, bool isOutputTable = false) { // TODO: (optionaly) if CalculateStats = True but SetOutputIdentity = False then Columns could be ommited from Create and from MergeOutput List columnsNames = (isOutputTable ? tableInfo.OutputPropertyColumnNamesDict : tableInfo.PropertyColumnNamesDict ).Values.ToList(); string timeStampColumn = ""; if (tableInfo.TimeStampColumnName != null) { columnsNames.Remove(tableInfo.TimeStampColumnName); timeStampColumn = $", [{tableInfo.TimeStampColumnName}] = CAST('' AS {TableInfo.TimeStampOutColumnType})"; // tsType:varbinary(8) }

string statsColumn = (tableInfo.BulkConfig.CalculateStats && isOutputTable) ? $", [{tableInfo.SqlActionIUD}] = CAST('' AS char(1))" : "";

var q = $"SELECT TOP 0 {GetCommaSeparatedColumns(columnsNames, "T")}" + timeStampColumn + statsColumn + " " +
        $"INTO {newTableName} FROM {existingTableName} AS T " +
        $"LEFT JOIN {existingTableName} AS Source ON 1 = 0;"; // removes Identity constrain
return q;

}

to this

public virtual string CreateTableCopy(string existingTableName, string newTableName, TableInfo tableInfo, bool isOutputTable = false) { // TODO: (optionaly) if CalculateStats = True but SetOutputIdentity = False then Columns could be ommited from Create and from MergeOutput List columnsNames = (isOutputTable ? tableInfo.OutputPropertyColumnNamesDict : tableInfo.PropertyColumnNamesDict ).Values.ToList(); string timeStampColumn = ""; if (tableInfo.TimeStampColumnName != null) { columnsNames.Remove(tableInfo.TimeStampColumnName); timeStampColumn = $", [{tableInfo.TimeStampColumnName}] = CAST('' AS {TableInfo.TimeStampOutColumnType})"; // tsType:varbinary(8) } string temporalTableColumns = ""; if (tableInfo.HasTemporalColumns) { tableInfo.BulkConfig.TemporalColumns.ForEach(columnName => { columnsNames.Remove(columnName); temporalTableColumns += $", T.[{columnName}]"; }); }

 string statsColumn = (tableInfo.BulkConfig.CalculateStats && isOutputTable) ? $", [{tableInfo.SqlActionIUD}] = CAST('' AS char(1))" : "";

 var q = $"SELECT TOP 0 {GetCommaSeparatedColumns(columnsNames, "T")}" + timeStampColumn + temporalTableColumns + statsColumn + " " +
         $"INTO {newTableName} FROM {existingTableName} AS T " +
         $"LEFT JOIN {existingTableName} AS Source ON 1 = 0;"; // removes Identity constrain
 return q;

}

then order of columns is good and test passed.

[rafal1104]

I try to make pull request and I see problem not fixed at all, now is another error:

Microsoft.Data.SqlClient.SqlException : Cannot insert the value NULL into column 'PeriodEnd', table 'tempdb.dbo.#ItemTempadb9a966___000000000036'; column does not allow nulls. INSERT fails. The statement has been terminated.

[borisdj]

Thx for contrib, if you don't find a solution, I'll look into it in a few days.