Batching DB commands in Entity Framework 4.0

Question

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.

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.

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

Answer 1

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

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

You've got a few options:

1. Stored Procedures
2. Classic ADO.NET or EntitySQL
3. Triggers

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

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.