Bug: BulkMergeAsync with multiple qualifiers

[nandaccio]

It seems that BulkMergeAsync does not work as expected in case of multiple qualifiers. See further details below.

At every run the same records are added, even though they are already available (based on the qualifiers):

First run:

Second run:

Third run:

I am using the following call to populate the table:

merged = await database.Do.BulkMergeAsync(transactions.ToList(), qualifiers: p => new { p.TransactionID, p.TransactionDate, p.AccountId, p.TransactionTypeID, p.Attribute1, p.Attribute2, p.Attribute3, p.Attribute4 }, hints: SqlServerTableHints.TabLock, bulkCopyTimeout: 0).ConfigureAwait(false);

where transactions is a List (coming from HashSet with a custom comparer, see below):

public class TransactionRaw
    {
        public long Id { get; set; }

        public long TransactionID { get; set; }

        public DateTime TransactionDate { get; set; }

        public DateTime IncidentCreationDate { get; set; }

        public int? AccountId { get; set; }

        public long? ContactID { get; set; }

        public int? SeverityID { get; set; }

        public int? InterfaceID { get; set; }

        public int? DispositionID { get; set; }

        public long? Attribute1 { get; set; }

        public long? Attribute2 { get; set; }

        public long? Attribute3 { get; set; }

        public long? Attribute4 { get; set; }

        public int? QueueID { get; set; }

        public int? TransactionTypeID { get; set; }

        public int? SourceLevel1ID { get; set; }

        public int? SourceLevel2ID { get; set; }

        public int? ProductID { get; set; }

        public int? CountryID { get; set; }

        public int? CustomerTypeID { get; set; }

        public int? CategoryID { get; set; }

        public int? StatusID { get; set; }

        public string PhoneCallUUID { get; set; }

        public int? ChannelID { get; set; }
    }

In order to avoid duplicates, the hashset uses this custom comparer:

    public class TransactionComparer : IEqualityComparer<TransactionRaw>
    {
        public bool Equals(TransactionRaw t1, TransactionRaw t2)
        {
            return (t1.TransactionID == t2.TransactionID) && (t1.TransactionDate == t2.TransactionDate) && (t1.Attribute1 == t2.Attribute1) && (t1.Attribute2 == t2.Attribute2) && (t1.Attribute3 == t2.Attribute3) && (t1.Attribute4 == t2.Attribute4) && (t1.AccountId == t2.AccountId);
        }

        public int GetHashCode(TransactionRaw t)
        {
            return $"{t.TransactionID}{t.TransactionDate}{t.AccountId}{t.Attribute1}{t.Attribute2}{t.Attribute3}{t.Attribute4}".GetHashCode();
        }
    }

the schema contains the following table:

CREATE TABLE [TransactionsRaw](
	[Id] [bigint] IDENTITY(1,1) NOT NULL,
	[TransactionID] [bigint] NOT NULL,
	[TransactionDate] [datetime] NOT NULL,
	[AccountId] [int] NULL,
	[ProductID] [int] NULL,
	[Attribute1] [bigint] NULL,
	[Attribute2] [bigint] NULL,
	[Attribute3] [bigint] NULL,
	[Attribute4] [bigint] NULL,
	[PhoneCallUUID] [varchar](64) NULL,
	[InterfaceID] [int] NULL,
	[CategoryID] [int] NULL,
	[ContactID] [int] NULL,
	[CountryID] [int] NULL,
	[CustomerTypeID] [int] NULL,
	[QueueID] [int] NULL,
	[IncidentCreationDate] [datetime] NOT NULL,
	[StatusID] [int] NULL,
	[SeverityID] [int] NULL,
	[SourceLevel1ID] [int] NULL,
	[SourceLevel2ID] [int] NULL,
	[DispositionID] [int] NULL,
	[TransactionTypeID] [int] NULL,
	[ChannelID] [int] NULL
) ON [PRIMARY]

Library Version:

RepoDb.1.12.7
RepoDb.MySql.1.1.4
RepoDb.SqlServer.1.1.3
RepoDb.SqlServer.BulkOperations.1.1.4

[mikependon]

I am shock on the number of qualifiers to only add/update such single TransationTypeId column. 😄 We will test this and will get back to you.

Can you try check if this is as well failing on the MergeAll operation? There, you can also inject an ITrace object to see the actual SQL Statement. See the documentation link here.

[mikependon]

