Getting SqlException and ObjectDisposedException in Asp.net core 5 webapi

Question

I have a repository for albums table (album entity) and I do filtering and paging in repository as you see:

public IQueryable<Album> GetAllAlbums(AlbumQueryParameters queryParameters)
    {
        var albums = _cache.Get<IQueryable<Album>>("_AllAlbums");

       if(albums == null){
           albums = _context.Albums;
           _cache.Set<IQueryable<Album>>("_AllAlbums", albums, ApplicationDefaults.MemoryCachingOption);
        }

        if(queryParameters.ArtistId != null)
        {
            albums = albums.Include(a => a.ArtistToAlbums)
            .SelectMany(a => a.ArtistToAlbums)
            .Where(a => a.ArtistId == Convert.ToInt32(queryParameters.ArtistId))
            .Select(a => a.Album);
        }

        if(queryParameters.GenreId != null)
        {
            albums = albums.Include(a => a.AlbumToGenres)
            .SelectMany(a => a.AlbumToGenres)
            .Where(a => a.GenreId == Convert.ToInt32(queryParameters.GenreId))
            .Select(a => a.Album);
        }

        // Pagination
        albums = albums.Skip(queryParameters.Size * (queryParameters.Page - 1))
        .Take(queryParameters.Size);

        return albums;
    }

And here is my controller:

[HttpGet]
    public async Task<IActionResult> GetAllAlbums([FromQuery] AlbumQueryParameters queryParameters)
    {
        var albums = _albumRepository.GetAllAlbums(queryParameters);
        return Ok(await albums.ToListAsync());
    }

