Vít Zaoral Vít Zaoral - 1 year ago 91
ASP.NET (C#) Question

EF Core and big traffic leads to max pool size was reached error

We're using ASP.NET Entity Framework Core for querying our MSSQL database in our Web API app. Sometimes when we have big traffic, querying to DB ends with this error:

Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached.

I wonder if our pattern of using
and querying is correct or if I am missing some using/dispose pattern and error is caused by some memory leak (after some research I read then I should not use using because the lifetime is managed by the framework). I am following documentation...

My connectionString:

"myConnection": "Server=xxx;Database=xxx;user id=xxx;password=xxx;Max Pool Size=200;Timeout=200;"

My Startup.cs

public void ConfigureServices(IServiceCollection services)
// scoped context
options => options.UseSqlServer(this.Configuration.GetConnectionString("myConnection")));

then in controllers I used dbcontext by dependency injection:

public class MyController : Controller
public MyController (MyDbContext context)
this.Context = context;

public ActionResult Get(int id)
// querying
return this.Context.tRealty.Where(x=>x.id == id).FirstOrDefault();

Should I use something like:

using (var context = this.Context)
return this.Context.tRealty.Where(x => x.id == id).FirstOrDefault();

But I think that this is bad pattern when I am using dependency injection of

Answer Source

I think problem was caused by storing objects from database context queries to In memory cache. I had one big LINQ query to database context with some other subqueries inside. I called FirstOrDefault() on the end of main query but not inside subqueries. Controller was fine with it, it materialize queries by default.

 return this.Context.tRealty.AsNoTracking().Where(
                x => x.Id == id && x.RealtyProcess == RealtyProcess.Visible).Select(
                s => new
                { .....

// subquery
videos = s.TVideo.Where(video => video.RealtyId == id && video.IsPublicOnYouTube).
                        Select(video => video.YouTubeId).ToList()), // missing ToList()

And there was problem - subqueries were holding connection to database context when they where storing to In memory cache. When I implemented Redis distributed cache, it was first failing on some strange errors. It helps when I write ToList() or FirstOrDefault() to all my subqueries because distributed cache needs materialized objects.

Now I have all my queries materialized explicitly and I got no max pool size was reached error. So that one must be careful when stored objects from database context queries to In memory cache. It is need to materialize all queries to avoid to holding connection somewhere in memory.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download