While on my way back, I just realized that you use a NULLABLE column as part of the qualifiers. Though, this is technicaly possible, but it is highly not recommend to use it, and if it is the use-case is very low in ratio.

RepoDB is using an explicit equality '=' in which would always return false to 'NULL = NULL'.

I would say, this is a valid bug even though we have not yet investigated it.

[mikependon]

Link to the code:

RepoDB/RepoDb.Extensions/RepoDb.SqlServer.BulkOperations/RepoDb.SqlServer.BulkOperations/SqlConnectionExtension.cs

Line 680 in 57e44b0

field => field.AsJoinQualifier("S", "T", dbSetting))

[nandaccio]

I am shock on the number of qualifiers to only add/update such single TransationTypeId column. 😄 We will test this and will get back to you.

Actually the number of identifiers is needed to identify a unique transaction, because I have to insert them only once. Source data does not expose a unique identifier for each transaction, so it's built on my side as a combination of a "minimum" amount of fields that makes the transaction unique.

Can you try check if this is as well failing on the MergeAll operation? There, you can also inject an ITrace object to see the actual SQL Statement. See the documentation link here.

I am going to test also the MergeAll operation, with an ITrace object as well.

[nandaccio]

I see that you already identified where things go wrong, thank you. :-)

This is the SQL statement generated by MergeAll and I confirm that also in this case there is a problem with (not) unique values:

