Nugget 21: Preventing SqlClient.SqlException Transient Failure In a Blazor Application

 


In WORKS Commons, a QuickGrid loading job applications provides a search function for users to filter job applications by Company Name. The search is pushed by EF to the server. However the way this was implemented had a fatal flaw: With every character typed by the user, a request is sent to the server. This causes conflicts or interruptions on MSSQL Server, which in turn cases EF execution to abort the batch and an exception is thrown:

2026-10-19 16:13:30.746 -07:00 [WRN] Unhandled exception rendering component: An exception has been raised that is likely due to a transient failure. Consider enabling transient error resiliency by adding 'EnableRetryOnFailure' to the 'UseSqlServer' call.
System.InvalidOperationException: An exception has been raised that is likely due to a transient failure. Consider enabling transient error resiliency by adding 'EnableRetryOnFailure' to the 'UseSqlServer' call.
---> Microsoft.Data.SqlClient.SqlException (0x80131904): The request failed to run because the batch is aborted, this can be caused by abort signal sent from client, or another request is running in the same session, which makes the session busy.
Operation cancelled by user.
at Microsoft.Data.SqlClient.SqlCommand.<>c.b__195_0(Task1 result) at System.Threading.Tasks.ContinuationResultTaskFromResultTask2.InnerInvoke()
at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state)
--- End of stack trace from previous location ---
at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state)
at System.Threading.Tasks.Task.ExecuteWithThreadLocal(Task& currentTaskSlot, Thread threadPoolThread)
--- End of stack trace from previous location ---
at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable1.AsyncEnumerator.InitializeReaderAsync(AsyncEnumerator enumerator, CancellationToken cancellationToken) at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerExecutionStrategy.ExecuteAsync[TState,TResult](TState state, Func4 operation, Func`4 verifySucceeded, CancellationToken cancellationToken)
ClientConnectionId:4e918cb2-5ca9-45d6-887a-20a5fe657a1a

See issue 133 on GitHub.

The suggestion on the stack trace is important, and it will add resilience, however, it addresses a different problem; it does not really solve the root cause because the grid continues to place requests constantly every time the user types a character. With EnableRetryOnFailure enabled, transient failures should be retried but it does not address the reason. The real solution is to debounce the user input.

This method was added to the View Model and handles the input of the search input control:


The key is in OnCompanySearchChanged() function. What is it doing?

Every single time the user types a new character OnCompanySerchChange() fires and the very first thing it does is cancel the cancellation token. This will kill the previous Task.Delay that was currently waiting.

If a previous delay is interrupted, it throws a TaskCanceledException. The catch, quietly handled it and logs it, preventing setting the value of CompanySearch, which is used to build the query.

If the user stops writing for 300 milliseconds the Task.Delay completes, CompanyName is updated, triggering the actual search logic.

The correction for this defect included other improvements. but debouncing user input was the key change by addressing excessive search requests. The logs tells us that story.

It is worth to notice that debouncing DOES NOT cancel a query that has already started, but it prevents the barrage of searches caused by the user typing. It is also important to mention that the stack trace suggestion was not ignored, hence the other improvements mentioned above; EnableRetryOnFailure() remains useful for eligible transient failures; but this addresses a different problem.


As a final note, WORKS Commons uses MSSQL Server 2025 (RTM) - Latest Update (X64)

Comments

Post a Comment

Popular posts from this blog

Nugget 11: Adding Filtering to a Blazor QuickGrid

Image
  The best way to add filtering to a Blazor QuickGrid is by creating an expression tree representing the predicate of a LINQ Lambda Expression Where clause. In  Nugget 9  , a simple Blazor app that supports CRUDE operations was created using scaffolding and Entity Framework.  Because the app loads a collection of items from the database and index component containing a QuickGrid component was created to display the collection of items. The above code represents the Index.razor component, which was modified after it was created.  Directives are omitted for simplicity. The component filters the collection of JobPosts by two model properties: JobPost.ApplicationDeclined , and JobPost.JobType using a checkbox and an input field.  The input field is defined in the ColumnOptions for the JobType PropertyColumn  and is bound to a component string variable, jobTypeSearch . Because this variable is bound to the input field, the variable is updated whene...
Read more

Nugget 9: Building a JobBank App using Blazor. A Nugget with a Story.

Image
One of my daughters is about to graduate in a few days, and she has a contract lined up already as a Game Developer! As a happy Dad, I am very proud of her accomplishments.   " Dad, how do I keep track of the employment opportunities I have applied to? " Good question; you need to keep track of who you send your resume to. Your email application outbox doesn't work well because some job applications are submitted online, and most of the time, the platform that handles applications doesn't send you a thank-you email. Plus, you could see the same job elsewhere and apply again by mistake, which is detrimental to us. You could create an Excel document and use it as a job submission database. You could search, filter, and add notes and comments after a phone call or interview. You could also make a record of any topics you might need to review before the initial contact with the potential employer based on the job description. This is practical, but not as fun. " How ...
Read more

Nugget 14: Connection to MongoDB Atlas - SSL Alert 80

Image
  I'm using MongoDB Atlas (1) to host my NoSQL database. Creating an application with the MERN stack was very straightforward. However, after a few hours, when I tried to run the same application, Atlas displayed an SSL error message. How is this possible? What changed overnight? Something changed overnight, of course! When creating the cluster in Atlas, my IP address was configured (2), and by default, it expired after 6 hours. The error message was a bit misleading, although the problem was discovered after some investigation. Either re-whitelist your IP address or remove and re-add it to fix the error. I had a harder time finding the solution in Atlas, so I'm writing it down. Log in to Atlas, go to Network Access, and fix it there.  There is also an option to be able to access the app from any IP.  I prefer to restrict the IP. After the correction, it worked as expected. The Atlas MongoDB database is impressive (3, 4).   References & Notes MongoDB Atlas ...
Read more