如何使用Entity Framework在TransactionScope中返回存储过程中完成的更新？

I have a stored procedure which I need to execute as part of a wider transaction scope using Entity Framework 4.0. Here is a quick demo of the scenario I have:

我有一个存储过程,我需要使用Entity Framework 4.0作为更广泛的事务范围的一部分执行。这是我的场景的快速演示:

private void button1_Click(object sender, EventArgs e)
{
    using (var scope = GetTransaction(IsolationLevel.Serializable))
    {
        var model = new SPUpdateTestEntities();

        var rows = model.TestTables.ToList();

        Debug.WriteLine("rows:");
        foreach (var row in rows)
        {
            Debug.WriteLine(row);
        }

        var random = new Random();

        var chars = "ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789";
        var result = new string(
            Enumerable.Repeat(chars, 8)
                        .Select(s => s[random.Next(s.Length)])
                        .ToArray());

        model.UpdateTestTable(rows[random.Next(rows.Count())].Id, result, random.Next());

        model.SaveChanges();

        var rowsAgain = model.TestTables.ToList();

        Debug.WriteLine("rowsAgain:");
        foreach (var row in rowsAgain)
        {
            Debug.WriteLine(row);
        }

        scope.Complete();
    }
}

private TransactionScope GetTransaction(IsolationLevel isolationLevel)
{
    var transactionOptions = new TransactionOptions();
    transactionOptions.IsolationLevel = isolationLevel;
    transactionOptions.Timeout = TransactionManager.MaximumTimeout;

    return new TransactionScope(TransactionScopeOption.Required, transactionOptions);
}

The purpose of the stored procedure UpdateTestTable is to both insert and update records in the TestTable.

存储过程UpdateTestTable的目的是插入和更新TestTable中的记录。

ALTER PROCEDURE [dbo].[UpdateTestTable]
    @Id AS INTEGER,
    @Text as VARCHAR(255),
    @Number AS INTEGER
AS
BEGIN
    INSERT INTO TestTable(Text, Number) 
    VALUES (CONVERT(varchar(255), NEWID()), ABS(CHECKSUM(NewId())) % 14)

    UPDATE TestTable 
    SET Text = @Text, Number = @Number 
    WHERE Id = @Id
END

The behavior I am expecting is for allRowsAgain to contain any modifications which were made in UpdateTestTable. The actualy behaviour I am getting is inserted records are returned but updates to exising records are not.

我期望的行为是allRowsAgain包含在UpdateTestTable中进行的任何修改。我得到的实际行为是插入的记录被返回但是对现有记录的更新不是。

I have tried the isolation levels Serializable (as shown), ReadComitted, and ReadUncomitted but this does not make a difference to the result as I hoped it would.

我已经尝试了Serializable(如图所示),ReadComitted和ReadUncomitted的隔离级别,但这并没有像我希望的那样对结果产生影响。

Edit: I've added some debug statements to make the results a little clearer, this is the output:

编辑:我添加了一些调试语句,使结果更清晰,这是输出:

First Run:

rows:
Id:2 - Number:1 - Text:Hello

rowsAgain:
Id:2 - Number:1 - Text:Hello
Id:19 - Number:1 - Text:0B22C83C-58E9-403C-96B4-FB3940E1F250

Second Run:

rows:
Id:2 - Number:2135368409 - Text:CQCXCTAY
Id:19 - Number:1 - Text:0B22C83C-58E9-403C-96B4-FB3940E1F250

rowsAgain:
Id:2 - Number:2135368409 - Text:CQCXCTAY
Id:19 - Number:1 - Text:0B22C83C-58E9-403C-96B4-FB3940E1F250
Id:20 - Number:8 - Text:D5A6684B-D140-415F-A81B-36705915FAF6

Thanks, Ant

1 个解决方案

#1

Entity Framework opens a new connection for each request unless you explicitly open the connection yourself. Therefore, multiple connections are enlisted in a distributed transaction here. Each connection sees a different view of the data. This is an anti-pattern.

除非您自己明确打开连接,否则实体框架会为每个请求打开一个新连接。因此,这里在分布式事务中登记了多个连接。每个连接都可以看到不同的数据视图。这是一种反模式。

This behavior is not useful and I have requested that the team changes it. EF should integrate with TS but at the moment integration is weak.

此行为没有用,我已请求团队更改它。 EF应该与TS集成,但目前集成很弱。

Open the connection manually before issuing the first command.

在发出第一个命令之前手动打开连接。

EF ensures that for each row there is at most one .NET object. When you query a row you always get the existing object. Here, it has old values. Lesson: Don't mix EF and SQL-based DML. You need to be very careful when doing that. Use a new EF context with the old connection or use MergeOption.

EF确保每行最多只有一个.NET对象。查询行时,您始终会获得现有对象。在这里,它有旧的价值观。课程:不要混用EF和基于SQL的DML。这样做时你需要非常小心。使用旧连接的新EF上下文或使用MergeOption。

#1