Entity Framework 4.1 Batch Updates

I'm doing some work with EF 4.1 and I'd like to perform a batch update on 3 tables (for the purpose of implementing a soft delete). From doing some quick research it looks like direct SQL queries are best. My implementation is 开发者_如何学运维below and I was hoping for some feedback on whether this is best practice. Thanks for the help ...

using (var scope = new TransactionScope())
{
     using (var db = new TimeCatchDb())
     {

        db.Database.ExecuteSqlCommand("UPDATE EmployeeRecords SET Deleted = 1 WHERE NoteTypeId = @p0", new SqlParameter { ParameterName = "p0", Value = id});

        db.Database.ExecuteSqlCommand("UPDATE Notes SET Deleted = 1 WHERE NoteTypeId = @p0", new SqlParameter { ParameterName = "p0", Value = id });

        db.Database.ExecuteSqlCommand("UPDATE NoteTypes SET Deleted = 1 WHERE Id = @p0", new SqlParameter { ParameterName = "p0", Value = id });
     }

     scope.Complete();
}

you can extend the ef with the following extension

Entity Framework Extended Library

A library the extends the functionality of Entity Framework.

Features •Batch Update and Delete •Future Queries •Audit Log Project Package and Source NuGet Package

PM> Install-Package EntityFramework.Extended

• NuGet: http://nuget.org/List/Packages/EntityFramework.Extended
• Source: http://github.com/loresoft/EntityFramework.Extended

Batch Update and Delete A current limitations of the Entity Framework is that in order to update or delete an entity you have to first retrieve it into memory. Now in most scenarios this is just fine. There are however some senerios where performance would suffer. Also, for single deletes, the object must be retrieved before it can be deleted requiring two calls to the database. Batch update and delete eliminates the need to retrieve and load an entity before modifying it.

//delete all users where FirstName matches
context.Users.Delete(u => u.FirstName == "firstname");

//update all tasks with status of 1 to status of 2
context.Tasks.Update(
    t => t.StatusId == 1, 
    t => new Task {StatusId = 2});

//example of using an IQueryable as the filter for the update
var users = context.Users
   .Where(u => u.FirstName == "firstname");

context.Users.Update(
   users, 
   u => new User {FirstName = "newfirstname"})

.

If you want batch why don't you place all updates into single ExecuteSqlCommand? Each call to ExecuteSqlCommand makes its own round trip to the database = it is not a batch. Also be aware that if any of modified records is currently loaded in the context changes will not be reflected = loaded entities will still have Deleted = 1. Direct SQL commands affect only database.

This is best achieved in normal EFv4 + EDMX by mapping custom stored procedure to Delete operation (but it also doesn't support batches) but once you don't have EDMX you cannot do that.