C#•12mo ago

Why is my EFCore code saturating my Postgres database connection pool?

I've got an API endpoint which applies a patch document to a resource on my database to update two values on a table, rather than re-sending all the table data. I've got a few endpoints feeding into postgres but this one seems to be saturating my connection pool for some reason.

Here's the code:

c#
/// <summary>
    /// Patch a diagnostic resource on the database.
    /// </summary>
    /// <param name="id"></param>
    /// <param name="patchDocument"></param>
    /// <returns></returns>
    [HttpPatch("patch-diagnostics/{id}")]
    public IActionResult PatchDiagnostics(int id, [FromBody] JsonPatchDocument<SystemDiagnosticData> patchDocument)
    {
        SystemDiagnosticData? resourceToPatch = _dbContext.DiagnosticData.FirstOrDefault(d => d.Id == id);

        if (resourceToPatch == null)
        {
            return Problem(
                statusCode: StatusCodes.Status404NotFound,
                detail: $"Resource with ID ({id}) not found.",
                title: "NO RESOURCE WITH SPECIFIED ID"
            );
        }

        patchDocument.ApplyTo(resourceToPatch, ModelState);

        if (!ModelState.IsValid)
        {
            return BadRequest(ModelState);
        }

        _dbContext.SaveChangesAsync();
        return Ok(resourceToPatch);
    }

c#
/// <summary>
    /// Patch a diagnostic resource on the database.
    /// </summary>
    /// <param name="id"></param>
    /// <param name="patchDocument"></param>
    /// <returns></returns>
    [HttpPatch("patch-diagnostics/{id}")]
    public IActionResult PatchDiagnostics(int id, [FromBody] JsonPatchDocument<SystemDiagnosticData> patchDocument)
    {
        SystemDiagnosticData? resourceToPatch = _dbContext.DiagnosticData.FirstOrDefault(d => d.Id == id);

        if (resourceToPatch == null)
        {
            return Problem(
                statusCode: StatusCodes.Status404NotFound,
                detail: $"Resource with ID ({id}) not found.",
                title: "NO RESOURCE WITH SPECIFIED ID"
            );
        }

        patchDocument.ApplyTo(resourceToPatch, ModelState);

        if (!ModelState.IsValid)
        {
            return BadRequest(ModelState);
        }

        _dbContext.SaveChangesAsync();
        return Ok(resourceToPatch);
    }

c#
/// <summary>
    /// Patch a diagnostic resource on the database.
    /// </summary>
    /// <param name="id"></param>
    /// <param name="patchDocument"></param>
    /// <returns></returns>
    [HttpPatch("patch-diagnostics/{id}")]
    public IActionResult PatchDiagnostics(int id, [FromBody] JsonPatchDocument<SystemDiagnosticData> patchDocument)
    {
        SystemDiagnosticData? resourceToPatch = _dbContext.DiagnosticData.FirstOrDefault(d => d.Id == id);

        if (resourceToPatch == null)
        {
            return Problem(
                statusCode: StatusCodes.Status404NotFound,
                detail: $"Resource with ID ({id}) not found.",
                title: "NO RESOURCE WITH SPECIFIED ID"
            );
        }

        patchDocument.ApplyTo(resourceToPatch, ModelState);

        if (!ModelState.IsValid)
        {
            return BadRequest(ModelState);
        }

        _dbContext.SaveChangesAsync();
        return Ok(resourceToPatch);
    }

c#
/// <summary>
    /// Patch a diagnostic resource on the database.
    /// </summary>
    /// <param name="id"></param>
    /// <param name="patchDocument"></param>
    /// <returns></returns>
    [HttpPatch("patch-diagnostics/{id}")]
    public IActionResult PatchDiagnostics(int id, [FromBody] JsonPatchDocument<SystemDiagnosticData> patchDocument)
    {
        SystemDiagnosticData? resourceToPatch = _dbContext.DiagnosticData.FirstOrDefault(d => d.Id == id);

        if (resourceToPatch == null)
        {
            return Problem(
                statusCode: StatusCodes.Status404NotFound,
                detail: $"Resource with ID ({id}) not found.",
                title: "NO RESOURCE WITH SPECIFIED ID"
            );
        }

        patchDocument.ApplyTo(resourceToPatch, ModelState);

        if (!ModelState.IsValid)
        {
            return BadRequest(ModelState);
        }

        _dbContext.SaveChangesAsync();
        return Ok(resourceToPatch);
    }

The endpoint is called every few minutes to update the system online status and current user count on a couple of virtual machines I'm running. As you can see in the attached image, I'm getting loads of

Client: ClientRead

Client: ClientRead

Client: ClientRead

Client: ClientRead wait events that don't disappear (there's 50 more if i scroll down). They're idle but, they don't disappear. This is the only endpoint that does this out of the 10+ enabled for the database.

Is there something I need to do in regards to closing a command sequence after applying a patch doc to a resource or something? I assumed

_dbContext.SaveChangesAsync()

_dbContext.SaveChangesAsync()

_dbContext.SaveChangesAsync()

_dbContext.SaveChangesAsync() would just cut that off like any other database altering function.

Thanks!

Why is my EFCore code saturating my Postgres database connection pool?

Similar Threads

Similar Threads

Similar Threads