实体框架4和事务：未提交的更改会影响事务中的选择结果？ [英] Entity Framework 4 and Transactions: Do uncommitted changes affect select results within a transaction?

问题描述

如果我使用Entity Framework 4进行交易，那么在修改后的集合中选择是否会反映交易中新保存的客户ID，否则会为每个客户生成相同的ID？

If I use transactions with Entity Framework 4 like so, will selects on the modified set reflect the newly saved customer IDs within the transaction, or will this generate the same id for each customer?

using ( System.Data.Common.DbTransaction trans = new DbTransaction() )
{
    foreach( var customer in CustomersToSave )
    {  
        // Calculate lowest ID available
        var id_Query =  (from c in Customers
                     select c.ID).Distinct();
        var lowest_ID_Available = 
            (from p1 in id_Query
             from p2 in id_Query.Where(a => a == p1 + 1).DefaultIfEmpty()
             where p2 == 0
             select p1).Min();

        ... Create a customer with the lowest_ID_Available
    }

    trans.Commit()
}

推荐答案

只有在调用 SaveChanges 在添加每个客户这是非常糟糕的解决方案后。此外，这不会在事务中运行，因为 DbTransaction 单独不起作用，直到从 DbConnection 通过调用的BeginTransaction 。使用 TransactionScope 。

This will work only if you call SaveChanges after adding each customer which is pretty bad solution. Also this will not run in transaction because DbTransaction alone doesn't work until created from DbConnection by calling BeginTransaction. Use TransactionScope instead.

另一个问题是并发。根据您的事务隔离级别，其他并发事务将等待此一个完成，或者可以使用与当前事务相同的ids。

Another issue is concurrency. Depending on your transaction isolation level other concurrent transactions will either wait for this one to complete or will be able to use same ids as the current transaction.

这通常由单独的表只包含计数器。您将创建存储过程以获取给定键的下一个计数器（表名）。该过程将使用原子更新，它将同时设置返回值和递增当前计数器。

This is usually handled by separate table which contains only counters. You will create stored procedure to get next counter for given key (table name). The procedure will use atomic update which will set return value and increment current counter in the same time.

此过程的使用取决于您的要求。如果您需要生成的数字的连续序列（没有回滚的孔），那么您必须仅在当前事务中锁定此特殊表中的记录。如果你的要求只是获得唯一的号码，但是你不需要有没有孔的序列，你可以使用并发访问。

Usage of this procedure depends on your requirements. If you require continuos sequence of generated numbers (without holes from rollbacks) then you must lock the record in this special table only for current transaction. If your requirement is only to get unique number but you don't need to have sequence without holes you can use concurrent access.

存储过程锁定记录的示例（if您不需要在update语句中锁定删除提示）：

Example of stored procedure locking the record (if you don't need locking remove hints in update statement):

CREATE PROCEDURE [dbo].[GetNextSequenceValue]
    @SequenceType VARCHAR(20)
AS
BEGIN
    DECLARE @Result INT

    UPDATE [dbo].[Sequences] WITH (ROWLOCK, UPDLOCK)
    SET @Result = Value = Value + 1
    WHERE SequenceType = @SequenceType

    RETURN @Result
END

这篇关于实体框架4和事务：未提交的更改会影响事务中的选择结果？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！