InvalidOperationException after a SqlException

[qin-nz]

Steps to reproduce

Run an ASP.NET Core 1.0 web app with Entity Framework Core on Azure Web App. And the Web Project and the EF Project(or lib project) are separately.

The issue

I'm running an ASP.NET Core 1.0 web app with Entity Framework Core. And then a SqlException is occur. (It is my fault to case this exception.)

My issuse is: After above exception, the app starts crashing on every request which need a context. And throwing the error

System.InvalidOperationException:   
ExecuteReader requires an open and available Connection. The connection's current state is closed. 

I can only recover from this by restarting the Azure App.

Further technical details

EF Core version: 1.0.0 Operating system: Azure Web App Visual Studio version: VS2015

Other details about my project setup:

[ajcvickers]

@qin-nz Can you post the code you have for calling AddDbContext and overriding OnConfiguring, if you are using these things? Also, can you show or describe how you are creating DbContext instances?

[qin-nz]

Code for calling AddDbContext

            var connection = Configuration.GetConnectionString("DefaultConnection");
            services.AddDbContext<Email2RssContext>(
                options => options.UseSqlServer(connection,
                b => b.MigrationsAssembly("Email2Rss.Core")));

All of DbContext instances are created by Dependency Injection. The following one is created in a Middleware.

  private readonly Email2RssContext _context;
  public V1Authorize(RequestDelegate next, ILogger<V1Authorize> logger, Email2RssContext context, IHostingEnvironment environment)
        {
            _next = next;
            _logger = logger;
            _context = context;
            _environment = environment;
        }
  public async Task Invoke(HttpContext httpContext)
        {
            string key = await _context.Users     // NOTICE HERE!
                .AsNoTracking()
                .Where(u => u.UserName == userName)
                .Select(u => u.RssAccessToken)
                .FirstOrDefaultAsync();
...

And I will got NullReferenceException at // NOTICE HERE! for only some request.

[qin-nz]

Except that exception, I could also get following two exceptions.

The connection does not support MultipleActiveResultSets.

The connection was not closed. The connection's current state is connecting.

[ajcvickers]

@qin-nz When you add DbContext in that way it is registered as a scoped service. Have scopes been created for all the places you get the context injected?

Also, DbContext is not thread safe. Have you ensured that multiple threads are not using the same context instance?

[qin-nz]

hi @ajcvickers , the only way I use DbContext is create it using controller(or middleware)'s constructor. I have one context instant for each controller instant. And I am not always using await/async. Is it okay?

[ajcvickers]

@qin-nz Does the middleware have a scope per request?

Anytime you call an async method in EF the call must be awaited before making any other call into EF.

[qin-nz]

No, the middleware should not have a scope per request. And the only async method is awaited. The SqlException only occur when the request rate is a little high. (about 10 requests per second) The NullReferenceException at string key = await _context.Users... still confused me.

May be some exceptions is caused by unstable SQL Azure service. Because I can get following exceptions in Application Insights and it's last for more than an hour. (My web app is deploy in Azure East Asia data center) Name resolution failed for d2d98bfbbd85.tr7.eastasia1-a.worker.database.windows.net. No such host is known Name resolution failed for b0d1e31e14b4.tr7.eastasia1-a.worker.database.windows.net. This is usually a temporary error during hostname resolution and means that the local server did not receive a response from an authoritative server

[ajcvickers]

@qin-nz I think it is likely that the same context instance is being used concurrently by multiple threads, especially given your statement that the middleware is not scoped by request, Can you do some investigation to ensure that the same context instance is not being used concurrently?

[qin-nz]

@ajcvickers I think you may be right.

Which ServiceLifeTime should I use for DbContext? Currently I don't explicit set it. I only need context in one middleware and in controllers.

I am doing the investigation and I will report it later.

[ajcvickers]

@qin-nz The default lifetime is Scoped. This is because normally there will be one scope per request, so you will get a new context instance per request. It is also valid to use Transient, in which case every place you use a context will get a new instance.

The thing to be careful about it not depending on DbContext in a service that is a Singleton if that singleton service is going to be used by multiple requests/threads. If you have a singleton service, then it may be better to depend on DbContextOptions<Email2RssContext > and then explicitly new up a context when needed passing in the context options that were injected:

using (var context = new Email2RssContext(contextOptions)
{
}

[ajcvickers]

Closing for now as last activity was two weeks ago. Feel free to re-open if you have any additional information.