Entity Framework 4.0 中的批处理 DB 命令

问题描述

我当前的项目需要每天与外部系统同步.同步基于复杂的导入文件结构，该结构通过广泛的业务逻辑进行解析和处理.由于业务逻辑，我们决定在 .NET 代码中实现这一点并重用现有的 BL 组件，而不是在存储过程或集成服务中编写相同的逻辑.

My current project requires everyday synchronization with external system. The synchronization is based on complex import file structure which is parsed and processed with extensive business logic. Due to the business logic we decided to make this in .NET code and reuse existing BL components instead of writting the same logic in stored procedures or integration services.

BL 层位于 EF 4.0 数据访问层之上.当前的执行过程是导入批处理，将所有更改填充到ObjectContext 中并在事务中执行SaveChanges.当我检查 SQL 探查器时，我看到 EF 将每个实体更改作为单个 SQL 命令执行(具有自己的到 DB 的往返行程).此外，看起来这些命令是完全按顺序执行的.因此，我有多达 100.000 次往返数据库以进行初始导入，以及 10.000 - 50.000 次往返数据库以进行每日同步.

The BL layer sits on top of EF 4.0 data access layer. Current implementation process the import batch, fills all changes into ObjectContext and executes SaveChanges in transaction. When I check SQL profiler I see that EF executes each entity change as single SQL command (with its own round trip to DB). Moreover it looks like these commands are executed fully sequentially. So I have up to 100.000 roundtrips to database for initial import and between 10.000 - 50.000 roundtrips to database for daily synchronization.

是否可以通过 EF 本身或某些提供程序/扩展以某种方式批量插入/更新/删除命令?

Is it possible to batch insert/update/delete commands somehow by EF itself or by some provider / extension?

推荐答案

不，这是不可能的(是的 - 我也哭了).

No, it can't be done (yes - i weep also).

EF 不支持批量操作，LINQ-SQL 有(有)同样的问题.

EF does not support batch operations, LINQ-SQL had (has) the same problem.

您有几个选择:

1. 存储过程
2. 经典的 ADO.NET 或 EntitySQL
3. 触发器

我过去选择了选项 1 和选项 3.

I've gone with option 1 and 3 in the past.

所有三种方法的问题是你失去了 EF 抽象、内部图(乐观并发)，并且你又回到了原生 SQL 的世界.

The problem with all three approaches is you lose the EF abstraction, the internal graph (optimistic concurrency), and your brought back to the world of native SQL.

这篇关于Entity Framework 4.0 中的批处理 DB 命令的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！