c# - Entity Framework, Transactions and Stored Procs -

i have stored procedure writes data table, based on values different table. so, reads start/end dates table, creates whole lot of data , writes separate table.

what happens user selects start/end date something, , store start/end date table. call stored procedure, within ef, , procedures reads table updated, , populates different table.

if proc fails, want roll data proc wrote, initial update of table.

i think data written table (the initial update, done in ef) when call 'savechanges'. call that, , call procedure. there way detect if procedure failed, , if so, rollback udpates (the table update, , proc did)?

currently, code looks this, seems paremeter in savechanges invalid (wants no parameters), , 'acceptallchanges' invalid:

using (var scope = new transactionscope()) {     context.savechanges(false);     context.project_scheduled_event_payments(st.id);     context.acceptallchanges(); } 

you can use transaction scope. you'll need add reference system.transactions.dll , add using system.transactions;

from msdn:

if call savechanges() or savechanges(true),the ef assumes if work completes okay, okay, discard changes has been tracking, , wait new changes.

unfortunately though if goes wrong somewhere else in transaction, because ef discarded changes tracking, can’t recover.

this savechanges(false) , acceptallchanges() come in.

savechanges(false) tells ef execute necessary database commands, hold on changes, can replayed if necessary.

now if broader transaction fails can retry ef specific bits, call savechanges(false). alternatively can walk through state-manager log failed.

once broader transaction succeeds, call acceptallchanges() manually, , changes being tracked discarded.

using (transactionscope scope = new transactionscope())  {      //do context1       //save changes don't discard yet      context1.savechanges(false);       //run secondary procedure, if succeeds then:         //     //     context1.acceptallchanges();   } 

edit: okay, above not work since .savechanges(false) objectcontext method (also deprecated .savechanges(saveoptions)) , have dbcontext. know dbcontext wrapper around objectcontext, can use iobjectcontextadapter access .savechanges(saveoptions) method:

first we'll need add using system.data.entity.infrastructure;, then:

using (transactionscope scope = new transactionscope())  {      // context       // cast context iobjectcontextadapter access full savechanges(saveoptions) method       iobjectcontextadapter contextadapter = context;      // .detectchangesbeforesave equivalent of .savechanges(false);     contextadapter.objectcontext.savechanges(         system.data.entity.core.objects.saveoptions.detectchangesbeforesave);      //run secondary procedure, if succeeds then:         //     //      contextadapter.objectcontext.acceptallchanges();      scope.complete();  }