0

I have a stored procedure that writes data to a table, based on values from a different table. So, it reads the start/end dates from a table, then creates a whole lot of data and writes it to a separate table.

What happens is the user selects a Start/End date for something, and then I store the start/end date into a table. I then call the stored procedure, from within EF, and that procedures reads from the table just updated, and populates a different table.

If the proc fails, I want to roll back any data that the proc wrote, as well as the initial update of the table.

I think the data is only written to the table (The initial update, done in EF) when you call the 'SaveChanges'. So I call that, and THEN call the procedure. Is there a way to detect if the procedure failed, and if so, rollback ALL udpates (The table update, and anything the proc did)?

Currently, my code looks like this, but it seems a paremeter in SaveChanges is invalid (wants no parameters), and 'AcceptAllChanges' is invalid:

using (var scope = new TransactionScope()) { Context.SaveChanges(false); Context.project_scheduled_event_payments(st.id); Context.AcceptAllChanges(); }
Improve this question

1 Answer 1

Reset to default
1

You can use a transaction scope. You'll need to add a reference to System.Transactions.dll and add a using System.Transactions;

From msdn:

If you call SaveChanges() or SaveChanges(true),the EF simply assumes that if its work completes okay, everything is okay, so it will discard the changes it has been tracking, and wait for new changes.

Unfortunately though if something goes wrong somewhere else in the transaction, because the EF discarded the changes it was tracking, we can’t recover.

This is where SaveChanges(false) and AcceptAllChanges() come in.

SaveChanges(false) tells the EF to execute the necessary database commands, but hold on to the changes, so they can be replayed if necessary.

Now if the broader transaction fails you can retry the EF specific bits, with another call to SaveChanges(false). Alternatively you can walk through the state-manager to log what failed.

Once the broader transaction succeeds, you simply call AcceptAllChanges() manually, and the changes that were being tracked are discarded.

using (TransactionScope scope = new TransactionScope()) { //Do something with context1 //Save Changes but don't discard yet context1.SaveChanges(false); //run your secondary procedure, if it succeeds then: // // context1.AcceptAllChanges(); }

edit: Okay, the above will not work since .SaveChanges(false) is an ObjectContext method (also deprecated for .SaveChanges(SaveOptions)) and we have a DbContext. We know DbContext is a wrapper around ObjectContext, so we can use an IObjectContextAdapter to get access to the .SaveChanges(SaveOptions) method:

First we'll need to add using System.Data.Entity.Infrastructure;, then:

using (TransactionScope scope = new TransactionScope()) { // do something with your context // cast your context to IObjectContextAdapter to get access to the full SaveChanges(SaveOptions) method IObjectContextAdapter contextAdapter = context; // .DetectChangesBeforeSave is the equivalent of .SaveChanges(false); contextAdapter.ObjectContext.SaveChanges( System.Data.Entity.Core.Objects.SaveOptions.DetectChangesBeforeSave); //run your secondary procedure, if it succeeds then: // // contextAdapter.ObjectContext.AcceptAllChanges(); scope.Complete(); }
Improve this answer
Sign up to request clarification or add additional context in comments.

7 Comments

Thanks Tom. Is this available in EF5? As I get the 'Can non identify symbol 'TransactionScope' issue.
@Craig Updated my answer, I believe you're just missing a reference.
Thanks @Tom - But now I am battling with SaveChanges. Doesn't want a boolean parameter. Wants zero arguments. Maybe I need to go to EF6, as I notice all docs seems to say "EF6 specific"?
Upgraded to EF6, but same issue. It seems it doesnt know what Context.AcceptAllChanges - nor does it want a parameter in SaveChanges. I'm wondering if I have my context setup incorrectly.
I'm generating my model from the DB... and I believe I am therefore, non-code-first? It's possible I've done something wrong though. When I setup my context, I do use: Context.Configuration.LazyLoadingEnabled = false; - Could that cause an issue? I wouldn't think so..
|

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.