c# - 我需要使用 MultipleActiveResultSets 功能吗？

Question

我的 Linq to Entities 查询是这样的:

ForEach(something)
{
   var query = (some DB query).ToList();
   for each(var x in query)
   {
       var query2 = some other DB query;
   }
} 

我的连接字符串中需要 MultipleActiveResultSets 吗？还是取出来就不疼了？

Answer 1

对于您给出的示例，禁用 MARS 应该没问题，因为您正在急切地加载(使用 ToList())。如果不是，则需要将其保持启用状态。

但是，如果您忘记预先加载，就会遇到问题。对于 query 中的第一个元素，您将在第二个 foreach 处打开连接并执行读取，但保持连接打开。然后你会尝试执行第二个查询(你的第二个事件结果集)，它会失败，抛出 System.Data.Entity.Core.EntityCommandExecutionException，提示:

There is already an open DataReader associated with this Command which must be closed first.

您可以在几分钟内(我刚刚做过)通过设置控制台应用程序并试用来对此进行测试。这是我的应用程序:

class Program
{
    static void Main(string[] args)
    {
        using (var db = new Config())
        {
            // foreach (var demo in db.Demos) uncomment this, and comment the below line, to make it throw
            foreach (var demo in db.Demos.ToList()) // doing this makes it work, since the first query is done and the connection closed
            {
                Console.WriteLine(demo.Name);
                var s = db.Demo2s.FirstOrDefault(d => d.id == demo.demo2ID);
                Console.WriteLine(s.Name + " " + s.id);
            }

            foreach (var demo2 in db.Demo2s)
            {
                Console.WriteLine(demo2.id + " " + demo2.Name);
            }
        }

        Console.ReadKey();
    }
}

public class Config : DbContext
{
    public Config()
        : base("test")
    { }

    public DbSet<demo> Demos { get; set; }
    public DbSet<demo2> Demo2s { get; set; }
}

internal sealed class Configuration : DbMigrationsConfiguration<ConsoleApplication1.Config>
{
    public Configuration()
    {
        AutomaticMigrationsEnabled = false;
    }

    protected override void Seed(ConsoleApplication1.Config context)
    {
        List<demo2> d2 = new List<demo2>
        {
            new demo2 {Name = "One"},
            new demo2 {Name = "Two"},
            new demo2 {Name = "Three"}
        };

        foreach (var demo in d2)
        {
            context.Demo2s.Add(demo);
        }

        context.SaveChanges();

        List<demo> e = new List<demo>
        {
            new demo {Name = "First", demo2ID = 1},
            new demo {Name = "Second", demo2ID = 2},
            new demo {Name = "Third" , demo2ID = 3}
        };

        foreach (var demo in e)
        {
            context.Demos.Add(demo);
        }

        context.SaveChanges();

    }
}

还有连接字符串，省略了 MARS 设置:

<connectionStrings>
    <add name="test" connectionString="Data Source=.;Initial Catalog=EFTesting;Integrated Security=SSPI;application name=EntityFramework" providerName="System.Data.SqlClient"/>
  </connectionStrings>