StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POEF4 Code First + SQL Server CE: save bidirectional reference atomically
text
Body
copied!<p>I want to save a few entities with a bidirectional relationship (navigation properties on both ends). This is accomplished by 2 calls to <code>context.SaveChanges()</code>.</p> <p><strong>[The complete details about my model, mappings, and how I got there are after the fold.]</strong></p> <pre><code>public void Save(){ var t = new Transfer(); var ti1 = new TransferItem(); var ti2 = new TransferItem(); //deal with the types with nullable FKs first t.TransferIncomeItem = ti1; t.TransferExpenseItem = ti2; context.Transfers.Add(t); context.Operations.Add(ti1); context.Operations.Add(ti2); //save, so all objects get assigned their Ids context.SaveChanges(); //set up the "optional" half of the relationship ti1.Transfer = t; ti2.Transfer = t; context.SaveChanges(); } </code></pre> <p>All's well, but how about making sure the database isn't inconsistent if lightning strikes beetween the two calls to <code>SaveChanges()</code> ?</p> <p>Enter <code>TransactionScope</code>...</p> <pre><code>public void Save(){ using (var tt = new TransactionScope()) { [...same as above...] tt.Complete(); } } </code></pre> <p>... but this fails on the first call to <code>context.SaveChanges()</code> with this error:</p> <blockquote> <p>The connection object can not be enlisted in transaction scope.</p> </blockquote> <p><a href="https://stackoverflow.com/questions/5153573/error-on-using-transactionscope-in-ef4-sql-compact-4">This question</a> and <a href="http://msdn.microsoft.com/en-us/library/bb896149%28v=sql.100%29.aspx" rel="nofollow noreferrer">this MSDN article</a> suggest I explicitely enlist the transaction...</p> <pre><code>public void Save(){ using (var tt = new TransactionScope()) { context.Database.Connection.EnlistTransaction(Transaction.Current); [...same as above...] tt.Complete(); } } </code></pre> <p>...same error:</p> <blockquote> <p>The connection object can not be enlisted in transaction scope.</p> </blockquote> <p>Dead end here... Let's go for a different approach - use an explicit transaction.</p> <pre><code>public void Save(){ using (var transaction = context.Database.Connection.BeginTransaction()) { try { [...same as above...] transaction.Commit(); } catch { transaction.Rollback(); throw; } } </code></pre> <p>Still no luck. This time, the error message is: </p> <blockquote> <p>BeginTransaction requires an open and available Connection. The connection's current state is Closed.</p> </blockquote> <p>How do I fix this?</p> <hr> <h2>TL;DR details</h2> <p>Here's my simplified model: a Transaction that references two operations (TransferItem) that reference back the transaction. <strong>This is a 1:1 mapping</strong> between Transfer and each of its two items.</p> <p>What I want is to make sure these are <strong>saved atomically</strong> when adding a new <code>Transfer</code>. </p> <p>Here's the path I've walked, and where I got stuck.</p> <p>The model:</p> <pre><code>public class Transfer { public long Id { get; set; } public long TransferIncomeItemId { get; set; } public long TransferExpenseItemId { get; set; } public TransferItem TransferIncomeItem { get; set; } public TransferItem TransferExpenseItem { get; set; } } public class Operation { public long Id; public decimal Sum { get; set; } } public class TransferItem: Operation { public long TransferId { get; set; } public Transfer Transfer { get; set; } } </code></pre> <p>I want to save this mapping to the database (SQL CE).</p> <pre><code>public void Save(){ var t = new Transfer(); var ti1 = new TransferItem(); var ti2 = new TransferItem(); t.TransferIncomeItem = ti1; t.TransferExpenseItem = ti2; context.Transfers.Add(t); context.Operations.Add(ti1); context.Operations.Add(ti2); context.SaveChanges(); } </code></pre> <p>This blows in my face with the error: </p> <blockquote> <p>"Unable to determine a valid ordering for dependent operations. Dependencies may exist due to foreign key constraints, model requirements, or store-generated values."</p> </blockquote> <p>Thsi is a chicken-and-egg problem. I can't save objects with non-nullable foreign keys, but in order to populate the foreign keys, I need to save the objects first.</p> <p>Looking at <a href="https://stackoverflow.com/questions/824635/same-table-relationship-in-entity-framework">this question</a> it seems that I have to relax my model, and:</p> <ul> <li>have nullable FKs on at least one side of the relationship</li> <li>save those objects first</li> <li>set up the relationship</li> <li>save again.</li> </ul> <p>Like this:</p> <pre><code>public class TransferItem: Operation { public Nullable<long> TransferId { get; set; } [etc] } </code></pre> <p>Also, here are the mappings. Morteza Manavi's <a href="http://weblogs.asp.net/manavi/archive/2011/05/01/associations-in-ef-4-1-code-first-part-5-one-to-one-foreign-key-associations.aspx" rel="nofollow noreferrer">article on EF 1:1 relationships</a> was <em>really</em> helpful. Basically, I need to create one-many relationships with a specified FK column. The 'CascadeOnDelete(false)' deals with an error about multiple cascade paths. (The DB may try to delete Transfer twice, once for each relationship)</p> <pre><code> modelBuilder.Entity<Transfer>() .HasRequired<TransferItem>(transfer => transfer.TransferIncomeItem) .WithMany() .HasForeignKey(x => x.TransferIncomeItemId) .WillCascadeOnDelete(false) ; modelBuilder.Entity<Transfer>() .HasRequired<TransferItem>(transfer => transfer.TransferExpenseItem) .WithMany() .HasForeignKey(x => x.TransferExpenseItemId) .WillCascadeOnDelete(false) ; </code></pre> <p>The updated code for saving the entities is at the beginning of the question.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload