Open Data Reader报错：如何修改foreach循环规避该错误

遇到Open Data Reader错误，问题出在foreach循环中，错误触发于循环内的await _context.SaveChangesAsync();语句。需求是在循环中从第一个数据库读取数据并复制到第二个数据库，不使用MARS，通过修改现有代码解决问题。

原代码 #

//first add one
var xOne = _context.PostOnes.First(m => m.OneZero == zero);
ClonePost one = new ClonePost();
one.PostNumber = xOne.OneZero;
one.Type = "1";
one.ItemNumber = xOne.OneId;
one.ItemData = xOne.OneStag + "_"+ xOne.OnePart + "_" + xOne.OneTitl;
_context.Add(one);
await _context.SaveChangesAsync();

//add 4 in loop
foreach (PostFou postFou in _context.PostFous.Where(m => m.FouZero == zero))
{
    ClonePost fou = new ClonePost();
    fou.PostNumber = postFou.FouZero;
    fou.Type = "4"; ;
    fou.ItemNumber = postFou.FouId;
    fou.ItemData = postFou.FouDigit + "_" + postFou.FouType + "_" + postFou.FouName + "_" + postFou.FouPhon + "_" + postFou.FouEmai + "_" + postFou.FouAddr
        + "_" + postFou.FouLocality + "_" + postFou.FouAdminDistrict + "_" + postFou.FouPost + "_" + postFou.FouOrg + "_" + postFou.FouChec;
    _context.Add(fou);
    await _context.SaveChangesAsync();
}

////add 5 in loop
//foreach(PostFiv postFiv in _context.PostFivs.Where(m => m.FivZero == zero)) 
//{
//    ClonePost fiv = new ClonePost();
//    fiv.PostNumber = postFiv.FivZero;
//    fiv.Type = "4";
//    fiv.ItemNumber = postFiv.FivId;
//    fiv.ItemData = postFiv.FivPrio + "_" + postFiv.FivCode + "_" + postFiv.FivText;
//    _context.Add(fiv);
//    await _context.SaveChangesAsync();
//}

错误详情 #

Microsoft.Data.SqlClient.SqlInternalConnectionTds.ValidateConnectionForExecute(SqlCommand command)
Microsoft.Data.SqlClient.SqlInternalConnection.BeginSqlTransaction(IsolationLevel iso, string transactionName, bool shouldReconnect)
Microsoft.Data.SqlClient.SqlConnection.BeginTransaction(IsolationLevel iso, string transactionName)
Microsoft.Data.SqlClient.SqlConnection.BeginTransaction(IsolationLevel iso)
Microsoft.Data.SqlClient.SqlConnection.BeginDbTransaction(IsolationLevel isolationLevel)
System.Data.Common.DbConnection.BeginDbTransactionAsync(IsolationLevel isolationLevel, CancellationToken cancellationToken)
System.Threading.Tasks.ValueTask<TResult>.get_Result()
System.Runtime.CompilerServices.ValueTaskAwaiter<TResult>.GetResult()
Microsoft.EntityFrameworkCore.Storage.RelationalConnection.BeginTransactionAsync(IsolationLevel isolationLevel, CancellationToken cancellationToken)
Microsoft.EntityFrameworkCore.Storage.RelationalConnection.BeginTransactionAsync(CancellationToken cancellationToken)
Microsoft.EntityFrameworkCore.Update.Internal.BatchExecutor.ExecuteAsync(IEnumerable<ModificationCommandBatch> commandBatches, IRelationalConnection connection, CancellationToken cancellationToken)
Microsoft.EntityFrameworkCore.Update.Internal.BatchExecutor.ExecuteAsync(IEnumerable<ModificationCommandBatch> commandBatches, IRelationalConnection connection, CancellationToken cancellationToken)
Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChangesAsync(IList<IUpdateEntry> entriesToSave, CancellationToken cancellationToken)
Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChangesAsync(DbContext _, bool acceptAllChangesOnSuccess, CancellationToken cancellationToken)
Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerExecutionStrategy.ExecuteAsync<TState, TResult>(TState state, Func<DbContext, TState, CancellationToken, Task<TResult>> operation, Func<DbContext, TState, CancellationToken, Task<ExecutionResult<TResult>>> verifySucceeded, CancellationToken cancellationToken)
Microsoft.EntityFrameworkCore.DbContext.SaveChangesAsync(bool acceptAllChangesOnSuccess, CancellationToken cancellationToken)
AURA.Controllers.CloneController.ReviewClone(string zero) in CloneController.cs
+
                await _context.SaveChangesAsync();
Microsoft.AspNetCore.Mvc.Infrastructure.ActionMethodExecutor+TaskOfIActionResultExecutor.Execute(IActionResultTypeMapper mapper, ObjectMethodExecutor executor, object controller, object[] arguments)
System.Threading.Tasks.ValueTask<TResult>.get_Result()
System.Runtime.CompilerServices.ValueTaskAwaiter<TResult>.GetResult()
Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.<InvokeActionMethodAsync>g__Awaited|12_0(ControllerActionInvoker invoker, ValueTask<IActionResult> actionResultValueTask)
Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.<InvokeNextActionFilterAsync>g__Awaited|10_0(ControllerActionInvoker invoker, Task lastTask, State next, Scope scope, object state, bool isCompleted)
Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Rethrow(ActionExecutedContextSealed context)
Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Next(ref State next, ref Scope scope, ref object state, ref bool isCompleted)
Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.<InvokeInnerFilterAsync>g__Awaited|13_0(ControllerActionInvoker invoker, Task lastTask, State next, Scope scope, object state, bool isCompleted)
Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeNextResourceFilter>g__Awaited|24_0(ResourceInvoker invoker, Task lastTask, State next, Scope scope, object state, bool isCompleted)
Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.Rethrow(ResourceExecutedContextSealed context)
Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.Next(ref State next, ref Scope scope, ref object state, ref bool isCompleted)
Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeFilterPipelineAsync>g__Awaited|19_0(ResourceInvoker invoker, Task lastTask, State next, Scope scope, object state, bool isCompleted)
Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeAsync>g__Awaited|17_0(ResourceInvoker invoker, Task task, IDisposable scope)
Microsoft.AspNetCore.Routing.EndpointMiddleware.<Invoke>g__AwaitRequestTask|6_0(Endpoint endpoint, Task requestTask, ILogger logger)
Microsoft.AspNetCore.Authorization.AuthorizationMiddleware.Invoke(HttpContext context)
Microsoft.AspNetCore.Authentication.AuthenticationMiddleware.Invoke(HttpContext context)
Microsoft.AspNetCore.Diagnostics.EntityFrameworkCore.MigrationsEndPointMiddleware.Invoke(HttpContext context)
Microsoft.AspNetCore.Diagnostics.EntityFrameworkCore.DatabaseErrorPageMiddleware.Invoke(HttpContext httpContext)
Microsoft.AspNetCore.Diagnostics.EntityFrameworkCore.DatabaseErrorPageMiddleware.Invoke(HttpContext httpContext)
Microsoft.AspNetCore.Diagnostics.DeveloperExceptionPageMiddleware.Invoke(HttpContext context)

解决方案 #

核心原因 #

foreach直接遍历EF的IQueryable时，数据是延迟加载的，数据库连接会保持打开状态。此时调用SaveChangesAsync会尝试在同一个连接上启动新的事务，导致Open Data Reader冲突。

修改方案 #

1. 提前加载数据到内存：将查询结果转为List，一次性加载所有数据到内存，释放数据库连接。
2. 批量提交变更：不在循环内每次调用SaveChangesAsync，而是先将所有待添加的ClonePost对象收集完成后，一次性提交，减少数据库交互次数。

修改后的代码 #

//first add one
var xOne = _context.PostOnes.First(m => m.OneZero == zero);
ClonePost one = new ClonePost();
one.PostNumber = xOne.OneZero;
one.Type = "1";
one.ItemNumber = xOne.OneId;
one.ItemData = $"{xOne.OneStag}_{xOne.OnePart}_{xOne.OneTitl}";
_context.Add(one);
await _context.SaveChangesAsync();

//add 4 in loop - 先加载所有数据到内存
var postFous = _context.PostFous.Where(m => m.FouZero == zero).ToList();
foreach (PostFou postFou in postFous)
{
    ClonePost fou = new ClonePost();
    fou.PostNumber = postFou.FouZero;
    fou.Type = "4";
    fou.ItemNumber = postFou.FouId;
    fou.ItemData = $"{postFou.FouDigit}_{postFou.FouType}_{postFou.FouName}_{postFou.FouPhon}_{postFou.FouEmai}_{postFou.FouAddr}_{postFou.FouLocality}_{postFou.FouAdminDistrict}_{postFou.FouPost}_{postFou.FouOrg}_{postFou.FouChec}";
    _context.Add(fou);
}
// 批量提交
await _context.SaveChangesAsync();

////add 5 in loop - 同理修改
//var postFivs = _context.PostFivs.Where(m => m.FivZero == zero).ToList();
//foreach(PostFiv postFiv in postFivs) 
//{
//    ClonePost fiv = new ClonePost();
//    fiv.PostNumber = postFiv.FivZero;
//    fiv.Type = "4";
//    fiv.ItemNumber = postFiv.FivId;
//    fiv.ItemData = $"{postFiv.FivPrio}_{postFiv.FivCode}_{postFiv.FivText}";
//    _context.Add(fiv);
//}
//await _context.SaveChangesAsync();

额外优化 #

• 使用字符串插值（$""）替代字符串拼接，提升代码可读性和性能。
• 批量提交不仅避免了连接冲突，还减少了数据库往返次数，提升整体执行效率。

内容的提问来源于stack exchange，提问作者Nick Fleetwood