This question is in regards to the best practice for handling many inserts or updates using Microsoft Entity Framework. The problem is that we wrote a long-running program which pulls back thousands of records from the database, and then updates a single field on each of those records, one-by-one. Much to our dismay, we realized that each of these records that were updated were locked for the duration of the time in which the ObjectContext was not disposed. Below is some pseudocode (doesn't actually run) to illustrate:
using(ObjectContext context = new ObjectContext())
var myRecords = context.CreateObjectSet<MyType>().AsQueryable();
foreach(var record in myRecords)
record.MyField = "updated!";
//--do something really slow like call an external web service
Entity framework on top of SQL server by default uses read committed transaction isolation level and transaction is committed at the end of
SaveChanges. If you suspect other behavior it must be by the rest of your code (are you using
TransactionScope? - you didn't show it in your code) or it must be some bug.
Also your approach is wrong. If you want to save each record separately you should also load each record separately. EF is definitely bad choice for this type of applications. Even if you use only single
SaveChange for updating all your records it will still make single roundtrip to database for each update.