c# - Entity Framework Code First and SQL Server 2012 Sequences -

i in middle of implementing database audit trail whereby crud operations performed through controllers in web api project serialize old , new poco's , store values later retrieval (historical, rollback, etc...).

when got working, did not how made controllers during post because ended having call savechanges() twice, once id inserted entity , again commit audit record needed know id.

i set out convert project (still in infancy) use sequences instead of identity columns. has added bonus of further abstracting me sql server, though not issue, allows me reduce number of commits , lets me pull logic out of controller , stuff service layer abstracts controllers repositories , lets me work auditing in "shim" layer.

once sequence object created , stored procedure expose it, created following class:

public class sequentialidprovider : isequentialidprovider {     private readonly iservice<sequencevalue> _sequencevalueservice;      public sequentialidprovider(iservice<sequencevalue> sequencevalueservice)     {         _sequencevalueservice = sequencevalueservice;     }      public int getnextid()     {         var value = _sequencevalueservice.selectquery("getsequenceids @numberofids", new sqlparameter("numberofids", sqldbtype.int) { value = 1 }).tolist();         if (value.first() == null)         {             throw new exception("unable retrieve next id's sequence.");         }          return value.first().firstvalue;     }      public ilist<int> getnextids(int numberofids)     {         var values = _sequencevalueservice.selectquery("getsequenceids @numberofids", new sqlparameter("numberofids", sqldbtype.int) { value = numberofids }).tolist();         if (values.first() == null)         {             throw new exception("unable retrieve next id's sequence.");         }          var list = new list<int>();         (var = values.first().firstvalue; <= values.first().lastvalue; i++)         {             list.add(i);         }          return list;     } } 

which provides 2 ways ids, single , range.

this worked great during first set of unit tests started testing in real world scenario, realized single call getnextid() return same value life of context, until savechanges() called, negating real benefit.

i not sure if there way around short of creating second context (not option) or going old school ado.net , making direct sql calls , use automapper same net result. neither of these appeal me hoping else has idea.

don't know if might you, how did audit log trail using code first. following coded class inheriting dbcontext.

in constructor have following

iobjectcontextadapter objectcontextadapter = (this iobjectcontextadapter); objectcontextadapter.objectcontext.savingchanges += savingchanges; 

this saving changes method wired previously

void savingchanges(object sender, eventargs e) {         debug.assert(sender != null, "sender can't null");         debug.assert(sender objectcontext, "sender not instance of objectcontext");          objectcontext context = (sender objectcontext);         ienumerable<objectstateentry> modifiedentities = context.objectstatemanager.getobjectstateentries(entitystate.modified);         ienumerable<objectstateentry> addedentities = context.objectstatemanager.getobjectstateentries(entitystate.added);          addedentities.tolist().foreach(a => {             //assign ids objects don't have             if (a.entity iidentity && (a.entity iidentity).id == guid.empty)                 (a.entity iidentity).id = guid.newguid();              this.set<auditlogentry>().add(auditlogentryfactory(a, _addedentry));         });          modifiedentities.tolist().foreach(m => {             this.set<auditlogentry>().add(auditlogentryfactory(m, _modifiedentry));         });     } 

and these methods used previosly build audit log details

private auditlogentry auditlogentryfactory(objectstateentry entry, string entrytype) {         auditlogentry auditlogentry = new auditlogentry() {             entrydate = datetime.now,             entrytype = entrytype,             id = guid.newguid(),             newvalues = auditlogentrynewvalues(entry),             table = entry.entityset.name,             userid = _userid         };          if (entrytype == _modifiedentry) auditlogentry.originalvalues = auditlogentryoriginalvalues(entry);          return auditlogentry;     }      /// <summary>     /// creates string of modified properties entity.     /// </summary>     private string auditlogentryoriginalvalues(objectstateentry entry) {         stringbuilder stringbuilder = new stringbuilder();          entry.getmodifiedproperties().tolist().foreach(m => {             stringbuilder.append(string.format("{0} = {1},", m, entry.originalvalues[m]));         });          return stringbuilder.tostring();     }      /// <summary>     /// creates string of modified properties' new values entity.     /// </summary>     private string auditlogentrynewvalues(objectstateentry entry) {         stringbuilder stringbuilder = new stringbuilder();          (int = 0; < entry.currentvalues.fieldcount; i++) {             stringbuilder.append(string.format("{0} = {1},",                 entry.currentvalues.getname(i), entry.currentvalues.getvalue(i)));         }          return stringbuilder.tostring();     } 

hopefully might point direction might solve problem.