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

Question

I'm running an ASP.NET Core 1.0 web app with Entity Framework Core. When the app has been running for a while (24 - 48 hours), the app starts crashing on every request to any endpoint or static resource 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 App Pool.

I am configuring Entity Framework like this:

Startup.cs

public void ConfigureServices(IServiceCollection services) { services.AddDbContext<ApplicationDbContext>(options => options.UseSqlServer(Configuration.GetConnectionString("DefaultConnection"))); }

I am loading data in the owin pipeline with an extension method like this like this:

Startup.cs

app.LoadTenantData();

AppBuilderExtensions.cs:

public static void LoadTenantData(this IApplicationBuilder app) { app.Use(async (context, next) => { var dbContext = app.ApplicationServices.GetService<ApplicationDbContext>(); var club = dbContext.Clubs.Single(c => c.Id == GetClubIdFromUrl(context)); context.Items[PipelineConstants.ClubKey] = club; await next(); }); }

Since the error only occurs when the app has been running for a long time, it is hard to reproduce, but I'm assuming it has something to do with EF opening and closing connections incorrectly.

How can I go about to debug this? Am I using EF incorrectly?

can you update here about how did you fix this! I'm getting the same problem. I'm using async and await everywhere where I do have any db query, and this keeps coming in around 30% cases when I make a load-test Thanks. — ravi punjwani
– ravi punjwani, Commented Sep 7, 2016 at 23:18
I never managed to fix the problem - ended up loading all my tenant-data inside of the MVC-pipeline using an action-filter: github.com/severisv/MyTeam/blob/master/src/MyTeam/Filters/… This only works if the filters are added in the correct order — severin
– severin, Commented Sep 8, 2016 at 8:51
@Erdogan gist.github.com/severisv/1749a13cf38aeb7e6891a9f305441448 — severin
– severin, Commented May 6, 2019 at 10:42

qin · Accepted Answer · 2016-09-20 15:53:46Z

6

I got same issue.

I think it is likely that the same dbcontext instance is being used concurrently by multiple threads.

You may need this: services.AddDbContext<ApplicationDbContext>(options => options.UseSqlServer(Configuration.GetConnectionString("DefaultConnection")),ServiceLifetime.Transient); There is a issue about that. https://github.com/aspnet/EntityFramework/issues/6491

edited Sep 20, 2016 at 15:53

answered Sep 9, 2016 at 2:31

qin

1,7431 gold badge15 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Matas Vaitkevicius Over a year ago

Although I didn't believe at first now I can second this, after introducing lock around a section that does muti table inserts error went away. Thanks, qin.

burton Over a year ago

This helped me realize my Page method was not declared async while my service method (containing the EF query) was async. I was updating to a newly coded (async) service method while previous version was not async. Thanks!

Matas Vaitkevicius · Accepted Answer · 2019-08-18 08:57:54Z

My app is very basic (write it fast, run it once, then forget), so a solution to the problem above was to simply introduce lock around the section that was doing multi-table inserts.

 public void CallGooglePlacesAPIAndSetCallback(string websiteName) { using (var db = new WebAnalyzerEntities()) { IList<IRecord> addressesToBeSearched = db.Rent.Where<IRecord>(o => o.Url.Contains(websiteName) && o.SpatialAnalysis.Count == 0).ToList().Union(db.Sale.Where<IRecord>(oo => oo.Url.Contains(websiteName) && oo.SpatialAnalysis.Count == 0)).ToList(); foreach (var locationTobeSearched in addressesToBeSearched) { try { //this is where I introduced the lock lock (_lock) { dynamic res = null; using (var client = new HttpClient()) { while (res == null || HasProperty(res, "next_page_token")) { var url = $"https://maps.googleapis.com/maps/api/geocode/json?address={locationTobeSearched.Address}&key={googlePlacesApiKey}&bounds=51.222,-11.0133788|55.636,-5.6582363"; if (res != null && HasProperty(res, "next_page_token")) url += "&pagetoken=" + res["next_page_token"]; var response = client.GetStringAsync(url).Result; JavaScriptSerializer json = new JavaScriptSerializer(); res = json.Deserialize<dynamic>(response); if (res["status"] == "OK") { Tuple<decimal?, decimal?, string> coordinatesAndPostCode = ReadResponse(res["results"][0]); if (coordinatesAndPostCode != null && coordinatesAndPostCode.Item1.HasValue && coordinatesAndPostCode.Item2.HasValue) { //this is the line where exception was thrown locationTobeSearched.SpatialAnalysis.Add(new SpatialAnalysis() { Point = CreatePoint(coordinatesAndPostCode.Item1.Value, coordinatesAndPostCode.Item2.Value) }); locationTobeSearched.PostCode = coordinatesAndPostCode.Item3; } } else if (res["status"] == "OVER_QUERY_LIMIT") { return; } } } } } catch (Exception e) { } db.SaveChanges(); } } }

Collectives™ on Stack Overflow

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

2 Answers 2

2 Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Linked

Related