A SQL 2005 Database Design and Entity Framework transaction conundrum

Question

Hello. I have a database design issue that I am trying to also fit into Entity Framework. Its complex, I have inherited it and its too late to make drastic changes! I want to draw on others wisdom before I make a decision about how to go forwards...my original post was much longer, but I think the business logic details are probably not important.

So to state the problem simply, say I have two tables in the same database:

TableA

Id, (other columns...)

TableB

Id, TableId (<--- soft key), TableTypeId, (other columns...)

Now TableB has a "soft key" to table A, that normally could be a Foreign Key, but with the schema I have it isn't, and trust me it can't be (the reason is that "fake foreign key" points at any one of 76 different tables depending on what is in TableB's "TableTypeId" column).

Now, we are using Entity Framework. Clearly there cannot be a Navigation Property between the two tables. The problem is I need to work across these two tables in a single transaction. For example, when I add a record to TableA, I need to also add a record to TableB and use the new Identity value created in TableA for the TableB's "TableId" column.

With a Navigation Property, I can connect the two EntityObjects in memory and then call Context.SaveChanges() and EntityFramework does it all in a transaction. However without a Navigation Property I have to insert into TableA first, get the Identity and then insert into TableB using the results of the first insert. THis is two transactions...

I have heard that Entity Framework allows you do to more things with transactions in more complex situations, such as defining your own transaction scopes and using other parameters to COntext.SaveChanges(). Before I try this, however, I would like to know if my problem is solveable in a single transaction...

What options do I have here other than a schema redesign...

Thanks

Answer 1

Ouch...

In EF 4.0 you could override the save changes method, start a transaction manually, set the TableB model as unchanged, save Table A, AcceptAllChanges(), mark TableB as changed, then save TableB.

For EF1 you'll have to start an ambient transaction manually ( using var scope = new TransactionScope ) {}, and then do the same process. Add TableA to your context, of if already added mark TableB as unchanged, call SaveChanges ( hopefully only saving your TableA object ), call AcceptAllChanges(), update TableB as changed and add your soft key value, call SaveChanges again. Complete the transaction.

AcceptAllChanges() or ChangeObjectState() are your friends here:

http://msdn.microsoft.com/en-us/library/system.data.objects.objectstatemanager.changeobjectstate%28VS.100%29.aspx

http://msdn.microsoft.com/en-us/library/system.data.objects.objectcontext.acceptallchanges%28VS.100%29.aspx

In both cases doing to have to tell EF to ignore TableB, save TableA, and then care about TableB again all inside an ambient transaction. This is 100% possible ( I'm going something similar ) its just going to require some work.

Pseudo Code:

  using (TransactionScope transaction = new TransactionScope())
  {
        //Set TableB as unchanged so EF ignores it
      YourObjectContext.ObjectStateManager.ChangeObjectState(TableBObject,EntityState.Unchanged);

      YourObjectContext.SaveChanges();
      YourObjectContext.AcceptAllChanges();

      TableB.SoftFK = TableA.Fk;          

      YourObjectContext.ObjectStateManager.ChangeObjectState(TableBObject,EntityState.Modified);

      YourObjectContext.SaveChanges();
      YourObjectContext.AcceptAllChanges();

      Transaction.Complete();
  }