Dapper和EF核心事务失败，并显示错误SqlConnection不支持并行事务

Question

我开始使用EF核心和Dapper，当我尝试在TransactionScope中使用它们时，当我调用context.SaveChanges()时，我得到了错误SqlConnection does not support parallel transactions.，代码如下：

public class TestController : ControllerBase
{
    private readonly MyDbContext dbContext;
    private readonly IDbConnection dbConnection;

    public TestController(MyDbContext dbContext,
        IDbConnection dbConnection)
    {
        this.dbContext = dbContext;
        this.dbConnection = dbConnection;
    }

    public void Test2()
    {
        using (IDbTransaction transaction = dbConnection.BeginTransaction())
        {
            Client client = new Client();
            client.ClientId = 3;
            client.Name = "New Client 3";

            dbContext.Entry<Client>(client).State = EntityState.Modified;

            List<Client> clients = new List<Client>();
            clients.Add(new Client
            {
                ClientId = 1,
                Name = "New Client 1",
            });
            clients.Add(new Client
            {
                ClientId = 2,
                Name = "New Client 2",
            });
            clients.Add(new Client
            {
                ClientId = 4,
                Name = "New Client 4",
            });

            string sql = "UPDATE Client SET Name = @Name WHERE ClientId = @ClientId;";

            try
            {
                dbConnection.Execute(sql, clients, transaction: transaction);

                dbContext.SaveChanges();

                transaction.Commit();
            }
            catch (System.Exception ex)
            {

            }
        }
    }
}

如何在单个事务作用域中成功地混合EF Core和Dapper操作？

Answer 1

我在这里为所有有同样问题的人找到了解决问题的方法。

public class TestController : ControllerBase
{
    private readonly MyDbContext dbContext;
    private readonly DbConnection dbConnection;

    public TestController(MyDbContext dbContext,
        DbConnection dbConnection)
    {
        this.dbContext = dbContext;
        this.dbConnection = dbConnection;
    }

    public void Test2()
    {
        IExecutionStrategy strategy = dbContext.Database.CreateExecutionStrategy();
        strategy.Execute(() =>
        {
            using (DbTransaction transaction = dbConnection.BeginTransaction())
            {
                Client client = new Client();
                client.ClientId = 3;
                client.Name = "New Client 3";

                dbContext.Database.UseTransaction(transaction);

                dbContext.Entry<Client>(client).State = EntityState.Modified;

                List<Client> clients = new List<Client>();
                clients.Add(new Client
                {
                    ClientId = 1,
                    Name = "New Client 1",
                });
                clients.Add(new Client
                {
                    ClientId = 2,
                    Name = "New Client 2",
                });
                clients.Add(new Client
                {
                    ClientId = 4,
                    Name = "New Client 4",
                });

                string sql = "UPDATE Client SET Name = @Name WHERE ClientId = @ClientId;";

                try
                {
                    dbContext.SaveChanges();

                    dbConnection.Execute(sql, clients, transaction: transaction);

                    transaction.Commit();
                }
                catch (System.Exception ex)
                {

                }
            }
        });
    }
}

Answer 2

在dotnet core 3.1中，您可以尝试以下操作。但它只适用于关系数据库。

context.Database.UseTransaction((DbTransaction)transaction);
context.SaveChanges();
transaction.Commit();

Answer 3

下面我让你的代码更容易阅读。

为什么要混合使用与数据库通信的方式？

我已经留下了评论，这将帮助你确定真正的问题所在。

ps原因是您先打开一个事务，然后再打开另一个事务...using (IDbTransaction transaction是开放的，_dbContext.SaveChanges();也是事务。

public class TestController : ControllerBase
{
    private readonly MyDbContext _dbContext;
    private readonly IDbConnection _dbConnection;

    public TestController(MyDbContext dbContext, IDbConnection dbConnection)
    {
        _dbContext = dbContext;
        _dbConnection = dbConnection;
    }

    public void Test2()
    {
        var client = new Client();
        client.ClientId = 3;
        client.Name = "New Client 3";

        //is the correct way to attach and entrity mark as modified.
         _dbContext.Entry<Client>(client).State = EntityState.Modified;
         _dbContext.SaveChanges();

        var clients = new List<Client>();
        clients.Add(new Client { ClientId = 1, Name = "New Client 1", });
        clients.Add(new Client { ClientId = 2, Name = "New Client 2", });
        clients.Add(new Client { ClientId = 4, Name = "New Client 4", });

        using (IDbTransaction transaction = _dbConnection.BeginTransaction())
        {
            string sql = "UPDATE Client SET Name = @Name WHERE ClientId = @ClientId;";
            //is this the correct way to call dapper
            _dbConnection.Execute(sql, clients, transaction: transaction);
            transaction.Commit();
        }
    }
}