Pagination, Sorting and Filtering with LINQ

Here is our sample data:

var books = new List<Book>(){
    new Book
        {
            Id = 1,
            ISBN = "EWRE23453FVFQ",
            Title = "First Book title",
            Description = "Description",
            Pages = 299,
            Author = new Author { Name = "John Doe", Bio = "John's Bio"}
        },
    new Book
    {
        Id = 2,
        ISBN = "UYTRE5434556",
        Title = "Second Book title",
        Description = "Description",
        Pages = 322,
        Author = new Author { Name = "Albert David", Bio = "David's Bio" }
    },
    new Book
    {
        Id = 3,
        ISBN = "654RDBSDB445",
        Title = "Third Book title",
        Description = "Description",
        Pages = 99,
        Author = new Author { Name = "Sun Micro", Bio = "Sun's Bio" }
    }
};

public class Book
{
    public int Id { get; set; }
    public string ISBN { get; set; } = string.Empty;
    public string Title { get; set; } = string.Empty;
    public string Description { get; set; } = string.Empty;
    public required Author Author { get; set; }
    public int Pages { get; set; }
}

public class Author
{
    public string Name { get; set; } = string.Empty;
    public string Bio { get; set; } = string.Empty;
}

Pagination

Here is a controller action method that shows how the page parameter is used ti achieve pagination

[HttpGet]
public async Task<IActionResult> GetBooks(int? page)
{
    IQueryable<Book> query = books.AsQueryable();

    int pageSize = 2;

    if (page == null || page < 1) page = 1;
    int totalPages = 0;

    decimal count = query.Count();
    totalPages = (int)Math.Ceiling(count / pageSize);

    if (page > totalPages)
    {
        page = 1;
    }

    query = query.Skip((int)(page - 1) * pageSize).Take(pageSize);

    var _books = query.ToList();

    var response = new
    {
        Page = page,
        TotalPages = totalPages,
        PageSize = pageSize,
        Data =  _books
    };

    return Ok(response);
}

Testing the requests:

https://localhost:7192/api/Books

https://localhost:7192/api/Books?page=1

{
  "page": 1,
  "totalPages": 2,
  "pageSize": 2,
  "data": [
    {
      "id": 1,
      "isbn": "EWRE23453FVFQ",
      "title": "First Book title",
      "description": "Description",
      "author": {
        "name": "John Doe",
        "bio": "John's Bio"
      },
      "pages": 299
    },
    {
      "id": 2,
      "isbn": "UYTRE5434556",
      "title": "Second Book title",
      "description": "Description",
      "author": {
        "name": "Albert David",
        "bio": "David's Bio"
      },
      "pages": 322
    }
  ]
}

Filtering

We will be filtering using title and author name. author and title are optional parameters

// author and title are optional parameters

IQueryable<Book> query = books.AsQueryable();

if(author != null)
{
    query = query.Where(
        x => x.Author.Name.ToLower()
                .Contains(author.ToLower()));
}

if (title != null)
{
    query = query.Where(
        x => x.Title.ToLower()
                .Contains(title.ToLower()));
}

Sorting

sort and order are optional parameters

// sort and order are option parameters

IQueryable<Book> query = books.AsQueryable();

if (sort == null) { sort = "id"; }

if (order == null || order != "asc") { order = "desc"; }

if (sort.ToLower() == "id")
{
    if (order == "asc")
    {
        query = query.OrderBy(b => b.Id);
    }
    else
    {
        query = query.OrderByDescending(b => b.Id);
    }
}

if (sort.ToLower() == "title")
{
    if (order == "asc")
    {
        query = query.OrderBy(b => b.Title);
    }
    else
    {
        query = query.OrderByDescending(b => b.Title);
    }
}

Final Controller Action

[HttpGet]
public async Task<IActionResult> GetBooks(int? page, 
    string? author, string? title, string? sort, string? order)
{
    IQueryable<Book> query = books.AsQueryable();


    // FILTERING BEGINS
    if(author != null)
    {
        query = query.Where(
            x => x.Author.Name.ToLower()
                    .Contains(author.ToLower()));
    }

    if (title != null)
    {
        query = query.Where(
            x => x.Title.ToLower()
                    .Contains(title.ToLower()));
    }
    // FILTERING ENDS

    
    // SORTING BEGINS
    if (sort == null) { sort = "id"; }

    if (order == null || order != "asc") { order = "desc"; }

    if (sort.ToLower() == "id")
    {
        if (order == "asc")
        {
            query = query.OrderBy(b => b.Id);
        }
        else
        {
            query = query.OrderByDescending(b => b.Id);
        }
    }

    if (sort.ToLower() == "title")
    {
        if (order == "asc")
        {
            query = query.OrderBy(b => b.Title);
        }
        else
        {
            query = query.OrderByDescending(b => b.Title);
        }
    }
    // SORTING ENDS


    // PAGINATION BEGINS
    int pageSize = 2;

    if (page == null || page < 1) page = 1;
    int totalPages = 0;

    decimal count = query.Count();
    totalPages = (int)Math.Ceiling(count / pageSize);

    if (page > totalPages)
    {
        page = 1;
    }

    query = query.Skip((int)(page - 1) * pageSize).Take(pageSize);
    // PAGINATION ENDS


    // GETTING THE FINAL RESULT
    var _books = query.ToList();

    var response = new
    {
        Page = page,
        TotalPages = totalPages,
        PageSize = pageSize,
        Data =  _books
    };

    return Ok(response);
}

Requests and Responses

Filtering

https://localhost:7192/api/Books?author=john

{
  "page": 1,
  "totalPages": 1,
  "pageSize": 2,
  "data": [
    {
      "id": 1,
      "isbn": "EWRE23453FVFQ",
      "title": "First Book title",
      "description": "Description",
      "author": {
        "name": "John Doe",
        "bio": "John's Bio"
      },
      "pages": 299
    }
  ]
}

Sorting

https://localhost:7192/api/Books?page=1&sort=title&order=desc

{
  "page": 1,
  "totalPages": 2,
  "pageSize": 2,
  "data": [
    {
      "id": 3,
      "isbn": "654RDBSDB445",
      "title": "Third Book title",
      "description": "Description",
      "author": {
        "name": "Sun Micro",
        "bio": "Sun's Bio"
      },
      "pages": 99
    },
    {
      "id": 2,
      "isbn": "UYTRE5434556",
      "title": "Second Book title",
      "description": "Description",
      "author": {
        "name": "Albert David",
        "bio": "David's Bio"
      },
      "pages": 322
    }
  ]
}