CODEWITHSUNDEEP
c#sql-serverlinq
Bohn
Bohn

Reputation: 26919

Do I need to use MultipleActiveResultSets feautre?

My Linq to Entities queries are something like this:

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

Do I need MultipleActiveResultSets in my connection string? or It won't hurt if I take it out?

Upvotes: 1

Views: 462

Answers (1)

DrewJordan
DrewJordan

Reputation: 5314

For the example you gave, it should be fine to disable MARS, because you're eagerly loading (using ToList()). If you weren't, then you would need to leave it enabled.

If you were to forget to eagerly load, however, you would run into problems. You'd open the connection at the second foreach and execute a read, but leave the connection open, for the first element in query. Then you'd try to execute the second query (your second active result set) and it would fail, throwing a System.Data.Entity.Core.EntityCommandExecutionException, complaining:

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

You can test this out in a few minutes (I just did) by setting up a console app and trying it out. Here's my app: 

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();

    }
}

And the connection string, with the MARS setting left out: 

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

Upvotes: 4

Related Questions