I started using EF Core with Dapper and while trying to use them both inside a TransactionScope when I call context.SaveChanges() I get the error SqlConnection does not support parallel transactions.
The code is the following:
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)
{
}
}
}
}
How can I mix EF Core and Dapper operations in a single transactionscope successfully?
I found the solution to my problem for anyone having the same problem here is what I did.
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)
{
}
}
});
}
}