C# 9和.NET 5中Linq GroupJoin报错：存在打开的DataReader

运行以下代码时遇到异常：

static void GroupJoinCategoriesAndProducts()
{
    using (var db = new Northwind())
    {
        var queryGroup = db.Categories
            .AsEnumerable()
            .GroupJoin(
                inner: db.Products,
                outerKeySelector: category => category.CategoryId,
                innerKeySelector: product => product.CategoryID,
                resultSelector: (c, matchingProducts) => new
                {
                    c.CategoryName,
                    Products = matchingProducts.OrderBy(p => p.ProductName)
                });

        foreach(var item in queryGroup)
        {
            WriteLine("{0} has {1} products.",
                arg0: item.CategoryName,
                arg1: item.Products.Count());
            foreach (var product in item.Products)
            {
                WriteLine($" {product.ProductName}");
            }
        }
    }
}

触发的异常信息：

System.InvalidOperationException
  HResult=0x80131509
  Message="There is already an open DataReader associated with this Connection which must be closed first."
  Source=Microsoft.Data.SqlClient
  StackTrace:
   em Microsoft.Data.SqlClient.SqlInternalConnectionTds.ValidateConnectionForExecute(SqlCommand command)
   em Microsoft.Data.SqlClient.SqlConnection.ValidateConnectionForExecute(String method, SqlCommand command)
   em Microsoft.Data.SqlClient.SqlCommand.ValidateCommand(Boolean isAsync, String method)
   em 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)
   em Microsoft.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
   em Microsoft.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior)
   em Microsoft.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
   em System.Data.Common.DbCommand.ExecuteReader()
   em Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReader(RelationalCommandParameterObject parameterObject)
   em Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.Enumerator.InitializeReader(DbContext _, Boolean result)
   em Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerExecutionStrategy.Execute[TState,TResult](TState state, Func`3 operation, Func`3 verifySucceeded)
   em Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.Enumerator.MoveNext()
   em System.Linq.Lookup`2.CreateForJoin(IEnumerable`1 source, Func`2 keySelector, IEqualityComparer`1 comparer)
   em System.Linq.Enumerable.<GroupJoinIterator>d__98`4.MoveNext()
   em LinqWithEFCore.Program.GroupJoinCategoriesAndProducts() em C:\Users\benhu\source\repos\TesteLinq\LinqWithEFCore\Program.cs:linha 29
   em LinqWithEFCore.Program.Main(String[] args) em C:\Users\benhu\source\repos\TesteLinq\LinqWithEFCore\Program.cs:linha 91

原因分析 #

问题出在AsEnumerable()和延迟加载的组合使用：

• db.Categories.AsEnumerable()会立即把所有分类加载到内存，但db.Products仍是IQueryable，属于延迟加载状态。
• 遍历每个分类时，访问matchingProducts.Count()和foreach (var product in item.Products)会为每个分类单独发起一次数据库查询，此时之前的DataReader尚未关闭，导致同一连接上存在多个活跃的DataReader，触发异常。

解决方法 #

方法1：将Products提前加载到内存 #

把db.Products转为内存集合，让GroupJoin完全在本地执行，避免多次发起数据库查询：

static void GroupJoinCategoriesAndProducts()
{
    using (var db = new Northwind())
    {
        var categories = db.Categories.ToList();
        var products = db.Products.ToList();
        
        var queryGroup = categories
            .GroupJoin(
                inner: products,
                outerKeySelector: category => category.CategoryId,
                innerKeySelector: product => product.CategoryID,
                resultSelector: (c, matchingProducts) => new
                {
                    c.CategoryName,
                    Products = matchingProducts.OrderBy(p => p.ProductName)
                });

        foreach(var item in queryGroup)
        {
            WriteLine("{0} has {1} products.",
                arg0: item.CategoryName,
                arg1: item.Products.Count());
            foreach (var product in item.Products)
            {
                WriteLine($" {product.ProductName}");
            }
        }
    }
}

方法2：使用EF Core关联预加载 #

利用Category实体的Products导航属性（需提前配置），一次性加载所有分类及其关联产品，减少数据库交互次数：

static void GroupJoinCategoriesAndProducts()
{
    using (var db = new Northwind())
    {
        var queryGroup = db.Categories
            .Include(c => c.Products) // 预加载关联产品
            .Select(c => new
            {
                c.CategoryName,
                Products = c.Products.OrderBy(p => p.ProductName)
            })
            .ToList(); // 一次性加载所有数据到内存

        foreach(var item in queryGroup)
        {
            WriteLine("{0} has {1} products.",
                arg0: item.CategoryName,
                arg1: item.Products.Count());
            foreach (var product in item.Products)
            {
                WriteLine($" {product.ProductName}");
            }
        }
    }
}

方法3：启用MultipleActiveResultSets（MARS） #

在数据库连接字符串中添加MultipleActiveResultSets=True，允许同一连接上同时存在多个DataReader，适合需要保留延迟加载的场景：

Server=.;Database=Northwind;Trusted_Connection=True;MultipleActiveResultSets=True;

修改后原代码可正常运行，但该方式会增加连接资源消耗，需根据业务场景选择。

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