How does Entity framework perform Transactions inside asp.net mvc

Question

I am using entity framework inside my asp.net mvc web application, but I can not understand how it will handle multiple transaction accessing the same data. For example I have the following action method that deelte a collection and then loop through a collection and delete the records:-

[HttpPost]
public ActionResult AssignPermisionLevel2(ICollection<SecurityroleTypePermision> list, int id)
{
    repository.DeleteSecurityroleTypePermisions(id);
    foreach (var c in list)
    {
        repository.InsertOrUpdateSecurityroleTypePermisions(c,User.Identity.Name);
    }
    repository.Save();
    return RedirectToAction("AssignPermisionLevel", new { id = id });
}

Which will call the following repository method:-

public void DeleteSecurityroleTypePermisions(int securityroleID)
{    
    var r = tms.SecurityroleTypePermisions.Where(a => a.SecurityRoleID == securityroleID);
    foreach (var c in r) {
        tms.SecurityroleTypePermisions.Remove(c);
    }

}

&

public void InsertOrUpdateSecurityroleTypePermisions(SecurityroleTypePermision role, string username)
{    
     var auditinfo = IntiateAdminAudit(tms.AuditActions.SingleOrDefault(a => a.Name.ToUpper() == "ASSIGN PERMISION").ID, tms.SecurityTaskTypes.SingleOrDefault(a => a.Name.ToUpper() == "SECURITY ROLE").ID, username, tms.SecurityRoles.SingleOrDefault(a=>a.SecurityRoleID == role.SecurityRoleID).Name, tms.PermisionLevels.SingleOrDefault(a=>a.ID== role.PermisionLevelID).Name +  " --> " + tms.TechnologyTypes.SingleOrDefault(a=>a.AssetTypeID == role.AssetTypeID).Name);
     tms.SecurityroleTypePermisions.Add(role);
     InsertOrUpdateAdminAudit(auditinfo);
}

So let say two users access the same action method at the same time, so will their transactions conflict with each other? , or all the transaction actions (Deletion & Addition) will execute and then the other transaction will start?

UPDATE Inside my Controller class i will initiate the repository as follow :-

[Authorize]
    public class SecurityRoleController : Controller
    {

        Repository repository = new Repository();

my second question is . You mentioned that EF will mark the entities for deletion or for insetion, then the sql will execute indie the database. but what if one sql statement delete some entities and the other sql statement from the second transaction delete the other entities , could this conflict happen at the database level ? or once the first sql statement from the first transaction start execution, it will prevent other transactions from being executed ? can you advice ?

Answer 1

This entirely depends on how you implement your DbContext. If your context is instantiated within a controller then each transaction will be contained within that context, i.e.

public class SomeController : Controller
{
    var repository = new DbContext();

    [HttpPost]
    public ActionResult AssignPermisionLevel2(ICollection<SecurityroleTypePermision> list, int id)
    {
        repository.DeleteSecurityroleTypePermisions(id);
        foreach (var c in list)
        {
            repository.InsertOrUpdateSecurityroleTypePermisions(c,User.Identity.Name);
        }
        repository.Save();
        return RedirectToAction("AssignPermisionLevel", new { id = id });
    }       
}

Each request will create its own instance of the repository and the two will not conflict on an application level. When SaveChanges is called on a DbContext it is done in a single transaction, and as the repository object is created for each request.

Unfortunately Entity Framework does not delete as you expect, and will delete individual elements rather than the entire table. What is actually happening when you are removing the entities in the first step and adding them in the second is as follows:

1. Load Entities X,Y, and Z
2. Mark X,Y, and Z for deletion
3. Insert new rows A, B and C
4. Run SQL which deletes X, Y and Z, and inserts A, B and C

Now if two requests come in at the same time what could possibly happen is objects X,Y and Z are both loaded in step 1 by both request contexts. They are both marked for deletion and two sets of A, B and C are set to insert. When the first transaction executes it will be fine, however when the second transaction commits it will not be able to find X, Y and Z as they no longer exist.

You may be able to use a lock over the critical section so that the entities are not loaded before they are deleted by another request. The lock would have to be static so something such as:

public class SecurityRoleController : Controller
{
Repository repository = new Repository();
public static object REQUEST_LOCK = new object();

[HttpPost]
public ActionResult AssignPermisionLevel2(ICollection<SecurityroleTypePermision> list, int id)
{
    lock(REQUEST_LOCK)
    {
            repository.DeleteSecurityroleTypePermisions(id);
            foreach (var c in list)
            {
                repository.InsertOrUpdateSecurityroleTypePermisions(c,User.Identity.Name);
            }
            repository.Save();
        }
            return RedirectToAction("AssignPermisionLevel", new { id = id });
    }       
}

Update 2

There are two sides to your problem, the way SQL handles transactions and the way Entity Framework performs deletes. Without going into massive detail on threading you basically have to lock the action so that the same method cannot execute twice at exactly the same time. This will prevent the context from reading potentially stale/already deleted data.

You can read more on SQL/EF race conditions with this question: Preventing race condition of if-exists-update-else-insert in Entity Framework