CODEWITHSUNDEEP
c#entity-framework
MaPi
MaPi

Reputation: 1601

Error on BulkSaveChanges with Entity Framework

I'm trying out Entity Framework Classic to batch the SaveChanges() operations with BulkSaveChanges.

It succeeds for certain records, but it fails for others (I've been breaking down the call to individual operations, and I've run the profiler).

The operation that makes it fail is:

exec sp_executesql N'CREATE TABLE #ZZZProjects_26190111_a15e_47d7_b253_2cc24510b3f8z ( [$action] VARCHAR(100) NULL, ZZZ_Index INT NULL, [Id] [sys].[int] NULL );
MERGE INTO [dbo].[MyEntityContact]  AS DestinationTable
USING
(
SELECT TOP 100 PERCENT * FROM (SELECT @0_0 AS [externalField], @0_1 AS [MyEntityId], @0_2 AS [CreatedDate], @0_3 AS [UpdateUser], @0_4 AS [UpdateDate], @0_5 AS [Id], @0_6 AS ZZZ_Index
UNION ALL SELECT @1_0 AS [externalField], @1_1 AS [MyEntityId], @1_2 AS [CreatedDate], @1_3 AS [UpdateUser], @1_4 AS [UpdateDate], @1_5 AS [Id], @1_6 AS ZZZ_Index
UNION ALL SELECT @2_0 AS [externalField], @2_1 AS [MyEntityId], @2_2 AS [CreatedDate], @2_3 AS [UpdateUser], @2_4 AS [UpdateDate], @2_5 AS [Id], @2_6 AS ZZZ_Index
UNION ALL SELECT @3_0 AS [externalField], @3_1 AS [MyEntityId], @3_2 AS [CreatedDate], @3_3 AS [UpdateUser], @3_4 AS [UpdateDate], @3_5 AS [Id], @3_6 AS ZZZ_Index
UNION ALL SELECT @4_0 AS [externalField], @4_1 AS [MyEntityId], @4_2 AS [CreatedDate], @4_3 AS [UpdateUser], @4_4 AS [UpdateDate], @4_5 AS [Id], @4_6 AS ZZZ_Index
UNION ALL SELECT @5_0 AS [externalField], @5_1 AS [MyEntityId], @5_2 AS [CreatedDate], @5_3 AS [UpdateUser], @5_4 AS [UpdateDate], @5_5 AS [Id], @5_6 AS ZZZ_Index) AS StagingTable ORDER BY ZZZ_Index
) AS StagingTable
ON 1 = 2
WHEN NOT MATCHED THEN
    INSERT ( [externalField], [MyEntityId], [CreatedDate], [UpdateUser], [UpdateDate] )
    VALUES ( [externalField], [MyEntityId], [CreatedDate], [UpdateUser], [UpdateDate] )
OUTPUT
    $action,
    StagingTable.ZZZ_Index,
    INSERTED.[Id]
INTO #ZZZProjects_26190111_a15e_47d7_b253_2cc24510b3f8z
;
SELECT   A.* ,B.[Id] AS [Id_zzzinserted] FROM #ZZZProjects_26190111_a15e_47d7_b253_2cc24510b3f8z AS A
INNER JOIN [dbo].[MyEntityContact] AS B  ON  A.[Id] = B.[Id]
;
DROP TABLE #ZZZProjects_26190111_a15e_47d7_b253_2cc24510b3f8z;',N'@0_0 int,@0_1 int,@0_2 smalldatetime,@0_3 nvarchar(50),@0_4 datetime,@0_5 int,@0_6 int,@1_0 int,@1_1 int,@1_2 smalldatetime,@1_3 nvarchar(50),@1_4 datetime,@1_5 int,@1_6 int,@2_0 int,@2_1 int,@2_2 smalldatetime,@2_3 nvarchar(50),@2_4 datetime,@2_5 int,@2_6 int,@3_0 int,@3_1 int,@3_2 smalldatetime,@3_3 nvarchar(50),@3_4 datetime,@3_5 int,@3_6 int,@4_0 int,@4_1 int,@4_2 smalldatetime,@4_3 nvarchar(50),@4_4 datetime,@4_5 int,@4_6 int,@5_0 int,@5_1 int,@5_2 smalldatetime,@5_3 nvarchar(50),@5_4 datetime,@5_5 int,@5_6 int',@0_0=34,@0_1=1297,@0_2='2014-09-16 17:05:00',@0_3=N'MyDomain\myUser',@0_4='2019-12-11 17:45:05.057',@0_5=NULL,@0_6=0,@1_0=35,@1_1=1297,@1_2='2014-09-16 17:33:00',@1_3=N'MyDomain\myUser',@1_4='2019-12-11 17:45:05.073',@1_5=NULL,@1_6=1,@2_0=37,@2_1=1297,@2_2='2014-09-17 16:27:00',@2_3=N'MyDomain\myUser',@2_4='2019-12-11 17:45:05.073',@2_5=NULL,@2_6=2,@3_0=47,@3_1=1297,@3_2='2014-09-19 11:55:00',@3_3=N'MyDomain\myUser',@3_4='2019-12-11 17:45:05.073',@3_5=NULL,@3_6=3,@4_0=54,@4_1=1297,@4_2='2014-09-22 10:49:00',@4_3=N'MyDomain\myUser',@4_4='2019-12-11 17:45:05.090',@4_5=NULL,@4_6=4,@5_0=66,@5_1=1297,@5_2='2014-09-29 13:59:00',@5_3=N'MyDomain\myUser',@5_4='2019-12-11 17:45:05.090',@5_5=NULL,@5_6=5

The reported error is

SqlTypeException: SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM.

I've been using a different id for MyEntityId while running the query, and it passed.

The debugger could not point out much more information about what column was making this fail.

Any idea?

Upvotes: 0

Views: 544

Answers (1)

MaPi
MaPi

Reputation: 1601

I had some operations executing before the SaveChanges method (populating the audit columns). By performing the same operations before the BulkSaveChanges everything went fine (and faster!).

Upvotes: 1

Related Questions