When I try to get data from this action I get this error:

    Microsoft.Data.SqlClient.SqlException (0x80131904): The number of rows provided for a FETCH clause must be greater then zero.
   at Microsoft.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at Microsoft.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   at Microsoft.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
   at Microsoft.Data.SqlClient.SqlDataReader.TryHasMoreRows(Boolean& moreRows)
   at Microsoft.Data.SqlClient.SqlDataReader.TryReadInternal(Boolean setTimeout, Boolean& more)
   at Microsoft.Data.SqlClient.SqlDataReader.ReadAsyncExecute(Task task, Object state)
   at Microsoft.Data.SqlClient.SqlDataReader.InvokeAsyncCall[T](AAsyncCallContext`1 context)
--- End of stack trace from previous location ---
   at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.AsyncEnumerator.MoveNextAsync()
   at Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.ToListAsync[TSource](IQueryable`1 source, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.ToListAsync[TSource](IQueryable`1 source, CancellationToken cancellationToken)
   at StoreApi.Controllers.AlbumController.GetAllAlbums(AlbumQueryParameters queryParameters) in /home/nalien/Projects/albumshop/StoreApi/Controllers/AlbumController.cs:line 27
   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, ValueTask`1 actionResultValueTask)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.<InvokeNextActionFilterAsync>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.<InvokeInnerFilterAsync>g__Awaited|13_0(ControllerActionInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeFilterPipelineAsync>g__Awaited|19_0(ResourceInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeAsync>g__Awaited|17_0(ResourceInvoker invoker, Task task, IDisposable scope)
   at Microsoft.AspNetCore.Routing.EndpointMiddleware.<Invoke>g__AwaitRequestTask|6_0(Endpoint endpoint, Task requestTask, ILogger logger)
   at Microsoft.AspNetCore.Authorization.AuthorizationMiddleware.Invoke(HttpContext context)
   at Swashbuckle.AspNetCore.SwaggerUI.SwaggerUIMiddleware.Invoke(HttpContext httpContext)
   at Swashbuckle.AspNetCore.Swagger.SwaggerMiddleware.Invoke(HttpContext httpContext, ISwaggerProvider swaggerProvider)
   at Microsoft.AspNetCore.Diagnostics.DeveloperExceptionPageMiddleware.Invoke(HttpContext context)
ClientConnectionId:66426711-f9b4-470a-898d-a0a0bd3082d1
Error Number:10744,State:1,Class:15

HEADERS
=======
Connection: keep-alive
Accept: */*
Accept-Encoding: gzip, deflate, br
Host: localhost:5001
Referer: http://localhost:5000/api/album?artistId=1
User-Agent: PostmanRuntime/7.26.8
Postman-Token: 317f1fb0-a03b-4c15-a6da-87b69934bf57

And when I comment the Pagination I get a new error:

System.ObjectDisposedException: Cannot access a disposed context instance. A common cause of this error is disposing a context instance that was resolved from dependency injection and then later trying to use the same context instance elsewhere in your application. This may occur if you are calling 'Dispose' on the context instance, or wrapping it in a using statement. If you are using dependency injection, you should let the dependency injection container take care of disposing context instances.
Object name: 'ApplicationDbContext'.
   at Microsoft.EntityFrameworkCore.DbContext.CheckDisposed()
   at Microsoft.EntityFrameworkCore.DbContext.get_InternalServiceProvider()
   at Microsoft.EntityFrameworkCore.DbContext.get_ChangeTracker()
   at Microsoft.EntityFrameworkCore.Query.CompiledQueryCacheKeyGenerator.GenerateCacheKeyCore(Expression query, Boolean async)
   at Microsoft.EntityFrameworkCore.Query.RelationalCompiledQueryCacheKeyGenerator.GenerateCacheKeyCore(Expression query, Boolean async)
   at Microsoft.EntityFrameworkCore.SqlServer.Query.Internal.SqlServerCompiledQueryCacheKeyGenerator.GenerateCacheKey(Expression query, Boolean async)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.ExecuteAsync[TResult](Expression query, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryProvider.ExecuteAsync[TResult](Expression expression, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryable`1.GetAsyncEnumerator(CancellationToken cancellationToken)
   at System.Runtime.CompilerServices.ConfiguredCancelableAsyncEnumerable`1.GetAsyncEnumerator()
   at Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.ToListAsync[TSource](IQueryable`1 source, CancellationToken cancellationToken)
   at StoreApi.Controllers.AlbumController.GetAllAlbums(AlbumQueryParameters queryParameters) in /home/nalien/Projects/albumshop/StoreApi/Controllers/AlbumController.cs:line 27
   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, ValueTask`1 actionResultValueTask)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.<InvokeNextActionFilterAsync>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.InvokeInnerFilterAsync()
--- End of stack trace from previous location ---
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeFilterPipelineAsync>g__Awaited|19_0(ResourceInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeAsync>g__Awaited|17_0(ResourceInvoker invoker, Task task, IDisposable scope)
   at Microsoft.AspNetCore.Routing.EndpointMiddleware.<Invoke>g__AwaitRequestTask|6_0(Endpoint endpoint, Task requestTask, ILogger logger)
   at Microsoft.AspNetCore.Authorization.AuthorizationMiddleware.Invoke(HttpContext context)
   at Swashbuckle.AspNetCore.SwaggerUI.SwaggerUIMiddleware.Invoke(HttpContext httpContext)
   at Swashbuckle.AspNetCore.Swagger.SwaggerMiddleware.Invoke(HttpContext httpContext, ISwaggerProvider swaggerProvider)
   at Microsoft.AspNetCore.Diagnostics.DeveloperExceptionPageMiddleware.Invoke(HttpContext context)

HEADERS
=======
Connection: keep-alive
Accept: */*
Accept-Encoding: gzip, deflate, br
Host: localhost:5001
Referer: http://localhost:5000/api/album?artistId=2
User-Agent: PostmanRuntime/7.26.8
Postman-Token: 6291c9cf-87cd-45a8-bc84-8d796efbed03

And here is the way I registered my services:

services.AddDbContext<ApplicationDbContext>(options => {options.UseSqlServer(configuration.GetConnectionString("MainDatabase"));});
services.AddTransient<IAlbumRepository, AlbumRepository>();
services.AddMemoryCache();

I don't know what to do to fix my problem.

The first error is probably because you don’t set queryArguments.Page and on you repository you have written Skip(queryArguments.Size * (queryArguments.Page - 1)) so if Page =0 and Size>0 you call Skip with a negative value. — ckuri
– ckuri, Commented Feb 5, 2021 at 8:35

sa-es-ir · Accepted Answer · 2021-02-05 21:31:03Z

1

The first exception happened when Skip and Take is zero, so you can check it before add pagination to query:

if(queryParameters.Size>0) 
   albums = albums.Skip(queryParameters.Size * (queryParameters.Page - 1)) .Take(queryParameters.Size);

The second exception is about object life time you add ApplicationDbContext as Scope means it lives in request life time but you add IAlbumRepository as Transient means a new instance is provided to every call interface so when you call this line:

var albums = _albumRepository.GetAllAlbums(queryParameters);

One instance of IAlbumRepository created and when respond and return albums, it will disposed and any objects in it will disposed (includes ApplicationDbContext), for prevent this exception just add IAlbumRepository as Scoped like this:

services.AddScoped<IAlbumRepository, AlbumRepository>();

I think it's better to return List instead of IQueryable some thing like this:

public async List<Album> GetAllAlbums(AlbumQueryParameters queryParameters)
    {
     //do some stuff
     return await ablums.ToListAsync();
    }

And in the controller:

return Ok(await _albumRepository.GetAllAlbums(queryParameters));

edited Feb 5, 2021 at 21:31

answered Feb 5, 2021 at 9:11

sa-es-ir

5,1902 gold badges20 silver badges38 bronze badges

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

2 Comments

thisisjab Over a year ago

You helped me with the first error and I thank you, cordially, but the second problems still remains. It really occurs when I am about to send my second request; I mean it's OK with the first request.

thisisjab Over a year ago

Thank you; You did help me a lot. Good luck my friend.

Collectives™ on Stack Overflow

Getting SqlException and ObjectDisposedException in Asp.net core 5 webapi

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related