CODEWITHSUNDEEP
c#asp.net-corepaginationentity-framework-core
Commander
Commander

Reputation: 317

SqlException error when trying to implement pagination in .net core web api

I have these two pieces of code to implement pagination.

The first one is a class as follows:

public class QueryParameters
{
    const int _maxSize = 100;
    private int _size = 50;

    public int Page { get; set; }

    public int Size 
    {  
        get { return _size; }
        set { _size = Math.Min(_maxSize, value); }
    }
}

The second one is my HttpGet code:

    private readonly NSContext _context;

    public UsersController(NSContext context)
    {
        _context = context;
    }

    [HttpGet]
    public async Task<IActionResult> GetAllUSers([FromQuery] QueryParameters queryParameters)
    {
        IQueryable<User> users = _context.User;
        users = users
            .Skip(queryParameters.Size * (queryParameters.Page - 1))
            .Take(queryParameters.Size);
        return Ok(await users.ToArrayAsync());
    }

But when I run the program I get this error:

enter image description here

Upvotes: 1

Views: 132

Answers (1)

Muhammed Nigil
Muhammed Nigil

Reputation: 183

I guess the problem is you are trying to skip negative 50 (-50) rows thats ie, The PAGE variable in QueryParameters Class is 0, because you have not initialized it any where

so this statement 

 Skip(queryParameters.Size * (queryParameters.Page - 1))

says Skip(50 * (0 - 1)) which is Skip (-50)

That's why the error.

You can give a default value

If you are using C# 6 you can do this:

public int Page { get; set; } = 1

or 

private int _Page = 1;
public int Page
{
   get
   {
      return _Page;
   }
   set
   {
      _Page = value;
   }
}

I hope this solves your issue

Upvotes: 2

Related Questions