我正在使用EF6查询一个后端数据库。用户可以自定义临时表,查询临时表中的数据。我正在使用
`DataTable result = context.Query(queryStatement);`
以获得结果,并且一直运行良好。
现在,在一系列其他the命令中需要查询,并且需要一个事务。所以我有
public static DataTable GetData()
{
using (MyDbContext context = new MyDbContext())
using (DbContextTransaction tran = context.Database.BeginTransaction())
{
try
{
int rowAffected = context.Database.ExecuteSqlCommand(
"UPDATE [MyDb].dbo.[TableLocks] SET RefCount = RefCount + 1 WHERE TableName = 'TESTTABLE1'");
if (rowAffected != 1)
throw new Exception("Cannot find 'TestTable1'");
//The following line will raise an exception
DataTable result = context.Query("SELECT TOP 100 * FROM [MyDb].dbo.[TestTable1]");
//This line will work if I change it to
//context.Database.ExecuteSqlCommand("SELECT TOP 100 * FROM [MyDb].dbo.[TestTable1]");
//but I don't know how to get the result out of it.
context.Database.ExecuteSqlCommand(
"UPDATE [MyDb].dbo.[TableLocks] SET RefCount = RefCount - 1 WHERE TableName = 'TestTable1'");
tran.Commit();
return result;
}
catch (Exception ex)
{
tran.Rollback();
throw (ex);
}
}
}
但这会在执行context.Query时抛出异常
ExecuteReader requires the command to have a transaction when the connection
assigned to the command is in a pending local transaction. The Transaction
property of the command has not been initialized.
当我读到这篇文章时:https://docs.microsoft.com/en-us/ef/ef6/saving/transactions,它说:
实体框架不在事务中包装查询。
这是导致这个问题的原因吗?
如何在事务中使用context.Query()
?
我还能用到什么?
我尝试了所有其他方法,但它们都不起作用-因为返回的数据类型无法预先预测。
我刚刚意识到,Query方法是在MyDbContext中定义的!
public DataTable Query(string sqlQuery)
{
DbProviderFactory dbFactory = DbProviderFactories.GetFactory(Database.Connection);
using (var cmd = dbFactory.CreateCommand())
{
cmd.Connection = Database.Connection;
cmd.CommandType = CommandType.Text;
cmd.CommandText = sqlQuery;
using (DbDataAdapter adapter = dbFactory.CreateDataAdapter())
{
adapter.SelectCommand = cmd;
DataTable dt = new DataTable();
adapter.Fill(dt);
return dt;
}
}
}
https://stackoverflow.com/questions/55622064
复制相似问题