EF4 Code First + SQL Server CE: save bidirectional reference atomically
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:
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.
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:
This may be because two calls to SaveChanges cause the connection to be opened and closed twice. With some versions of SQL Server this causes the transaction to be promoted to a distributed transaction, which will fail if the service isn't running.
The easiest solution is to manually manage the connection by explicitly opening it prior to creating the TransactionScope . Then EF won't try to open or close the connection itself until it closes it when the context is disposed.
See this answer for a code sample and this and this blog post.
链接地址: http://www.djcxy.com/p/63066.html上一篇: 反射获取对象属性来对列表进行排序