28

I have an ASP.Net Core application that uses EF Core.

I use ASP.Net Identity and share the same DBContext for my app's entities.

I have set my connection string to an Azure SQL database to have MultipleActiveResultSet=True.

It works for a day or two, but eventually it fails with the error:

The connection does not support MultipleActiveResultSets.

I don't think MARS is the real issue since it worked for the first two days it was up.

I am using ASP.Net Core's built-in DI to set my DbContext. 

services.AddDbContext<AppDbContext>(options =>
  options.UseSqlServer(appDbContextConnectionString));

My understanding is that the default lifetime for the DbContext above is Transient (per web request).

Is it alright to share the same DBContext with ASP.Net Identity or should I have a separate one for my app's entities, pointed to the same DB?

I don't know if this is an issue with EF Core, ASP.Net Core or with SQL Azure configuration.

Clashsoft
  • 11,553
  • 5
  • 40
  • 79
Jonas Arcangel
  • 2,085
  • 11
  • 55
  • 85
  • https://github.com/aspnet/EntityFrameworkCore/issues/6491 What does he mean by "Does the middleware have a scope per request?" – Jonas Arcangel Sep 04 '17 at 19:28
  • can you share the code where you inject or create your db context? – hugo Sep 08 '17 at 11:58
  • 4
    You might want to check if your connection string with "MultipleActiveResultSet=True" is actually used at runtime. Just a guess. https://stackoverflow.com/questions/12690453/sql-azure-getting-an-error-there-is-already-an-open-datareader-associated-wit – A.J.Bauer Sep 12 '17 at 05:19
  • _DbContext_ is not thread safe. Make sure that multiple threads are not using the same context instance. Anytime you call an `async` method in _EF_ the call **MUST** be awaited before making any other call into _EF_. – Mojtaba Nov 08 '18 at 22:00

5 Answers5

15

I had a similar issue and I found that the problem was that I missed an await statement in front of my method.

FetchStuffFromDBAsync();

Became:

await FetchStuffFromDBAsync();

And the problem was gone.

MattBH
  • 1,562
  • 3
  • 24
  • 31
6

MultipleActiveResultSets=True need to add in connection string.

string connectionString = "Data Source=MSSQL1;Initial Catalog=AdventureWorks;Integrated Security=SSPI;MultipleActiveResultSets=True";
Irvin Dominin
  • 30,819
  • 9
  • 77
  • 111
Sumit Ganguly
  • 69
  • 1
  • 3
  • 1
    Can you give more info on **why** it needs to be added though? Especially if it's intermittent and a new transient connection is created per request anyway – JvR Aug 26 '21 at 10:29
  • It can make your query too slow. You have to be hesitant about using this. – iman safari Jun 27 '23 at 11:00
3

Multiple Active Result Sets (MARS) is a feature that works with SQL Server to allow the execution of multiple batches on a single connection. When MARS is enabled for use with SQL Server, each command object used adds a session to the connection.

The MARS feature is disabled by default.

You can enable it like below.

string connectionString = "Data Source=MSSQL1;" +   
    "Initial Catalog=AdventureWorks;Integrated Security=SSPI;" +  
    "MultipleActiveResultSets=True";

Special Considerations When Using MARS

cdev
  • 5,043
  • 2
  • 33
  • 32
1

I kept running into this issue too, and setting MultipleActiveResultSet=True in the connection string didn't do much.

My db configuration in Startup.cs is very similar to what you have:

 services.AddEntityFrameworkSqlServer()
         .AddDbContext<MyDbContext>(options =>
           options.UseSqlServer(Configuration.GetConnectionString("DefaultConnection"),
            builder => builder.MigrationsAssembly("MyProject")),
            ServiceLifetime.Transient
         );

It turned out that I was getting

The connection does not support MultipleActiveResultSets

because I had several async db queries accessing the same entities. So one db query would retrieve an entity, and a second would have included the same entity through EF Core's new Include method. Also see https://stackoverflow.com/a/46164203/4336725

The reason I had several async db queries was because EF Core does not currently support lazy loading (unlike EF 6). https://github.com/aspnet/EntityFrameworkCore/issues/3797

My workaround was to define several IQueryables with different explicit Include() and ThenInclude().

June Lau
  • 151
  • 2
  • 9
-1

Remove "Connection Timeout" setting from your connection string.

Adi Bilauca
  • 181
  • 2
  • 10