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.

Answer 2

There is no reason to detach the objects from the object state manager. Just by looking at the code, it doesn't make much sense at what you are trying to do. Perhaps you don't have much experience with EF, but I would recommend reading up on how EF works before using it.

You could simply load the User entity and include the rules in the result set for a batch operation: var dbUser = ctx.Users.Include(x => x.Rules).FirstOrDefault(x => x.UID == user.UID);

Now that you have your fully populated user object and all the rules associated with them, you can find the rule you want to remove: var ruleToDelete = dbUser.Rules.FirstOrDefault(x => x.UID == rule.UID);.

Now you have the rule from the user you want to delete and it didn't cost you a trip back to the database. Now you can remove the rule: ctx.Rules.Remove(ruleToDelete);

Now when you call ctx.SaveChanges() the changes will be pushed back to the database. You do need to understand that Entity Framework wraps all insert, update, and delete statements in a transaction automatically for you. Also, EF doesn't have support for batch insert, update, and delete operations.

//Get the user from the db, should check for null after this
var dbUser = ctx.Users.Include(x => x.Rules).FirstOrDefault(x => x.UID == user.UID);

//Get the rule from the user object you want to remove
var ruleToDelete = dbUser.Rules.FirstOrDefault(x => x.UID == rule.UID);

//Set the state of the object to "Deleted" in the state manager so it will issue a delete statement
ctx.Rules.Remove(ruleToDelete);

//Save the changes to the database in a transaction
ctx.SaveChanges()