MERGE [dbo].[TransactionsRaw] WITH (TABLOCK) AS T USING ( SELECT @Id AS [Id], @TransactionID AS [TransactionID], @TransactionDate AS [TransactionDate], @IncidentCreationDate AS [IncidentCreationDate], @AccountId AS [AccountId], @ContactID AS [ContactID], @SeverityID AS [SeverityID], @InterfaceID AS [InterfaceID], @DispositionID AS [DispositionID], @Attribute1 AS [Attribute1], @Attribute2 AS [Attribute2], @Attribute3 AS [Attribute3], @Attribute4 AS [Attribute4], @QueueID AS [QueueID], @TransactionTypeID AS [TransactionTypeID], @SourceLevel1ID AS [SourceLevel1ID], @SourceLevel2ID AS [SourceLevel2ID], @ProductID AS [ProductID], @CountryID AS [CountryID], @CustomerTypeID AS [CustomerTypeID], @CategoryID AS [CategoryID], @StatusID AS [StatusID], @PhoneCallUUID AS [PhoneCallUUID], @ChannelID AS [ChannelID] ) AS S ON ( S.[TransactionID] = T.[TransactionID] AND S.[TransactionDate] = T.[TransactionDate] AND S.[AccountId] = T.[AccountId] AND S.[Attribute1] = T.[Attribute1] AND S.[Attribute2] = T.[Attribute2] AND S.[Attribute3] = T.[Attribute3] AND S.[Attribute4] = T.[Attribute4] AND S.[TransactionTypeID] = T.[TransactionTypeID] ) WHEN NOT MATCHED THEN INSERT ( [TransactionID], [TransactionDate], [IncidentCreationDate], [AccountId], [ContactID], [SeverityID], [InterfaceID], [DispositionID], [Attribute1], [Attribute2], [Attribute3], [Attribute4], [QueueID], [TransactionTypeID], [SourceLevel1ID], [SourceLevel2ID], [ProductID], [CountryID], [CustomerTypeID], [CategoryID], [StatusID], [PhoneCallUUID], [ChannelID] ) VALUES ( S.[TransactionID], S.[TransactionDate], S.[IncidentCreationDate], S.[AccountId], S.[ContactID], S.[SeverityID], S.[InterfaceID], S.[DispositionID], S.[Attribute1], S.[Attribute2], S.[Attribute3], S.[Attribute4], S.[QueueID], S.[TransactionTypeID], S.[SourceLevel1ID], S.[SourceLevel2ID], S.[ProductID], S.[CountryID], S.[CustomerTypeID], S.[CategoryID], S.[StatusID], S.[PhoneCallUUID], S.[ChannelID] ) WHEN MATCHED THEN UPDATE SET T.[TransactionID] = S.[TransactionID], T.[TransactionDate] = S.[TransactionDate], T.[IncidentCreationDate] = S.[IncidentCreationDate], T.[AccountId] = S.[AccountId], T.[ContactID] = S.[ContactID], T.[SeverityID] = S.[SeverityID], T.[InterfaceID] = S.[InterfaceID], T.[DispositionID] = S.[DispositionID], T.[Attribute1] = S.[Attribute1], T.[Attribute2] = S.[Attribute2], T.[Attribute3] = S.[Attribute3], T.[Attribute4] = S.[Attribute4], T.[QueueID] = S.[QueueID], T.[TransactionTypeID] = S.[TransactionTypeID], T.[SourceLevel1ID] = S.[SourceLevel1ID], T.[SourceLevel2ID] = S.[SourceLevel2ID], T.[ProductID] = S.[ProductID], T.[CountryID] = S.[CountryID], T.[CustomerTypeID] = S.[CustomerTypeID], T.[CategoryID] = S.[CategoryID], T.[StatusID] = S.[StatusID], T.[PhoneCallUUID] = S.[PhoneCallUUID], T.[ChannelID] = S.[ChannelID] OUTPUT INSERTED.[Id] AS [Id], @__RepoDb_OrderColumn_0 AS [OrderColumn] ; MERGE [dbo].[TransactionsRaw] WITH (TABLOCK) AS T USING ( SELECT @Id_1 AS [Id], @TransactionID_1 AS [TransactionID], @TransactionDate_1 AS [TransactionDate], @IncidentCreationDate_1 AS [IncidentCreationDate], @AccountId_1 AS [AccountId], @ContactID_1 AS [ContactID], @SeverityID_1 AS [SeverityID], @InterfaceID_1 AS [InterfaceID], @DispositionID_1 AS [DispositionID], @Attribute1_1 AS [Attribute1], @Attribute2_1 AS [Attribute2], @Attribute3_1 AS [Attribute3], @Attribute4_1 AS [Attribute4], @QueueID_1 AS [QueueID], @TransactionTypeID_1 AS [TransactionTypeID], @SourceLevel1ID_1 AS [SourceLevel1ID], @SourceLevel2ID_1 AS [SourceLevel2ID], @ProductID_1 AS [ProductID], @CountryID_1 AS [CountryID], @CustomerTypeID_1 AS [CustomerTypeID], @CategoryID_1 AS [CategoryID], @StatusID_1 AS [StatusID], @PhoneCallUUID_1 AS [PhoneCallUUID], @ChannelID_1 AS [ChannelID] ) AS S ON ( S.[TransactionID] = T.[TransactionID] AND S.[TransactionDate] = T.[TransactionDate] AND S.[AccountId] = T.[AccountId] AND S.[Attribute1] = T.[Attribute1] AND S.[Attribute2] = T.[Attribute2] AND S.[Attribute3] = T.[Attribute3] AND S.[Attribute4] = T.[Attribute4] AND S.[TransactionTypeID] = T.[TransactionTypeID] ) WHEN NOT MATCHED THEN INSERT ( [TransactionID], [TransactionDate], [IncidentCreationDate], [AccountId], [ContactID], [SeverityID], [InterfaceID], [DispositionID], [Attribute1], [Attribute2], [Attribute3], [Attribute4], [QueueID], [TransactionTypeID], [SourceLevel1ID], [SourceLevel2ID], [ProductID], [CountryID], [CustomerTypeID], [CategoryID], [StatusID], [PhoneCallUUID], [ChannelID] ) VALUES ( S.[TransactionID], S.[TransactionDate], S.[IncidentCreationDate], S.[AccountId], S.[ContactID], S.[SeverityID], S.[InterfaceID], S.[DispositionID], S.[Attribute1], S.[Attribute2], S.[Attribute3], S.[Attribute4], S.[QueueID], S.[TransactionTypeID], S.[SourceLevel1ID], S.[SourceLevel2ID], S.[ProductID], S.[CountryID], S.[CustomerTypeID], S.[CategoryID], S.[StatusID], S.[PhoneCallUUID], S.[ChannelID] ) WHEN MATCHED THEN UPDATE SET T.[TransactionID] = S.[TransactionID], T.[TransactionDate] = S.[TransactionDate], T.[IncidentCreationDate] = S.[IncidentCreationDate], T.[AccountId] = S.[AccountId], T.[ContactID] = S.[ContactID], T.[SeverityID] = S.[SeverityID], T.[InterfaceID] = S.[InterfaceID], T.[DispositionID] = S.[DispositionID], T.[Attribute1] = S.[Attribute1], T.[Attribute2] = S.[Attribute2], T.[Attribute3] = S.[Attribute3], T.[Attribute4] = S.[Attribute4], T.[QueueID] = S.[QueueID], T.[TransactionTypeID] = S.[TransactionTypeID], T.[SourceLevel1ID] = S.[SourceLevel1ID], T.[SourceLevel2ID] = S.[SourceLevel2ID], T.[ProductID] = S.[ProductID], T.[CountryID] = S.[CountryID], T.[CustomerTypeID] = S.[CustomerTypeID], T.[CategoryID] = S.[CategoryID], T.[StatusID] = S.[StatusID], T.[PhoneCallUUID] = S.[PhoneCallUUID], T.[ChannelID] = S.[ChannelID] OUTPUT INSERTED.[Id] AS [Id], @__RepoDb_OrderColumn_1 AS [OrderColumn] ; MERGE [dbo].[TransactionsRaw] WITH (TABLOCK) AS T USING ( SELECT @Id_2 AS [Id], @TransactionID_2 AS [TransactionID], @TransactionDate_2 AS [TransactionDate], @IncidentCreationDate_2 AS [IncidentCreationDate], @AccountId_2 AS [AccountId], @ContactID_2 AS [ContactID], @SeverityID_2 AS [SeverityID], @InterfaceID_2 AS [InterfaceID], @DispositionID_2 AS [DispositionID], @Attribute1_2 AS [Attribute1], @Attribute2_2 AS [Attribute2], @Attribute3_2 AS [Attribute3], @Attribute4_2 AS [Attribute4], @QueueID_2 AS [QueueID], @TransactionTypeID_2 AS [TransactionTypeID], @SourceLevel1ID_2 AS [SourceLevel1ID], @SourceLevel2ID_2 AS [SourceLevel2ID], @ProductID_2 AS [ProductID], @CountryID_2 AS [CountryID], @CustomerTypeID_2 AS [CustomerTypeID], @CategoryID_2 AS [CategoryID], @StatusID_2 AS [StatusID], @PhoneCallUUID_2 AS [PhoneCallUUID], @ChannelID_2 AS [ChannelID] ) AS S ON ( S.[TransactionID] = T.[TransactionID] AND S.[TransactionDate] = T.[TransactionDate] AND S.[AccountId] = T.[AccountId] AND S.[Attribute1] = T.[Attribute1] AND S.[Attribute2] = T.[Attribute2] AND S.[Attribute3] = T.[Attribute3] AND S.[Attribute4] = T.[Attribute4] AND S.[TransactionTypeID] = T.[TransactionTypeID] ) WHEN NOT MATCHED THEN INSERT ( [TransactionID], [TransactionDate], [IncidentCreationDate], [AccountId], [ContactID], [SeverityID], [InterfaceID], [DispositionID], [Attribute1], [Attribute2], [Attribute3], [Attribute4], [QueueID], [TransactionTypeID], [SourceLevel1ID], [SourceLevel2ID], [ProductID], [CountryID], [CustomerTypeID], [CategoryID], [StatusID], [PhoneCallUUID], [ChannelID] ) VALUES ( S.[TransactionID], S.[TransactionDate], S.[IncidentCreationDate], S.[AccountId], S.[ContactID], S.[SeverityID], S.[InterfaceID], S.[DispositionID], S.[Attribute1], S.[Attribute2], S.[Attribute3], S.[Attribute4], S.[QueueID], S.[TransactionTypeID], S.[SourceLevel1ID], S.[SourceLevel2ID], S.[ProductID], S.[CountryID], S.[CustomerTypeID], S.[CategoryID], S.[StatusID], S.[PhoneCallUUID], S.[ChannelID] ) WHEN MATCHED THEN UPDATE SET T.[TransactionID] = S.[TransactionID], T.[TransactionDate] = S.[TransactionDate], T.[IncidentCreationDate] = S.[IncidentCreationDate], T.[AccountId] = S.[AccountId], T.[ContactID] = S.[ContactID], T.[SeverityID] = S.[SeverityID], T.[InterfaceID] = S.[InterfaceID], T.[DispositionID] = S.[DispositionID], T.[Attribute1] = S.[Attribute1], T.[Attribute2] = S.[Attribute2], T.[Attribute3] = S.[Attribute3], T.[Attribute4] = S.[Attribute4], T.[QueueID] = S.[QueueID], T.[TransactionTypeID] = S.[TransactionTypeID], T.[SourceLevel1ID] = S.[SourceLevel1ID], T.[SourceLevel2ID] = S.[SourceLevel2ID], T.[ProductID] = S.[ProductID], T.[CountryID] = S.[CountryID], T.[CustomerTypeID] = S.[CustomerTypeID], T.[CategoryID] = S.[CategoryID], T.[StatusID] = S.[StatusID], T.[PhoneCallUUID] = S.[PhoneCallUUID], T.[ChannelID] = S.[ChannelID] OUTPUT INSERTED.[Id] AS [Id], @__RepoDb_OrderColumn_2 AS [OrderColumn] ; MERGE [dbo].[TransactionsRaw] WITH (TABLOCK) AS T USING ( SELECT @Id_3 AS [Id], @TransactionID_3 AS [TransactionID], @TransactionDate_3 AS [TransactionDate], @IncidentCreationDate_3 AS [IncidentCreationDate], @AccountId_3 AS [AccountId], @ContactID_3 AS [ContactID], @SeverityID_3 AS [SeverityID], @InterfaceID_3 AS [InterfaceID], @DispositionID_3 AS [DispositionID], @Attribute1_3 AS [Attribute1], @Attribute2_3 AS [Attribute2], @Attribute3_3 AS [Attribute3], @Attribute4_3 AS [Attribute4], @QueueID_3 AS [QueueID], @TransactionTypeID_3 AS [TransactionTypeID], @SourceLevel1ID_3 AS [SourceLevel1ID], @SourceLevel2ID_3 AS [SourceLevel2ID], @ProductID_3 AS [ProductID], @CountryID_3 AS [CountryID], @CustomerTypeID_3 AS [CustomerTypeID], @CategoryID_3 AS [CategoryID], @StatusID_3 AS [StatusID], @PhoneCallUUID_3 AS [PhoneCallUUID], @ChannelID_3 AS [ChannelID] ) AS S ON ( S.[TransactionID] = T.[TransactionID] AND S.[TransactionDate] = T.[TransactionDate] AND S.[AccountId] = T.[AccountId] AND S.[Attribute1] = T.[Attribute1] AND S.[Attribute2] = T.[Attribute2] AND S.[Attribute3] = T.[Attribute3] AND S.[Attribute4] = T.[Attribute4] AND S.[TransactionTypeID] = T.[TransactionTypeID] ) WHEN NOT MATCHED THEN INSERT ( [TransactionID], [TransactionDate], [IncidentCreationDate], [AccountId], [ContactID], [SeverityID], [InterfaceID], [DispositionID], [Attribute1], [Attribute2], [Attribute3], [Attribute4], [QueueID], [TransactionTypeID], [SourceLevel1ID], [SourceLevel2ID], [ProductID], [CountryID], [CustomerTypeID], [CategoryID], [StatusID], [PhoneCallUUID], [ChannelID] ) VALUES ( S.[TransactionID], S.[TransactionDate], S.[IncidentCreationDate], S.[AccountId], S.[ContactID], S.[SeverityID], S.[InterfaceID], S.[DispositionID], S.[Attribute1], S.[Attribute2], S.[Attribute3], S.[Attribute4], S.[QueueID], S.[TransactionTypeID], S.[SourceLevel1ID], S.[SourceLevel2ID], S.[ProductID], S.[CountryID], S.[CustomerTypeID], S.[CategoryID], S.[StatusID], S.[PhoneCallUUID], S.[ChannelID] ) WHEN MATCHED THEN UPDATE SET T.[TransactionID] = S.[TransactionID], T.[TransactionDate] = S.[TransactionDate], T.[IncidentCreationDate] = S.[IncidentCreationDate], T.[AccountId] = S.[AccountId], T.[ContactID] = S.[ContactID], T.[SeverityID] = S.[SeverityID], T.[InterfaceID] = S.[InterfaceID], T.[DispositionID] = S.[DispositionID], T.[Attribute1] = S.[Attribute1], T.[Attribute2] = S.[Attribute2], T.[Attribute3] = S.[Attribute3], T.[Attribute4] = S.[Attribute4], T.[QueueID] = S.[QueueID], T.[TransactionTypeID] = S.[TransactionTypeID], T.[SourceLevel1ID] = S.[SourceLevel1ID], T.[SourceLevel2ID] = S.[SourceLevel2ID], T.[ProductID] = S.[ProductID], T.[CountryID] = S.[CountryID], T.[CustomerTypeID] = S.[CustomerTypeID], T.[CategoryID] = S.[CategoryID], T.[StatusID] = S.[StatusID], T.[PhoneCallUUID] = S.[PhoneCallUUID], T.[ChannelID] = S.[ChannelID] OUTPUT INSERTED.[Id] AS [Id], @__RepoDb_OrderColumn_3 AS [OrderColumn] ; MERGE [dbo].[TransactionsRaw] WITH (TABLOCK) AS T USING ( SELECT @Id_4 AS [Id], @TransactionID_4 AS [TransactionID], @TransactionDate_4 AS [TransactionDate], @IncidentCreationDate_4 AS [IncidentCreationDate], @AccountId_4 AS [AccountId], @ContactID_4 AS [ContactID], @SeverityID_4 AS [SeverityID], @InterfaceID_4 AS [InterfaceID], @DispositionID_4 AS [DispositionID], @Attribute1_4 AS [Attribute1], @Attribute2_4 AS [Attribute2], @Attribute3_4 AS [Attribute3], @Attribute4_4 AS [Attribute4], @QueueID_4 AS [QueueID], @TransactionTypeID_4 AS [TransactionTypeID], @SourceLevel1ID_4 AS [SourceLevel1ID], @SourceLevel2ID_4 AS [SourceLevel2ID], @ProductID_4 AS [ProductID], @CountryID_4 AS [CountryID], @CustomerTypeID_4 AS [CustomerTypeID], @CategoryID_4 AS [CategoryID], @StatusID_4 AS [StatusID], @PhoneCallUUID_4 AS [PhoneCallUUID], @ChannelID_4 AS [ChannelID] ) AS S ON ( S.[TransactionID] = T.[TransactionID] AND S.[TransactionDate] = T.[TransactionDate] AND S.[AccountId] = T.[AccountId] AND S.[Attribute1] = T.[Attribute1] AND S.[Attribute2] = T.[Attribute2] AND S.[Attribute3] = T.[Attribute3] AND S.[Attribute4] = T.[Attribute4] AND S.[TransactionTypeID] = T.[TransactionTypeID] ) WHEN NOT MATCHED THEN INSERT ( [TransactionID], [TransactionDate], [IncidentCreationDate], [AccountId], [ContactID], [SeverityID], [InterfaceID], [DispositionID], [Attribute1], [Attribute2], [Attribute3], [Attribute4], [QueueID], [TransactionTypeID], [SourceLevel1ID], [SourceLevel2ID], [ProductID], [CountryID], [CustomerTypeID], [CategoryID], [StatusID], [PhoneCallUUID], [ChannelID] ) VALUES ( S.[TransactionID], S.[TransactionDate], S.[IncidentCreationDate], S.[AccountId], S.[ContactID], S.[SeverityID], S.[InterfaceID], S.[DispositionID], S.[Attribute1], S.[Attribute2], S.[Attribute3], S.[Attribute4], S.[QueueID], S.[TransactionTypeID], S.[SourceLevel1ID], S.[SourceLevel2ID], S.[ProductID], S.[CountryID], S.[CustomerTypeID], S.[CategoryID], S.[StatusID], S.[PhoneCallUUID], S.[ChannelID] ) WHEN MATCHED THEN UPDATE SET T.[TransactionID] = S.[TransactionID], T.[TransactionDate] = S.[TransactionDate], T.[IncidentCreationDate] = S.[IncidentCreationDate], T.[AccountId] = S.[AccountId], T.[ContactID] = S.[ContactID], T.[SeverityID] = S.[SeverityID], T.[InterfaceID] = S.[InterfaceID], T.[DispositionID] = S.[DispositionID], T.[Attribute1] = S.[Attribute1], T.[Attribute2] = S.[Attribute2], T.[Attribute3] = S.[Attribute3], T.[Attribute4] = S.[Attribute4], T.[QueueID] = S.[QueueID], T.[TransactionTypeID] = S.[TransactionTypeID], T.[SourceLevel1ID] = S.[SourceLevel1ID], T.[SourceLevel2ID] = S.[SourceLevel2ID], T.[ProductID] = S.[ProductID], T.[CountryID] = S.[CountryID], T.[CustomerTypeID] = S.[CustomerTypeID], T.[CategoryID] = S.[CategoryID], T.[StatusID] = S.[StatusID], T.[PhoneCallUUID] = S.[PhoneCallUUID], T.[ChannelID] = S.[ChannelID] OUTPUT INSERTED.[Id] AS [Id], @__RepoDb_OrderColumn_4 AS [OrderColumn] ; MERGE [dbo].[TransactionsRaw] WITH (TABLOCK) AS T USING ( SELECT @Id_5 AS [Id], @TransactionID_5 AS [TransactionID], @TransactionDate_5 AS [TransactionDate], @IncidentCreationDate_5 AS [IncidentCreationDate], @AccountId_5 AS [AccountId], @ContactID_5 AS [ContactID], @SeverityID_5 AS [SeverityID], @InterfaceID_5 AS [InterfaceID], @DispositionID_5 AS [DispositionID], @Attribute1_5 AS [Attribute1], @Attribute2_5 AS [Attribute2], @Attribute3_5 AS [Attribute3], @Attribute4_5 AS [Attribute4], @QueueID_5 AS [QueueID], @TransactionTypeID_5 AS [TransactionTypeID], @SourceLevel1ID_5 AS [SourceLevel1ID], @SourceLevel2ID_5 AS [SourceLevel2ID], @ProductID_5 AS [ProductID], @CountryID_5 AS [CountryID], @CustomerTypeID_5 AS [CustomerTypeID], @CategoryID_5 AS [CategoryID], @StatusID_5 AS [StatusID], @PhoneCallUUID_5 AS [PhoneCallUUID], @ChannelID_5 AS [ChannelID] ) AS S ON ( S.[TransactionID] = T.[TransactionID] AND S.[TransactionDate] = T.[TransactionDate] AND S.[AccountId] = T.[AccountId] AND S.[Attribute1] = T.[Attribute1] AND S.[Attribute2] = T.[Attribute2] AND S.[Attribute3] = T.[Attribute3] AND S.[Attribute4] = T.[Attribute4] AND S.[TransactionTypeID] = T.[TransactionTypeID] ) WHEN NOT MATCHED THEN INSERT ( [TransactionID], [TransactionDate], [IncidentCreationDate], [AccountId], [ContactID], [SeverityID], [InterfaceID], [DispositionID], [Attribute1], [Attribute2], [Attribute3], [Attribute4], [QueueID], [TransactionTypeID], [SourceLevel1ID], [SourceLevel2ID], [ProductID], [CountryID], [CustomerTypeID], [CategoryID], [StatusID], [PhoneCallUUID], [ChannelID] ) VALUES ( S.[TransactionID], S.[TransactionDate], S.[IncidentCreationDate], S.[AccountId], S.[ContactID], S.[SeverityID], S.[InterfaceID], S.[DispositionID], S.[Attribute1], S.[Attribute2], S.[Attribute3], S.[Attribute4], S.[QueueID], S.[TransactionTypeID], S.[SourceLevel1ID], S.[SourceLevel2ID], S.[ProductID], S.[CountryID], S.[CustomerTypeID], S.[CategoryID], S.[StatusID], S.[PhoneCallUUID], S.[ChannelID] ) WHEN MATCHED THEN UPDATE SET T.[TransactionID] = S.[TransactionID], T.[TransactionDate] = S.[TransactionDate], T.[IncidentCreationDate] = S.[IncidentCreationDate], T.[AccountId] = S.[AccountId], T.[ContactID] = S.[ContactID], T.[SeverityID] = S.[SeverityID], T.[InterfaceID] = S.[InterfaceID], T.[DispositionID] = S.[DispositionID], T.[Attribute1] = S.[Attribute1], T.[Attribute2] = S.[Attribute2], T.[Attribute3] = S.[Attribute3], T.[Attribute4] = S.[Attribute4], T.[QueueID] = S.[QueueID], T.[TransactionTypeID] = S.[TransactionTypeID], T.[SourceLevel1ID] = S.[SourceLevel1ID], T.[SourceLevel2ID] = S.[SourceLevel2ID], T.[ProductID] = S.[ProductID], T.[CountryID] = S.[CountryID], T.[CustomerTypeID] = S.[CustomerTypeID], T.[CategoryID] = S.[CategoryID], T.[StatusID] = S.[StatusID], T.[PhoneCallUUID] = S.[PhoneCallUUID], T.[ChannelID] = S.[ChannelID] OUTPUT INSERTED.[Id] AS [Id], @__RepoDb_OrderColumn_5 AS [OrderColumn] ; MERGE [dbo].[TransactionsRaw] WITH (TABLOCK) AS T USING ( SELECT @Id_6 AS [Id], @TransactionID_6 AS [TransactionID], @TransactionDate_6 AS [TransactionDate], @IncidentCreationDate_6 AS [IncidentCreationDate], @AccountId_6 AS [AccountId], @ContactID_6 AS [ContactID], @SeverityID_6 AS [SeverityID], @InterfaceID_6 AS [InterfaceID], @DispositionID_6 AS [DispositionID], @Attribute1_6 AS [Attribute1], @Attribute2_6 AS [Attribute2], @Attribute3_6 AS [Attribute3], @Attribute4_6 AS [Attribute4], @QueueID_6 AS [QueueID], @TransactionTypeID_6 AS [TransactionTypeID], @SourceLevel1ID_6 AS [SourceLevel1ID], @SourceLevel2ID_6 AS [SourceLevel2ID], @ProductID_6 AS [ProductID], @CountryID_6 AS [CountryID], @CustomerTypeID_6 AS [CustomerTypeID], @CategoryID_6 AS [CategoryID], @StatusID_6 AS [StatusID], @PhoneCallUUID_6 AS [PhoneCallUUID], @ChannelID_6 AS [ChannelID] ) AS S ON ( S.[TransactionID] = T.[TransactionID] AND S.[TransactionDate] = T.[TransactionDate] AND S.[AccountId] = T.[AccountId] AND S.[Attribute1] = T.[Attribute1] AND S.[Attribute2] = T.[Attribute2] AND S.[Attribute3] = T.[Attribute3] AND S.[Attribute4] = T.[Attribute4] AND S.[TransactionTypeID] = T.[TransactionTypeID] ) WHEN NOT MATCHED THEN INSERT ( [TransactionID], [TransactionDate], [IncidentCreationDate], [AccountId], [ContactID], [SeverityID], [InterfaceID], [DispositionID], [Attribute1], [Attribute2], [Attribute3], [Attribute4], [QueueID], [TransactionTypeID], [SourceLevel1ID], [SourceLevel2ID], [ProductID], [CountryID], [CustomerTypeID], [CategoryID], [StatusID], [PhoneCallUUID], [ChannelID] ) VALUES ( S.[TransactionID], S.[TransactionDate], S.[IncidentCreationDate], S.[AccountId], S.[ContactID], S.[SeverityID], S.[InterfaceID], S.[DispositionID], S.[Attribute1], S.[Attribute2], S.[Attribute3], S.[Attribute4], S.[QueueID], S.[TransactionTypeID], S.[SourceLevel1ID], S.[SourceLevel2ID], S.[ProductID], S.[CountryID], S.[CustomerTypeID], S.[CategoryID], S.[StatusID], S.[PhoneCallUUID], S.[ChannelID] ) WHEN MATCHED THEN UPDATE SET T.[TransactionID] = S.[TransactionID], T.[TransactionDate] = S.[TransactionDate], T.[IncidentCreationDate] = S.[IncidentCreationDate], T.[AccountId] = S.[AccountId], T.[ContactID] = S.[ContactID], T.[SeverityID] = S.[SeverityID], T.[InterfaceID] = S.[InterfaceID], T.[DispositionID] = S.[DispositionID], T.[Attribute1] = S.[Attribute1], T.[Attribute2] = S.[Attribute2], T.[Attribute3] = S.[Attribute3], T.[Attribute4] = S.[Attribute4], T.[QueueID] = S.[QueueID], T.[TransactionTypeID] = S.[TransactionTypeID], T.[SourceLevel1ID] = S.[SourceLevel1ID], T.[SourceLevel2ID] = S.[SourceLevel2ID], T.[ProductID] = S.[ProductID], T.[CountryID] = S.[CountryID], T.[CustomerTypeID] = S.[CustomerTypeID], T.[CategoryID] = S.[CategoryID], T.[StatusID] = S.[StatusID], T.[PhoneCallUUID] = S.[PhoneCallUUID], T.[ChannelID] = S.[ChannelID] OUTPUT INSERTED.[Id] AS [Id], @__RepoDb_OrderColumn_6 AS [OrderColumn] ;```

[mikependon]

I will issue you a hot fix beta only for the bulk operations (BulkUpdate). The fixes for the MergeAll operation will be a part of the next release. Is that ok?

[nandaccio]

I will issue you a hot fix beta only for the bulk operations (BulkUpdate). The fixes for the MergeAll operation will be a part of the next release. Is that ok?

If the hot-fix will work with BulkMerge/BulkMergeAsync then it's totally fine!
MergeAll can wait :)

Thank you!

[mikependon]

The fix is now available at RepoDb.SqlServer.BulkOperations v1.1.5-beta1.

[nandaccio]

I confirm that the fix is working! Thank you again.

[mikependon]

Thanks for confirming. 😄