I need to do Multiple Breeze SaveChanges in one transaction

Question

I have a webpage that accepts a fee payment and it has to INSERT 6 rows, spread across 4 tables. I have had to break the INSERTS into two separate SaveChanges but I need them to both be within the same database transaction so that all INSERTS and UPDATES will rollback in case of a problem.

I'm using Breeze 1.6 on top of Entity Framework 6.2, on top of Oracle Mgd Data Access 12.2 in a SPA template.

The 4 tables are A, B, C, and D. Tables B, C, and D are children of A, each carrying A's PK as a foreign key. I originally coded INSERTS in this sequence as required by my app A1, B1, C1, C2, C3, D1 and followed that by a single Breeze SaveChanges. C3 has Oracle triggers that update a few columns in A1 and B1 at the time C3 is INSERTED. My problem is that Entity Framework was not INSERTING in the sequence I coded (and I understand I have no control over the sequence). I was actually getting this sequence: A1, C1, C2, C3, B1, D1 and since C3 has a trigger that updates A and B, it ran into a problem because B wasn't INSERTED yet.

So I found this question: What logic determines the insert order of Entity Framework 6 and it suggests using multiple SaveChanges to gain some control. Got that working as follows:

• A1, B1 SaveChanges
• C1, C2, C3, D1 SaveChanges All triggers including the C3 update of A and B are working great now.

BUT Breeze/Entity Framework treats each SaveChanges as a transaction so now I can't rollback both parts in case of errors in the 2nd SaveChanges. Am I going to have to code the Part 1 rollback myself in case of a Part 2 failure? Or is there a secret I don't know?

I am familiar with BeginTransaction, Commit, and Rollback but not sure how/where to fit it in with Breeze and Entity Framework in the mix. Breeze grabs each SaveChanges and wraps it in a transaction automatically.

I need to have multiple SaveChanges grouped into one Transaction.

Thanks for any help.

Answer 1

I think the best thing to do in this case is to let the Breeze client treat it as a single save, while the server breaks apart the save bundle to save it in the right order.

Let the Breeze ContextProvider deserialize the save bundle, then use the BeforeSaveEntities hook to manually make the changes. Finally, mark the entities as Unchanged so that the ContextProvider won't try to save them again.

Here's an example implementation.

Server

On the server, in your Breeze Controller, create a new save method that adds a BeforeSaveEntitiesDelegate that is only used for this special save:

[HttpPost]
public SaveResult SaveFeePayment(JObject saveBundle) {
{
    contextProvider.BeforeSaveEntitiesDelegate += BeforeSaveFeePayment;
    return contextProvider.SaveChanges(saveBundle);
}

private Dictionary<Type, List<EntityInfo>> BeforeSaveFeePayment(Dictionary<Type, List<EntityInfo>> entities)
{
    var context = contextProvider.Context;

    // Get the list of EntityInfo for each type.  Throws exception if not found.
    // A fee payment save bundle must have A, B, C, and D or it is invalid.
    var ainfos = entities[typeof(A)];
    var binfos = entities[typeof(B)];
    var cinfos = entities[typeof(C)];
    var dinfos = entities[typeof(D)];

    using (var tx = context.Database.BeginTransaction()) 
    {               
        // Save A and B
        Attach(context, ainfos);
        Attach(context, binfos);
        context.SaveChanges();

        // Save C and D
        Attach(context, cinfos);
        Attach(context, dinfos);
        context.SaveChanges();

        tx.Commit();
    }

    // Set all states to Unchanged, so Breeze won't try to save them again
    ainfos.ForEach(info => info.EntityState = Breeze.ContextProvider.EntityState.Unchanged);
    binfos.ForEach(info => info.EntityState = Breeze.ContextProvider.EntityState.Unchanged);
    cinfos.ForEach(info => info.EntityState = Breeze.ContextProvider.EntityState.Unchanged);
    dinfos.ForEach(info => info.EntityState = Breeze.ContextProvider.EntityState.Unchanged);

    // Return the entities, so Breeze will return them back to the client
    return entities;
}

// Map Breeze EntityState to EF EntityState
private static Dictionary<Breeze.ContextProvider.EntityState, System.Data.Entity.EntityState> entityStateMap =
    new Dictionary<Breeze.ContextProvider.EntityState, System.Data.Entity.EntityState> {
      { Breeze.ContextProvider.EntityState.Added, System.Data.Entity.EntityState.Added },
      { Breeze.ContextProvider.EntityState.Deleted, System.Data.Entity.EntityState.Deleted },
      { Breeze.ContextProvider.EntityState.Detached, System.Data.Entity.EntityState.Detached },
      { Breeze.ContextProvider.EntityState.Modified, System.Data.Entity.EntityState.Modified },
      { Breeze.ContextProvider.EntityState.Unchanged, System.Data.Entity.EntityState.Unchanged }
    };

// Attach entities to the DbContext in the correct entity state 
private static void Attach(DbContext context, List<EntityInfo> infos)
{
    foreach(var info in infos)
    {
        var efState = entityStateMap[info.EntityState];
        context.Entry(info.Entity).State = efState;
    }
}

Client

On the client, you will need to call the SaveFeePayment endpoint using a named save. You'll do this only when saving a fee payment. Continue use the normal SaveChanges endpoint for other saves.

Call the special endpoint by specifying the resourceName:

var saveOptions = new SaveOptions({ resourceName: "SaveFeePayment" });
return myEntityManager.saveChanges(null, saveOptions);

Transactions?

I haven't tested this example to be 100% about the transaction behavior. I am not sure whether we should be using the existing contextProvider.Context or creating a new DbContext at the start of the method. The difference is in how the database connections are handled. See Microsoft's guidance, and hope that it works the same with Oracle.

Hopefully your previous transaction management solution can be applied to the BeforeSaveFeePayment method above.

Hope this helps.