2

I want to merge a list of local entities into a table (if they don't exist already) using linq2db.

I try to do the following:

var recordsConvertedToOrders = reportRecords.Select(i =>
{
    // some predefined conversion
    var order = mapper.Map<Order>(i);
    order.Created = DateTime.Now;
    return order;
});


using (var db = new DataConnection("StuckOrders"))
{
    var count = await db.GetTable<Order>()
        .Merge()
        .Using(recordsConvertedToOrders)
        .On((t, s) => t.OrderlineID == s.OrderlineID)
        .InsertWhenNotMatched()
        .MergeAsync();
}

which gives me this exception:

Unhandled Exception: System.Data.SqlClient.SqlException: 
Violation of PRIMARY KEY constraint 'PK_Order'. 
Cannot insert duplicate key in object 'dbo.Order'. 
The duplicate key value is (0).
The statement has been terminated.

It happens because of the following SQL being generated.

(notice 0's passed for Id and then inserted with INSERT into the table.)

-- StuckOrders SqlServer.2012 (asynchronously)
SET IDENTITY_INSERT [dbo].[Order] ON
MERGE INTO [dbo].[Order] [Target]
USING
(
        VALUES
        (0,9369660,13869511,N'BP1328147327',NULL,NULL,NULL,'2017-11-08T23:08:54','2017-11-08','2017-11-08T23:08:00',18302384,N'LinkedProductToProductContent',NULL,'2017-11-16T09:24:14.300',0,0,0,0,0,0,0,N'3. orderitem (FFApi) not in FulfilmentOrderlines',15829191,N'Windows Editor',0,NULL,NULL,'2017-11-16T13:28:10.866',NULL),
        (0,9308332,13792138,N'NO1320410131',NULL,NULL,NULL,'2017-11-04T22:58:49','2017-11-04','2017-11-04T22:57:00',18193864,N'FailedFinalizerJob',NULL,'2017-11-16T09:24:14.300',0,0,0,0,0,0,0,N'1. orderitem not in fulfilmentflow',15745587,N'Prints Editor',0,NULL,NULL,'2017-11-16T13:28:10.896',NULL)
) [Source] ([c0], [c1], [c2], [c3], [c4], [c5], [c6], [c7], [c8], [c9], [c10], [c11], [c12], [c13], [c14], [c15], [c16], [c17], [c18], [c19], [c20], [c21], [c22], [c23], [c24], [c25], [c26], [c27], [c28])
ON ([Target].[OrderlineID] = [Source].[c10])

WHEN NOT MATCHED THEN
INSERT
(
        [Id],
        [OrderId],
        [OrderItemId],
        [OrderItemCode],
        [OrderCode],
        [CopyItemCode],
        [ProductionTicketID],
        [OrderDateTime],
        [OrderDate],
        [UploadEndDateTime],
        [OrderlineID],
        [ProductContentStatusName],
        [ProductContentStatusDate],
        [Date],
        [check_notIn_FulfillmentFlow],
        [check_notIn_PlantFromOrderDomain],
        [check_notIn_Fulfillment],
        [check_notIn_PlantFromFulfillment],
        [check_Vouchers],
        [check_ExternalVendors],
        [check_notIn_Waiting],
        [CheckDescription],
        [ProductContentID],
        [EditorName],
        [Resolved],
        [FixOrdersJira],
        [RootCauseJira],
        [Created],
        [Updated]
)
VALUES
(
        [Source].[c0],
        [Source].[c1],
        [Source].[c2],
        [Source].[c3],
        [Source].[c4],
        [Source].[c5],
        [Source].[c6],
        [Source].[c7],
        [Source].[c8],
        [Source].[c9],
        [Source].[c10],
        [Source].[c11],
        [Source].[c12],
        [Source].[c13],
        [Source].[c14],
        [Source].[c15],
        [Source].[c16],
        [Source].[c17],
        [Source].[c18],
        [Source].[c19],
        [Source].[c20],
        [Source].[c21],
        [Source].[c22],
        [Source].[c23],
        [Source].[c24],
        [Source].[c25],
        [Source].[c26],
        [Source].[c27],
        [Source].[c28]
)
;
SET IDENTITY_INSERT [dbo].[Order] OFF

Here is my Order model generated by linq2db:

[Table(Schema="dbo", Name="Order")]
public partial class Order
{
    [Column(), PrimaryKey,  Identity] public long Id { get; set; } // bigint
    ...

I want Ids to be skipped in the MERGE SQL so that they can be generated as a normal incremental Identity value.

I ended up specifying the fields to insert by hand (which I don't like because it duplicates our AutoMapper mapping):

using (var db = new DataConnection("StuckOrders"))
{
    var count = await db.GetTable<Order>()
        .Merge()
        .Using(reportRecords)
        .On((t, s) => t.OrderlineID == s.OrderLineId)
        .InsertWhenNotMatched(s => new Order
        {
            OrderId = s.OrderId,
            OrderItemId = s.OrderItemId,
            OrderItemCode = s.OrderItemCode,
            //OrderCode = s.OrderCode,
            ProductionTicketID = s.ProductionTicketId,
            OrderDateTime = s.OrderDateTime,
            OrderDate = s.OrderDate,
            UploadEndDateTime = s.UploadEndDateTime,
            OrderlineID = s.OrderLineId.Value,
            ProductContentStatusName = s.ProductContentStatusName,
            ProductContentStatusDate = s.ProductContentStatusDate,
            Date = s.Date,
            CheckNotInFulfillmentFlow = s.check_notIn_FulfillmentFlow,
            CheckNotInPlantFromOrderDomain = s.check_notIn_PlantFromOrderDomain,
            CheckNotInFulfillment = s.check_notIn_Fulfillment,
            CheckNotInPlantFromFulfillment = s.check_notIn_PlantFromFulfillment,
            CheckVouchers = s.check_Vouchers,
            CheckExternalVendors = s.check_ExternalVendors,
            CheckNotInWaiting = s.check_notIn_Waiting,
            CheckDescription = s.CheckDescription,
            ProductContentID = s.ProductContentID,
            EditorName = s.EditorName,
            Created = DateTime.Now,
        })
        .MergeAsync();
}

How can I make the first code snippet work as expected?

Ivan Akcheurov
  • 2,173
  • 19
  • 15

0 Answers0