EF6 - How to load entities in an optimized way for particular example?

Question

I have Users - Rules tables with many-to-many relationship. I want to delete some rules of a certain user and update one field of that user in the same step / database transaction, so I have the following pseudo-code in BL:

this.UnitOfWork.ExecuteTransaction(() =>
{
   // delete rules
   foreach (var rule in ruleList)
       repoRules.DeleteRulesForUser(user, rule);

   // update user field
   repoUser.UpdateUserField(user);
}

In EF repositories:

public void DeleteRulesForUser(User user, Rule rule)
{
   // check user
   EFUser u = this.dbContext.EFUsers.Find(user.UID);
   if (u == null)
      throw new DBModuleException();

   // check rule
   EFRule r = this.dbContext.EFRules.Find(rule.UID);
   if (r == null)
      throw new DBModuleException();


   // detach previous entities, so that we can attach with relation 
   this.dbContext.ObjectContext.Detach(u);
   this.dbContext.ObjectContext.Detach(r);

   // prepare entities
   var efUser = new EFUser { US_UID = user.UID };
   var efRule = new EFRule { RU_UID = rule.UID };
   efRule.Users.Add(efUser);

   // attach
   this.dbContext.EFRules.Attach(efRule);

   // delete relation
   efRule.Users.Remove(efUser);
}

public void UpdateUserField(User user)
{
    // get updating user
    EFUser efUser = this.dbContext.EFUsers.FirstOrDefault(u => u.UID == user.UID);
    if (efUser == null)
       throw new DBModuleException("Not found!");

    // perform update
    efUser.US_Field = user.Field;
}

My UnitOfWork implementation (only with EF6 or above):

public override void ExecuteTransaction(Action transAction)
{
    // make sure to make IsolationLevel flexible later
    using (var dbTransaction = this.dbContext.Database
                              .BeginTransaction(IsolationLevel.ReadCommitted)) 
    {
       try
       {
           transAction();

           // save changes if not done yet
           this.dbContext.SaveChanges();

           // commit transaction
           dbTransaction.Commit();
       }
       catch (System.Data.DataException ex)
       {
           // rollback
           dbTransaction.Rollback();

           // handling exception ceremony comes here
       }
    }

My problem is that DeleteRulesForUser attaches that user with only UID (as specified in the method), but then, later in the UpdateUserField EF is providing this user with only UID, instead of getting the user with complete data because FirstOrDefault calls the database. I assume this is the way how EF cache works(?). Anyhow, the update operation fails and I want to learn how to resolve this best.

I see the need of loading sometimes the entity with only UIDs, but sometimes with complete data.

I was thinking to detach the entities at the end of DeleteRulesForUser method, but I don't think is good for performance, due to the fact there are for-loops sometimes in BL. I also thought maybe I should rewrite my Update method, but don't know a good way to do that yet.

What would you recommend?

FYI: I have LazyLoading disabled.

Note: BL doesn't use EF classes, but proprietary domain classes (EF support has been added later). Don't criticize the existing architecture, I want a solution for this as is.

EDIT1: As I read a bit more, maybe AutoDetectChangesEnabled could help, but I need more info to find out.

Answer 1

If you have the requirement to delete thousands of rules, I would suggest to drop Entity Framework and use ADO.NET and sql statements (or stored procedures), which is a lot faster and simpler. See Batch update/delete EF5.

There's also no reason for attaching or detaching objects. If you use EF, create a context from the moment you need it and dispose it as soon as possible.