注册

达梦EF库构造的批量插入语句在执行后返回"影响了0条数据"导致EF框架报错

飞羽流星 2021/09/24 1107 3

【DM版本】:DM8.1.2.61
【操作系统】:Windows 10
【CPU】: x64
【问题描述】:
EF Core库版本: 3.1.0.5402

当对一个表插入多条数据时,达梦的EF Core库会将插入语句翻译为一种特殊的以数组的形式插入的批量插入语句
(已简化其中重复的数组项定义,替换项目中的字段名,其中ID_KEY为自增的主键)

BEGIN 
DECLARE  TYPE rrr0 IS RECORD ("prop1" BIGINT, "prop2" NVARCHAR2(8188), "prop3" INT, "prop4" NVARCHAR2(8188), "prop5" datetime, "prop6" BIGINT, "prop7" BIGINT, "_Position" int);
TYPE ccc0 IS ARRAY rrr0[];
c0 ccc0; 
"V_ID_KEY" BIGINT;
BEGIN
c0 = NEW rrr0[1];
c0[1]."prop1" = :p0 ;
c0[1]."prop2" = :p1 ;
c0[1]."prop3" = :p2 ;
c0[1]."prop4" = :p3 ;
c0[1]."prop5" = :p4 ;
c0[1]."prop6" = :p5 ;
c0[1]."prop7" = :p6 ;
c0[1]."_Position" = 0;
SELECT MAX("ID_KEY") INTO "V_ID_KEY" FROM MYSCHEMA."MYTABLE";
INSERT INTO MYSCHEMA."MYTABLE" ("prop1", "prop2", "prop3", "prop4", "prop5", "prop6", "prop7")SELECT "prop1", "prop2", "prop3", "prop4", "prop5", "prop6", "prop7" FROM ARRAY C0;
SELECT "t"."ID_KEY" FROM MYSCHEMA."MYTABLE" "t"
 WHERE EXISTS(SELECT * FROM ARRAY c0 "i" WHERE ("t"."prop1" = "i"."prop1" OR ("t"."prop1" IS NULL AND "i"."prop1" IS NULL ))  AND (TEXT_EQUAL("t"."prop2", "i"."prop2") OR ("t"."prop2" IS NULL AND "i"."prop2" IS NULL ))  AND ("t"."prop3" = "i"."prop3" OR ("t"."prop3" IS NULL AND "i"."prop3" IS NULL ))  AND (TEXT_EQUAL("t"."prop4", "i"."prop4") OR ("t"."prop4" IS NULL AND "i"."prop4" IS NULL ))  AND ("t"."prop5" = "i"."prop5" OR ("t"."prop5" IS NULL AND "i"."prop5" IS NULL ))  AND ("t"."prop6" = "i"."prop6" OR ("t"."prop6" IS NULL AND "i"."prop6" IS NULL ))  AND ("t"."prop7" = "i"."prop7" OR ("t"."prop7" IS NULL AND "i"."prop7" IS NULL )) ) AND ( "t"."ID_KEY" > "V_ID_KEY" OR "V_ID_KEY" IS NULL) 
ORDER BY "t"."ROWID";

 END;
 END;

在管理工具执行该语句后返回如下结果

输入参数1:1
输入参数2:abcd
输入参数3:30
输入参数4:asefasef
输入参数5:2021-03-03
输入参数6:1
输入参数7:null
执行成功, 执行耗时67毫秒. 执行号:147614
影响了0条记录

1条语句执行成功

请注意,该语句返回的信息为"影响了0条记录"。
并且该SQL语句的确没有成功写入任何有效数据,尽管它确实使得标识列种子+1了。

在代码中使用Add或AddRange方法添加20条数据并调用SaveChanges方法后,达梦EF Core库翻译的该语句执行后抛出了以下异常:

Microsoft.EntityFrameworkCore.DbUpdateConcurrencyException: Database operation expected to affect 20 row(s) but actually affected 0 row(s). Data may have been modified or deleted since entities were loaded. See ht*tp://go.microsoft.com/fwlink/?LinkId=527962 for information on understanding and handling optimistic concurrency exceptions.
   at Microsoft.EntityFrameworkCore.Update.AffectedCountModificationCommandBatch.ThrowAggregateUpdateConcurrencyException(Int32 commandIndex, Int32 expectedRowsAffected, Int32 rowsAffected)
   at Microsoft.EntityFrameworkCore.Update.AffectedCountModificationCommandBatch.ConsumeResultSetWithPropagation(Int32 commandIndex, RelationalDataReader reader)
   at Microsoft.EntityFrameworkCore.Update.AffectedCountModificationCommandBatch.Consume(RelationalDataReader reader)
   at Microsoft.EntityFrameworkCore.Update.ReaderModificationCommandBatch.Execute(IRelationalConnection connection)
   at Microsoft.EntityFrameworkCore.Update.Internal.BatchExecutor.Execute(IEnumerable`1 commandBatches, IRelationalConnection connection)
   at Microsoft.EntityFrameworkCore.Storage.RelationalDatabase.SaveChanges(IList`1 entries)
   at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChanges(IList`1 entriesToSave)
   at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChanges(DbContext _, Boolean acceptAllChangesOnSuccess)
   at Microsoft.EntityFrameworkCore.Dm.Storage.Internal.DmExecutionStrategy.Execute[TState,TResult](TState state, Func`3 operation, Func`3 verifySucceeded)
   at Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChanges(Boolean acceptAllChangesOnSuccess)
   at Microsoft.EntityFrameworkCore.DbContext.SaveChanges(Boolean acceptAllChangesOnSuccess)
   at Microsoft.EntityFrameworkCore.DbContext.SaveChanges()
   ...

经检查,此语句是自带事务的,若在语句末尾添加COMMIT将提交结果并成功插入数据。
但是此语句的事务机制导致返回影响行数为0,在EFCore中不兼容,且加上COMMIT后依然返回"影响了0条记录"。
此语句问题导致无法使用达梦EF库执行此类批量插入操作,目前只能循环需要插入的数据列表并对每条数据执行SaveChanges.

==========================================
很奇怪,这个bug并没有出现在另一个表上…

回答 0
暂无回答
扫一扫
联系客服