I am getting the following error - sometimes - when trying to execute multiple statements using .NET 5.0, Dapper 2.0.78 with async and MSSQL Server:
System.InvalidOperationException: Invalid attempt to call NextResult when reader is closed. at Microsoft.Data.SqlClient.SqlDataReader.TryNextResult(Boolean& more)
at Microsoft.Data.SqlClient.SqlDataReader.NextResult() at Dapper.SqlMapper.GridReader.NextResult() in //Dapper/SqlMapper.GridReader.cs:line 414 at Dapper.SqlMapper.GridReader.ReadDeferred[T](Int32 index, Func 2 deserializer, Type effectiveType)+System.IDisposable.Dispose() at Dapper.SqlMapper.GridReader.ReadDeferred[T](Int32 index, Func 2 deserializer, Type effectiveType)+MoveNext() in //Dapper/SqlMapper.GridReader.cs:line 384 at System.Collections.Generic.List 1..ctor(IEnumerable 1 collection)
at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
It does not happen all the time. I suspect the SQL connection is being closed unintentionally. What can be wrong?
To debug I have tested the SQL statements and looked at the Execution Plan in SSMS - nothing here is flagged, i.e. proper indexes, primary keys are configured, it executes in very short time. SQL Server has
Here is my code which is relatively simple.
private async Task<RecipeListModel> GetRecipesByIngredientAsync(int id)
{
string sql = @"SELECT Id,Title FROM dbo.[Ingredients] WHERE Id = @id;
SELECT ID,Title FROM dbo.[Recipes] WHERE IngredientId = @Id;" // simplified for the example
RecipeListModel model = new() { };
using (SqlConnection conn = new("my connection here"))
{
var data = await conn.QueryMultipleAsync(sql, new { id }).ConfigureAwait(false);
model = new RecipeListModel
{
Ingredient = data.ReadAsync<Ingredient>().Result.SingleOrDefault(),
Recipes = data.ReadAsync<Recipe>().Result.ToList()
};
}
return model; // exception happens here.
}
public class Ingredient
{
public int Id { get; set; }
public string Title { get; set; }
// ...
}
public class Recipe
{
public int Id { get; set; }
public string Title { get; set; }
// ...
}
public class RecipeListModel
{
public IEnumerable<Recipe> Recipes { get; set; }
public Ingredient Ingredient { get; set; }
// ...
}
UPDATE WITH CHANGES FROM COMMENTS:
[Route("~/{lang}/ingredient/{title}-{id}", Name = "ingredient-view")]
public async Task<IActionResult> Ingredient(string title, int id, string q, int page = 1)
{
if (ModelState.IsValid)
{
var model = await GetRecipesByIngredientAsync(page, id, title, q).ConfigureAwait(false);
if (model.Ingredient == null || Common.ClearUrl(model.Ingredient.Title) != title) // for SEO purposes, make sure that we do not have a tampered URL
{
return NotFound();
}
if (model.Recipes.Any())
{
var total = model.Recipes.First().TotalRows;
model.TotalRows = total;
model.Pager = new Pager(total, page, 8);
model.q = string.IsNullOrEmpty(q) ? "" : q.ToString();
return View(model);
}
else
{
RecipeListModel empty = new()
{
Recipes = new List<Recipe>() { new Recipe() { Title = "" } },
Pager = new Pager(0, 0, 1),
q = q
};
return View(empty);
}
}
return NotFound();
}
private async Task<RecipeListModel> GetRecipesByIngredientAsync(int p, int id, string title, string q)
{
string searchSql = @" -- optional (search phrase can be empty)
INNER JOIN FREETEXTTABLE([dbo].[FT_Recipes_v], *, @SearchPhrase, LANGUAGE 'English') AS recipesFullSearch
ON(r.Id = recipesFullSearch.[Key])";
string sql = string.Format(@"SELECT Id,Title FROM dbo.[Ingredients] WHERE Id = @id;
;WITH RowCounter AS (
SELECT COUNT(r.Id) as TotalRows
FROM
[dbo].[Recipes] r
INNER JOIN [dbo].[RecipeIngredients] RI ON RI.Recipe_Id = r.Id
INNER JOIN [dbo].[Ingredients] I ON RI.Ingredient_Id = I.Id
{0} -- inject search phrase here if not empty
WHERE
[Active] = 1 AND [Approved] = 1
AND I.Id = @Id
),
DataRows AS (
SELECT
r.Id
,STUFF( -- combine all tags into a 'csv list' like pepper[i:123]salt[i:124]...
(
SELECT TOP 3
']' + Title + '[i:' + CAST(Ingredient_Id AS nvarchar(11)) [text()]
FROM
(
SELECT
RI.Recipe_Id
,RI.Ingredient_Id
,I.Title
FROM dbo.RecipeIngredients AS RI
INNER JOIN dbo.Ingredients AS I
ON RI.Ingredient_Id = I.Id
-- here's the relation to the main query
WHERE RI.Recipe_Id = r.Id
) TempTable
FOR XML PATH(''), TYPE
).value('.','nvarchar(max)'),1,1,''
) IngredientsCSV
,r.Title
,LEFT(r.Description,260) as Description
,d.Title AS DishTypeTitle
,I.Title AS IngredientTitle
,RF.[file]
FROM
[dbo].[Recipes] r
INNER JOIN [dbo].[DishTypes] d ON r.DishType_Id = d.Id
INNER JOIN [dbo].[RecipeIngredients] RI ON RI.Recipe_Id = r.Id
INNER JOIN [dbo].[Ingredients] I ON RI.Ingredient_Id = I.Id
{0} -- inject search phrase here if not empty
OUTER APPLY
(SELECT TOP 1 recipe_id,[file] FROM dbo.RecipeFiles WHERE recipe_id = r.id ) RF
WHERE
[Active] = 1 AND [Approved] = 1 AND I.[Id] = @Id
ORDER BY
r.Id DESC
OFFSET (@PageNumber) ROWS
FETCH FIRST (@RowsPerPage) ROWS ONLY
)
SELECT
dr.*,
(select TotalRows from rowcounter) as TotalRows
FROM
DataRows dr;"
, !string.IsNullOrEmpty(q) ? searchSql : ""
);
using (SqlConnection conn = new("data source=someip;initial catalog=mydb;persist security info=True;user id=u;password=p"))
{
using (var data = await conn.QueryMultipleAsync(sql, new
{
SearchPhrase = q,
id,
PageNumber = (p - 1) * 8,
RowsPerPage = 8
}).ConfigureAwait(false))
{
return new RecipeListModel
{
Ingredient = await data.ReadSingleOrDefaultAsync<Ingredient>().ConfigureAwait(false),
Recipes = await data.ReadAsync<Recipe>().ConfigureAwait(false)
};
}
}
}
Here is the full event log (notice the NextResultAsync instead of NextResult previously):
RequestPath: /ingredient/pepper-123
An unhandled exception has occurred while executing the request.
Exception: System.InvalidOperationException: Invalid attempt to call NextResultAsync when reader is closed. at Microsoft.Data.Common.ADP.ExceptionWithStackTrace(Exception e) --- End of stack trace from previous location --- at Dapper.SqlMapper.GridReader.NextResultAsync() in /_/Dapper/SqlMapper.GridReader.Async.cs:line 157 at Dapper.SqlMapper.GridReader.ReadBufferedAsync[T](Int32 index, Func
2 deserializer) in /_/Dapper/SqlMapper.GridReader.Async.cs:line 241 at myproject.Controllers.HomeController.GetRecipesByIngredientAsync(Int32 p, Int32 id, String title, String q) in C:\Web\myproject\myproject-net\Controllers\HomeController.cs:line 502 at myproject.Controllers.HomeController.Ingredient(String title, Int32 id, String q, Int32 page) in C:\Web\myproject\myproject-net\Controllers\HomeController.cs:line 125 at Microsoft.AspNetCore.Mvc.Infrastructure.ActionMethodExecutor.TaskOfIActionResultExecutor.Execute(IActionResultTypeMapper mapper, ObjectMethodExecutor executor, Object controller, Object[] arguments) at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.<InvokeActionMethodAsync>g__Awaited|12_0(ControllerActionInvoker invoker, ValueTask1 actionResultValueTask) at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.g__Awaited|10_0(ControllerActionInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted) at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Rethrow(ActionExecutedContextSealed context) at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Next(State& next, Scope& scope, Object& state, Boolean& isCompleted) at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.g__Awaited|13_0(ControllerActionInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted) at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.g__Awaited|24_0(ResourceInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted) at Microsoft.AspNetCore.Mvc.Filters.MiddlewareFilterBuilder.<>c.<b__8_0>d.MoveNext() --- End of stack trace from previous location --- at Microsoft.AspNetCore.Localization.RequestLocalizationMiddleware.Invoke(HttpContext context) at Microsoft.AspNetCore.Localization.RequestLocalizationMiddleware.Invoke(HttpContext context) at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.g__Awaited|24_0(ResourceInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted) at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.Rethrow(ResourceExecutedContextSealed context) at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.Next(State& next, Scope& scope, Object& state, Boolean& isCompleted) at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.g__Awaited|19_0(ResourceInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted) at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.g__Awaited|17_0(ResourceInvoker invoker, Task task, IDisposable scope) at Microsoft.AspNetCore.Routing.EndpointMiddleware.g__AwaitRequestTask|6_0(Endpoint endpoint, Task requestTask, ILogger logger) at Microsoft.AspNetCore.Authorization.AuthorizationMiddleware.Invoke(HttpContext context) at Microsoft.AspNetCore.Authentication.AuthenticationMiddleware.Invoke(HttpContext context) at SixLabors.ImageSharp.Web.Middleware.ImageSharpMiddleware.Invoke(HttpContext context) at Microsoft.AspNetCore.Diagnostics.StatusCodePagesMiddleware.Invoke(HttpContext context) at WebMarkupMin.AspNetCore2.WebMarkupMinMiddleware.ProcessAsync(HttpContext context, Boolean useMinification, Boolean useCompression) at WebMarkupMin.AspNetCore2.WebMarkupMinMiddlewareBase.Invoke(HttpContext context) at Microsoft.AspNetCore.Diagnostics.ExceptionHandlerMiddleware.g__Awaited|6_0(ExceptionHandlerMiddleware middleware, HttpContext context, Task task)
I can't say for sure, but it appears to be a bug in ReadSingleOrDefaultAsync which causes it to close the connection.
The idea is to fully read all resultsets before you close the connection, so you need to use something that will do that. This could be foreach, ToList, ToDictionary or SingleOrDefault.
So instead use ReadAsync and feed it into the standard SingleOrDefault.
model = new RecipeListModel
{
Ingredient = (await data.ReadAsync<Ingredient>().ConfigureAwait(false)).SingleOrDefault(),
Recipes = (await data.ReadAsync<Recipe>().ConfigureAwait(false)).ToList(),
}
I hope to get a chance to debug the Dapper source code, and update this answer with exactly what is going wrong.