zzzprojects
AllowDuplicateKeys doesn't work on BulkInsert
Description
I'm using BulkInsert to insert a list of records where duplicate keys are possible. For this I'm using AllowDuplicateKeys = true. Now this doesn't seem to work.
Exception
If you are seeing an exception, include the full exceptions details (message and stack trace).
Exception message:
Exception has been thrown by the target of an invocation.
---> System.Exception: An error occured because the primary key specified is not unique, you can set the property 'AllowDuplicateKeys' to true to allow duplicate key. See the inner exception for details.
---> Microsoft.Data.SqlClient.SqlException (0x80131904): The MERGE statement attempted to UPDATE or DELETE the same row more than once. This happens when a target row matches more than one source row. A MERGE statement cannot UPDATE/DELETE the same row of the target table multiple times. Refine the ON clause to ensure a target row matches at most one source row, or use the GROUP BY clause to group the source rows.
Stack trace:
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.TryHasMoreRows(Boolean& moreRows)
at Microsoft.Data.SqlClient.SqlDataReader.TryReadInternal(Boolean setTimeout, Boolean& more)
at Microsoft.Data.SqlClient.SqlDataReader.Read()
at System.Data.Common.DataAdapter.FillLoadDataRow(SchemaMapping mapping)
at System.Data.Common.DataAdapter.FillFromReader(DataSet dataset, DataTable datatable, String srcTable, DataReaderContainer dataReader, Int32 startRecord, Int32 maxRecords, DataColumn parentChapterColumn, Object parentChapterValue)
at System.Data.Common.DataAdapter.Fill(DataSet dataSet, String srcTable, IDataReader dataReader, Int32 startRecord, Int32 maxRecords)
at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet)
at .(DbCommand , BulkOperation , Int32 )
at .(DbCommand , BulkOperation , Int32 )
at .( , DbCommand )
at .Execute(List`1 actions)
ClientConnectionId:89939aec-0656-44cf-b2cc-d886f1cbfd8c
Error Number:8672,State:1,Class:16
--- End of inner exception stack trace ---
at .Execute(List`1 actions)
at .(List`1 )
at Z.BulkOperations.BulkOperation.Execute()
at Z.BulkOperations.BulkOperation.BulkInsert()
at .BulkInsert[T](DbContext this, IEntityType entityType, IEnumerable`1 list, Action`1 options, SavingSelector savingSelector, Boolean forceSpecificTypeMapping)
at .BulkInsert[T](DbContext this, IEnumerable`1 entities, Action`1 options, Boolean isBulkSaveChanges)
at Z.EntityFramework.Extensions.PublicInternalBulkOperationManager.BulkInsertCast[T](BulkOperation`1 this, DbContext context, List`1 list)
--- End of inner exception stack trace ---
at System.RuntimeMethodHandle.InvokeMethod(Object target, Object[] arguments, Signature sig, Boolean constructor, Boolean wrapExceptions)
at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture)
at System.Reflection.MethodBase.Invoke(Object obj, Object[] parameters)
at .BulkInsert[T](DbContext this, IEntityType entityType, IEnumerable`1 list, Action`1 options, SavingSelector savingSelector, Boolean forceSpecificTypeMapping)
at .BulkInsert[T](DbContext this, IEnumerable`1 entities, Action`1 options, Boolean isBulkSaveChanges)
at .(DbContext this, List`1 , Action`1 )
at .(DbContext this, StateManager , IReadOnlyList`1 , Action`1 )
at .(DbContext this, StateManager , IReadOnlyList`1 , Action`1 )
at .(DbContext this, Action`1 , DbContext )
at DbContextExtensions.(DbContext this, Action`1 , DbContext )
at .BulkInsert[T](DbContext this, IEntityType entityType, IEnumerable`1 list, Action`1 options, SavingSelector savingSelector, Boolean forceSpecificTypeMapping)
at .BulkInsert[T](DbContext this, IEnumerable`1 entities, Action`1 options, Boolean isBulkSaveChanges)
at DbContextExtensions.BulkInsert[T](DbContext this, IEnumerable`1 entities, Action`1 options)
at DbSetExtensions.BulkInsert[T](DbSet`1 this, IEnumerable`1 entities, Action`1 options)
Fiddle or Project (Optional)
The Merge Request being fired by ZZZ is:
DECLARE @30fbc0b87a124b17abe2a6150827940f INT;
MERGE INTO [data].[Messages] AS DestinationTable
USING
(
SELECT TOP 100 PERCENT *, ROW_NUMBER() OVER ( PARTITION BY [Device_ID],[Timestamp],[TextHash] ORDER BY ZZZ_Index DESC ) AS ZZZ_Row_Number FROM
(SELECT TOP 100 PERCENT * FROM ZZZProjects_18f8e5fb_7eb6_40cf_9190_496b1a475d4d
WHERE ZZZ_Index >= 0 AND ZZZ_Index <= 321 ORDER BY ZZZ_Index) AS StagingTable ORDER BY ZZZ_Index
) AS StagingTable
ON (DestinationTable.[Device_ID] = StagingTable.[Device_ID] OR (DestinationTable.[Device_ID] IS NULL AND StagingTable.[Device_ID] IS NULL)) AND (DestinationTable.[Timestamp] = StagingTable.[Timestamp] OR (DestinationTable.[Timestamp] IS NULL AND StagingTable.[Timestamp] IS NULL)) AND (DestinationTable.[TextHash] = StagingTable.[TextHash] OR (DestinationTable.[TextHash] IS NULL AND StagingTable.[TextHash] IS NULL))
WHEN MATCHED THEN
UPDATE SET @30fbc0b87a124b17abe2a6150827940f = NULL
WHEN NOT MATCHED AND (ZZZ_Row_Number = 1 OR (
StagingTable.[Device_ID] IS NULL AND StagingTable.[Timestamp] IS NULL AND StagingTable.[TextHash] IS NULL
))
THEN
INSERT ( [Device_ID], [IsComing], [MessageCode_ID], [MessageSender_ID], [Parameters], [TextHash], [Timestamp] )
VALUES ( [Device_ID], [IsComing], [MessageCode_ID], [MessageSender_ID], [Parameters], [TextHash], [Timestamp] )
OUTPUT
$action,
StagingTable.ZZZ_Index,
INSERTED.[Message_ID] AS [Message_ID_zzzinserted];
Further technical details
- EF version: EF Core 5
- EF Extensions version: latest
- Database Provider: SQL Server
I tracked the Merge statement which is created. You can see that the "WHEN NOT MATCHED"-Part correctly filters the Duplicates by RowNum. But the "WHEN MATCHED"-Part is not, so the duplicates match, hence the error provided by SQL-Server.
Is there an option that I'm missing to deactivate the MATCHED-Part. I think the Matched-Part should look like this:
WHEN MATCHED AND (ZZZ_Row_Number = 1) THEN
UPDATE SET @30fbc0b87a124b17abe2a6150827940f = NULL
What do you think?
Hello @NicoKno ,
We indeed have an error. It looks like you have no column to update (which is why we use a kind of PlaceHolder logic since we still need to return what has been "updated"), and when it happens we don't support the AllowDuplicateKeys logic.
So yes, you are right, the "ZZZ_Row_Number" should be found here.
My developer will look at it.
Best Regards,
Jon
Hello @JonathanMagnan ,
I think I found a workaround. I'm now setting "AutoMapOutputDirection" to false. This strips the "MATCHED" part and "OUTPUT" completely out of the MERGE statement. Which in my case is what I want I guess. I don't need the IDs returned from the BulkInsert in this case. I also think that all this is only happening because I use the BulkInsert in a "IncludeGraph" context. But the entity is the last leaf in then graph so it doesn't really need to be tracked further.
I'm interested in what your developer comes up with. But no hurries, it's working for me now.
Hello @NicoKno ,
My developer thinks you currently have the option ForceOutputFromUnmodifiedRow = true enabled.
Is he right?
If that's the case, it would explain everything.
Since you force to output unmodified row, we need to output all values which include rows that are included more than once.
In that case, we cannot set the WHEN MATCHED AND (ZZZ_Row_Number = 1) THEN in the UPDATE part, since otherwise, we will miss some row that would have been updated.
In this case, no rows are updated since there is no value to update but we still need to output it due to the ForceOutputFromUnmodifiedRow options.
Do it makes sense? I believe the first thing to confirm would be if you really have or not the option ForceOutputFromUnmodifiedRow enabled.
Best Regards,
Jon
Hi @JonathanMagnan,
this is my code:
dataContext.Messages.BulkInsert(newRecords,
options =>
{
options.IncludeGraph = true;
options.IncludeGraphOperationBuilder = operation =>
{
if(operation is BulkOperation<MessageSender> senderOperation)
{
senderOperation.ColumnPrimaryKeyExpression = sender => new {sender.SenderName};
senderOperation.AllowDuplicateKeys = true;
senderOperation.InsertIfNotExists = true;
senderOperation.ForceOutputFromUnmodifiedRow = true;
}
if(operation is BulkOperation<Message> messageOperation)
{
messageOperation.ColumnPrimaryKeyExpression = message => new
{
message.ID,
message.Timestamp,
message.TextHash
};
messageOperation.AllowDuplicateKeys = true;
messageOperation.InsertIfNotExists = true;
messageOperation.AutoMapOutputDirection = false;
}
};
});
As you can see I have the "ForceOutputFromUnmodifiedRow" enabled. But only for one of my entity operations "MessageSender", which is wanted in this case. The problem occurs for the other entity "Message", where "ForceOutputFromUnmodifiedRow" is not enabled.
You can see my workaround where I set "AutoMapOutputDirection" to false.
I will try and set ForceOutputFromUnmodifiedRow = false for the Messages to verify if this works too.
Edit: Setting ForceOutputFromUnmodifiedRow = false does not prevent the WHEN MATCHED
Thank you, we will look at it
Perhaps we will find something now we know better how you use it
Hello @NicoKno ,
The v5.1.21 has been released.
You were right, we indeed have an issue with the option ForceOutputFromUnmodifiedRow, the property was always true!
Let me know if everything works in the latest version.
Best Regards,
Jon
Hello @NicoKno ,
Since our last conversation, we haven't heard from you.
Did you get the chance to try the v5.1.21?
Let me know if everything works in the latest version.
Best Regards,
Jon
Sorry for the delay. I checked your new Version. I changed my ForceOutputFromUnmodifiedRow to false for the second operation and it works as expected now.
Thanks again for your time.
Awesome @NicoKno ,
we are glad to hear that the issue is now fixed :)
Don't hesitate to contact us with any questions or further assistance,
Jon