EF4 Code First + SQL Server CE: save bidirectional reference atomically

Question

I want to save a few entities with a bidirectional relationship (navigation properties on both ends). This is accomplished by 2 calls to context.SaveChanges().

[The complete details about my model, mappings, and how I got there are after the fold.]

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();
} 

All's well, but how about making sure the database isn't inconsistent if lightning strikes beetween the two calls to SaveChanges() ?

Enter TransactionScope...

public void Save(){
    using (var tt = new TransactionScope())
    {
        [...same as above...]
        tt.Complete();
    }
}

... but this fails on the first call to context.SaveChanges() with this error:

The connection object can not be enlisted in transaction scope.

This question and this MSDN article suggest I explicitely enlist the transaction...

public void Save(){
    using (var tt = new TransactionScope())
    {
        context.Database.Connection.EnlistTransaction(Transaction.Current);

        [...same as above...]
        tt.Complete();
    }
}

...same error:

The connection object can not be enlisted in transaction scope.

Dead end here... Let's go for a different approach - use an explicit transaction.

public void Save(){
    using (var transaction = context.Database.Connection.BeginTransaction())
    {
        try
        {
            [...same as above...]
            transaction.Commit();
        }
        catch
        {
            transaction.Rollback();
            throw;
        }
    }

Still no luck. This time, the error message is:

BeginTransaction requires an open and available Connection. The connection's current state is Closed.

How do I fix this?

---

TL;DR details

Here's my simplified model: a Transaction that references two operations (TransferItem) that reference back the transaction. This is a 1:1 mapping between Transfer and each of its two items.

What I want is to make sure these are saved atomically when adding a new Transfer.

Here's the path I've walked, and where I got stuck.

The model:

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; }
}

I want to save this mapping to the database (SQL CE).

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();
}

This blows in my face with the error:

"Unable to determine a valid ordering for dependent operations. Dependencies may exist due to foreign key constraints, model requirements, or store-generated values."

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.

Looking at this question it seems that I have to relax my model, and:

• have nullable FKs on at least one side of the relationship
• save those objects first
• set up the relationship
• save again.

Like this:

public class TransferItem: Operation
{
    public Nullable<long> TransferId { get; set; }
    [etc]
}

Also, here are the mappings. Morteza Manavi's article on EF 1:1 relationships was really 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)

        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)
            ;

The updated code for saving the entities is at the beginning of the question.

Answer 1

To make this work, I had to add more fluent mappings, to explicitly create the optional mapping of the TransferItem on the Transfer class, as well as make the FK nullable on the TransferItem.

I didn't have a problem wrapping this all in a single TransactionScope, once the mappings were fixed.

Here's the entire console app:

  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 { get; set; }
      public decimal Sum { get; set; }
   }

   public class TransferItem : Operation
   {
      public long? TransferId { get; set; }
      public Transfer Transfer { get; set; }
   }

   public class Model : DbContext
   {

      public DbSet<Transfer> Transfers { get; set; }
      public DbSet<Operation> Operations { get; set; }
      public DbSet<TransferItem> TransferItems { get; set; }

      protected override void OnModelCreating( DbModelBuilder modelBuilder )
      {
         modelBuilder.Entity<Transfer>()
            .HasRequired( t => t.TransferIncomeItem )
            .WithMany()
            .HasForeignKey( x => x.TransferIncomeItemId )
            .WillCascadeOnDelete( false );

         modelBuilder.Entity<Transfer>()
            .HasRequired( t => t.TransferExpenseItem )
             .WithMany()
            .HasForeignKey( x => x.TransferExpenseItemId )
            .WillCascadeOnDelete( false );

         modelBuilder.Entity<TransferItem>()
            .HasOptional( t => t.Transfer )
            .WithMany()
            .HasForeignKey( ti => ti.TransferId );
      }
   }

   class Program
   {
      static void Main( string[] args )
      {

         using( var scope = new TransactionScope() )
         {
            var context = new Model();

            var ti1 = new TransferItem();
            var ti2 = new TransferItem();

            //deal with the types with nullable FKs first
            context.Operations.Add( ti1 );
            context.Operations.Add( ti2 );
            var t = new Transfer();
            context.Transfers.Add( t );
            t.TransferIncomeItem = ti1;
            t.TransferExpenseItem = 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();
            scope.Complete();
         }

      }
   }

When ran produced this database:

And this output: