Linq-to-SQL迁移EF Core时DataReader已打开异常解决咨询

更新说明：此代码用于服务器端数据导出流程，无需分页/分块操作，仅需流式处理多达数十万条数据。

我正在将旧版Linq-to-SQL代码/查询迁移至.NET 7 / EF Core，部分在L2S中正常运行的代码现在抛出如下异常：

已有一个与该连接关联的DataReader处于打开状态，必须先将其关闭。

我查阅资料后看到启用MARS的建议（但需谨慎考虑），也尝试过若干.Include语句，但均无效果。

数据库表结构 #

存在Profile（父表）-> HistoryData（子表）的关联关系，其中HistoryData.hispKey是Profile.pKey的外键，表结构如下：

CREATE TABLE [dbo].[Group](
    [gKey] [int] IDENTITY(1,1) NOT NULL,
    [gName] [nvarchar](255) NOT NULL,
    ....
)
CREATE TABLE [dbo].[Profile](
    [pKey] [int] IDENTITY(1,1) NOT NULL,
    [pgKey] [int] NOT NULL,
    [pAuthID] [nvarchar](255) NOT NULL,
    [pDateUpdated] [datetime2](7) NOT NULL,
    [pDateCreated] [datetime2](7) NOT NULL,
    [pUpdatedBy] [varchar](255) NOT NULL,
    [pCreatedBy] [varchar](255) NOT NULL
    [pProfile] [ntext] NOT NULL,
    [pProfileXml] [xml] NOT NULL
)
CREATE TABLE [dbo].[HistoryData](
    [hisKey] [int] IDENTITY(1,1) NOT NULL,
    [hisgKey] [int] NOT NULL,
    [hispKey] [int] NOT NULL,
    [hisType] [varchar](50) NOT NULL,
    [hisIndex] [varchar](255) NOT NULL,
    [hisDateUpdated] [datetime2](7) NOT NULL,
    [hisDateCreated] [datetime2](7) NOT NULL,
    [hisUpdatedBy] [varchar](255) NOT NULL,
    [hisCreatedBy] [varchar](255) NOT NULL,
    [hisData] [ntext] NOT NULL,
    [hisDataXml] [xml] NOT NULL
)

EF Core 查询代码 #

投影语句与Linq-to-SQL中的写法一致（仅将DataContext替换为DbContext）：

var profiles = dataContexts.xDS.Profiles
        .Where(p => p.GroupKey == gKey)
        .Where(p => options.AuthIdsToExport.Length == 0 || options.AuthIdsToExport.Contains(p.AuthID))
        .OrderBy(p => p.AuthID)
        .Select(p => new XmlProfileRow { 
             AuthId = p.AuthID, 
             Data = p.Data, 
             DataXml = p.ProfileXml, 
             DateCreated = p.DateCreated, 
             DateUpdated = p.DateUpdated 
         });

var historyDatas = dataContexts.xDS.HistoryDatas
        // .Include( h => h.Profile ) // 此操作无效
        .Where(h => h.GroupKey == gKey )
        .Where(h => options.AuthIdsToExport.Length == 0 || options.AuthIdsToExport.Contains(h.Profile!.AuthID))
        .OrderBy(h => h.Profile!.AuthID)
        .ThenBy(h => h.Type)
        .ThenBy(h => h.Index)
        .Select(h => new XmlHistoryRow { 
             AuthId = h.Profile!.AuthID, 
             DateCreated = h.DateCreated, 
             DateUpdated = h.DateUpdated, 
             Type = h.Type, 
             Index = h.Index, 
             Data = h.Data, 
             DataXml = h.DataXml 
         });

原本通过IEnumerator对象使用这两个查询，我绝对不能将所有数据加载到内存中，需要流式遍历Profiles的结果，并针对每一行流式遍历对应的History Data行，目的是避免为每个Profile的HistoryData行发起新查询。

问题复现代码 #

var profileEnumerator = profiles.GetEnumerator();
var historyEnumerator = historyDatas.GetEnumerator();
var currentHistory = historyEnumerator.MoveNext();
var currentProfile= profileEnumerator.MoveNext(); // 此行抛出异常

异常调用栈 #

at Microsoft.Data.SqlClient.SqlInternalConnectionTds.ValidateConnectionForExecute(SqlCommand command)  
at Microsoft.Data.SqlClient.SqlCommand.ValidateCommand(Boolean isAsync, String method)  
at Microsoft.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, TaskCompletionSource'1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry, String method)  
at Microsoft.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior)  
at Microsoft.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)  
at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReader(RelationalCommandParameterObject parameterObject)  
at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable'1.Enumerator.InitializeReader(Enumerator enumerator)  
at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable'1.Enumerator.<>c.<MoveNext>b__21_0(DbContext _, Enumerator enumerator)  
at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerExecutionStrategy.Execute[TState,TResult](TState state, Func'3 operation, Func'3 verifySucceeded)  
at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable'1.Enumerator.MoveNext()  
at UserQuery.ExportDataAsync(String groupName, DataOptions options, DataContexts dataContexts), line 154  

生成的SQL语句 #

抛出异常前生成的SQL如下（逻辑正确）：

info: 10/21/2024 10:30:59.384 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command)       
Executed DbCommand (1ms) [Parameters=[@__gKey_0='1009' (Nullable = true)], CommandType='Text', CommandTimeout='30']      
SELECT [p].[pAuthID] AS [AuthId], [h].[hisDateCreated] AS [DateCreated], [h].[hisDateUpdated] AS [DateUpdated], [h].[hisType] AS [Type], [h].[hisIndex] AS [Index], [h].[hisData] AS [Data], [h].[hisDataXml] AS [DataXml]      
FROM [HistoryData] AS [h]      
LEFT JOIN [Profile] AS [p] ON [h].[hispKey] = [p].[pKey]      
WHERE [h].[hisgKey] = @__gKey_0      
ORDER BY [p].[pAuthID], [h].[hisType], [h].[hisIndex]

fail: 10/21/2024 10:30:59.385 RelationalEventId.CommandError[20102] (Microsoft.EntityFrameworkCore.Database.Command)       
Failed executing DbCommand (0ms) [Parameters=[@__gKey_0='1009' (Nullable = true)], CommandType='Text', CommandTimeout='30']      
SELECT [p].[pAuthID] AS [AuthId], [p].[pProfile] AS [Data], [p].[pProfileXml] AS [DataXml], [p].[pDateCreated] AS [DateCreated], [p].[pDateUpdated] AS [DateUpdated]      
FROM [Profile] AS [p]      
WHERE [p].[pgKey] = @__gKey_0      
ORDER BY [p].[pAuthID]

解决方案 #

方法1：启用MARS（多活动结果集） #

这是最直接的解决方式，问题本质是同一个数据库连接上同时打开了两个DataReader。只需在连接字符串中添加MultipleActiveResultSets=True即可。

• MARS是SQL Server原生支持的特性，不会破坏现有业务逻辑
• 针对流式处理大量数据的场景，MARS性能表现稳定，无需过度担忧兼容性问题

方法2：使用独立DbContext实例 #

为两个查询分别创建独立的DbContext实例，让每个查询使用各自的数据库连接，从根源避免DataReader冲突：

using var profileContext = new YourDbContext();
var profiles = profileContext.Profiles
    .Where(p => p.GroupKey == gKey && (options.AuthIdsToExport.Length == 0 || options.AuthIdsToExport.Contains(p.AuthID)))
    .OrderBy(p => p.AuthID)
    .Select(p => new XmlProfileRow { ... });

using var historyContext = new YourDbContext();
var historyDatas = historyContext.HistoryDatas
    .Where(h => h.GroupKey == gKey && (options.AuthIdsToExport.Length == 0 || options.AuthIdsToExport.Contains(h.Profile!.AuthID)))
    .OrderBy(h => h.Profile!.AuthID)
    .ThenBy(h => h.Type)
    .ThenBy(h => h.Index)
    .Select(h => new XmlHistoryRow { ... });

var profileEnumerator = profiles.GetEnumerator();
var historyEnumerator = historyDatas.GetEnumerator();
// 后续流式遍历逻辑

这种方式无需修改连接字符串，适合无法启用MARS的场景，但会增加数据库连接数，需注意调整连接池配置。

方法3：合并查询并流式处理 #

如果业务逻辑允许，可将两个查询合并为一个关联查询，一次性获取所有数据后在内存中分组处理，全程仅使用一个DataReader：

var combinedData = profileContext.Profiles
    .Where(p => p.GroupKey == gKey && (options.AuthIdsToExport.Length == 0 || options.AuthIdsToExport.Contains(p.AuthID)))
    .OrderBy(p => p.AuthID)
    .SelectMany(p => p.HistoryDatas
        .Where(h => h.GroupKey == gKey)
        .OrderBy(h => h.Type)
        .ThenBy(h => h.Index)
        .Select(h => new {
            Profile = new XmlProfileRow { 
                AuthId = p.AuthID, 
                Data = p.Data, 
                DataXml = p.ProfileXml, 
                DateCreated = p.DateCreated, 
                DateUpdated = p.DateUpdated 
            },
            History = new XmlHistoryRow { 
                AuthId = p.AuthID, 
                DateCreated = h.DateCreated, 
                DateUpdated = h.DateUpdated, 
                Type = h.Type, 
                Index = h.Index, 
                Data = h.Data, 
                DataXml = h.DataXml 
            }
        })
        .DefaultIfEmpty() // 保留无历史数据的Profile
    );

foreach (var item in combinedData)
{
    // 流式处理Profile及对应的History数据
}

这种方式彻底避免了DataReader冲突，同时保持流式处理特性，需确保EF Core能生成高效的关联SQL，避免N+1查询问题。

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