Best Practice to Lock a Record for Editing While Using Entity Framework

Best practice to lock a record for editing while using entity framework

Best practice is to use RowVersion and Optimistic locking.

Optimistic Concurrency Patterns explained.

If using Code first, then include a field in your POCO.

public virtual byte[] RowVersion { get; set; }

EF will add a Timestamp/RowVersion property to your table. It will be checked during Update. And automatically updated by the DB when changed.

EDIT: to better explain.

What EF is looking for is properties that are concurrency fields so you can actually control the concurrency with one or more fields.

entity.Property(p => p.RowVersion).IsConcurrencyToken()

when performing an update or delete you catch the defined exception

catch (DbUpdateConcurrencyException ex)

EF treats the RowVersion as a concurrency token. This is the Generally used approach. Since SQLServer will automatically update this field type for you.
So very fast and easy. But you can tell EF a property is a concurrency token explicitly and have more than one.

So EF should add properties to the where clause for updates and deletes
to make sure the record didn't change since accessed.

How To Avoid Locking Database In Entity Framework 4 When Doing Many Updates

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.

How do entity framework handle concurrency access,when issuing multiple delete & create opeartions

General approach to locking in EF is to use Optimistic locking.

EF Locking docu

SO example

-userB retrieve all the records , delete, add new records. but when userB reach the saveChnages() , entity framework will raise a
DbUpdateConcurrencyException when it try to delete a record that no
more exists ? so is this what will happen. ?

Yes this is what will happen IF you have declared a Concurrency field on the record.
eg

 public virtual byte[] RowVersion { get; set; }

What is the best way to prevent updating on specific fields in Entity Framework

Well I would advice against ever using the EF classes in the View. You're best bet is to construct ViewModel classes and use Automapper to map them from the EF classes.

When you are updating records in the database though, you can control which fields in the ViewModel are used to update the existing fields in the EF class.

The normal process would be:

• Use the Id to get the latest version of the existing object out of the database.

• If you are using optimistic concurrency control then check that the object has not been updated since the ViewModel was created (so check timestamp for example).

• Update this object with the required fields from your ViewModel object.

• Persist the updated object back to the database.

Update to include Automapper examples:

Let's say your POCO is

public class MyObject 
{
   public int Id {get;set;}
   public string Field1 {get;set;}
   public string Field2 {get;set;}
}

and Field1 is the field you don't want updating.

You should declare a view model with the same properties:

public class MyObjectModel 
{
   public int Id {get;set;}
   public string Field1 {get;set;}
   public string Field2 {get;set;}
}

and Automap between them in the constructor of your Controller.

Mapper.CreateMap<MyObject, MyObjectModel>();

you can if you wish (although I prefer to do this manually, automap the other way too:

Mapper.CreateMap<MyObjectModel, MyObject>().ForMember(dest=>dest.Field1, opt=>opt.Ignore());

When you are sending date to your website you would use:

 var myObjectModelInstance = Mapper.Map<MyObject, MyObjectModel>(myObjectInstance);

to create the viewModel.

When saving the data, you'd probably want something like:

public JsonResult SaveMyObject(MyObjectModel myModel)
{
    var poco = Mapper.Map<MyObjectModel, MyObject>(myModel);
    if(myModel.Id == 0 ) 
    {
       //New object
       poco.Field1 = myModel.Field1 //set Field1 for new creates only

    }
}

although I'd probably remove the exclusion of Field1 above and do something like:

public JsonResult SaveMyObject(MyObjectModel myModel)
{
   var poco;
   if(myModel.Id == 0)
   {
     poco = Mapper.Map<MyObjectModel, MyObject>(myModel);
   }        
   else
   {
     poco = myDataLayer.GetMyObjectById(myModel.Id);
     poco.Field2 = myModel.Field2;
   }
   myDataLayer.SaveMyObject(poco);
}

note I believe that best-practise would have you never Automap FROM the ViewModel, but to always do this manually, including for new items.

I need to do rollback or somenthing when I don't need to do anything more?

You should check your data before begin transaction, you dont need begin trasnaction while you are reading data from DB, because first part in your logic there is no any update,insert... and you dont need any commit or rollback

Actually your code should be like this

using(Entities dbContext = new Entities())
{
        //Get data from database

        //check data from database

        //if my data is the expected: return true;

using (DbContextTransaction myTransaction = dbContext.Database.BeginTransaction())
    {
         //if not is expected:
            try 
             {
               //update data;
               //dbContext.Savechages();
               //myTransaction.Commit();
               //return true;
             }
           catch(Exception ex)
           {
            transaction.Rollback();
            ......
            return false;
        }
    }
}

---

Related Topics