Adding Filtering, Sorting And Pagination To a REST API | .NET 7

if you are building any kind of web API you're going to need support for filtering sorting and pagination so in this video I'm going to show you how to implement these features and what are the best practices that you need to think about we're going to expose an endpoint for fetching all of the products and we're going to introduce support for filtering sorting and paging the products so I'm going to start by exposing a get endpoint with the route of products and I'm going to make it asynchronous for now I'm just going to take in an ice sander argument so that I can publish my query using mediator and get back all of the products from the database I'm going to create a new query instance which I'm going to call the get products query and right now I don't have this class so we're going to be implementing it in just a moment then we're going to fetch all of the products by sending this query and we're going to return our products from the API by calling results dot OK and passing in the list of products right now this isn't going to compile so let's go to the application layer and introduce our query inside of the products feature folder I'm going to add another one for getting all of the products and let's start by introducing our query so this is going to be the get product query I'm going to turn it into a record and for now I'm not going to add any arguments and I'm just going to implement the I request interface from mediator and we're going to return a list of product responses I already have such a response in the query for getting a single product so we're going to just reuse it for our query that is going to fetch all of the products if you are reusing response objects like this it could make sense to move them into a separate folder or assembly so that you can share them more easily among your queries so with this in place if we head back to the endpoint and import the query everything is going to compile nicely and this is going to be a list of product responses that is returned by mediator let's go ahead and introduce a respective Handler for our query instance so this is going to be the get products query Handler it has to implement the I request Handler interface from mediator we need to specify which query we are handling and what is the response of this query so this response is going to be a list of product response objects so that's what I'm going to specify and now I need to implement my query Handler and I just have one method that is returning my list of product responses I'm going to use EF core directly for writing my query so I'm going to inject the I application DB context interface and now I can inject it from The Constructor and in my handle method I'm going to start by making the handle method asynchronous and let's create a variable for our products now I'm going to speed things up by pasting in a query that returns all of the products from the database and Maps them to a product response so nothing fancy here just a simple EF core query and now we're going to start introducing more capabilities to our feature the first thing I want to introduce is support for filtering on the name and skew values with some search term that I'm going to pass from my endpoint so let's head over to the endpoint and introduce our first argument which is going to be a string value for the search term now we're going to use this to apply a contains filter on the name and skew columns and this is going to be how we introduce filtering now I need to be able to pass this into my query so on the query object I need to expose a property for our search term and now we're going to use it in our query Handler to add filtering how you introduce filtering is by adding aware statement and then you can filter the product based on your requirements I said that I want to apply the search term filter on the name and this queue of my product the product name is a string so this should be easy to filter on and the skew is a value object so this is going to be slightly more challenging now let's say we only want to apply the filter when the search term is not empty this also means that the search term argument could be nullable so let's say we support not passing in a search term so I'm going to make the adjustment to support for a nullable string and my endpoint and in my query I'm going to make a few changes here and I'm going to grab the context products DB set and try to convert it into a variable so this is going to be the products query and you can either call as variable here or you can just cast this to an i queryable of product and this should work without a problem the beauty of Ike variable is that you can compose on the ike variable instance like adding a where statement or sorting before you finally invoke the projection and load the data from the database so the first thing we want to do if let's say string is null or white space and we're going to pass in the search term and we really want to negate this so when the search term exists we want to update our product query so that it becomes product query with the where statement applied and the where statement we want to apply is that the name contains the search term from our query or this Q value contains the search term from our query so if you actually leave your query like this you're going to run into an exception at runtime because EF core will not be able to translate this query because of the way mysq value object is configured a lot of you working with Rich domain models are going to run into this issue sooner or later so I'm going to show you how to fix it and unfortunately the fix isn't really pretty so my skew value object is just a wrapper around a string value and what I have to do is introduce an explicit operator that is going to cast mysq value object into a string value by just returning the value of this queue and then when I go back to my query what I have to do is take this skew value object and explicitly cast it to the string through the operator that I just defined and then I can ask if this string contains the search term so this should cover filtering and let's check if everything is working if I just send a get request to our API to fetch all of the products we're going to get back all of the products in the database and let's say I want to apply a filter on the search term so I can do something like this we need to append a search term query parameter and we need to give it a value of let's say product 1 which should match this product here now if I send this you're going to see that the query executes properly and we get back some data let's try Product 2 so this still works and if I just pass in 2 I also expect to get back the same product because this is checking if the product name or SKU contains the character 2 in any place in the value at the the database level your entire products table will be scanned to check the name and skew columns for the presence of the search term so this may not be ideal if you have a lot of data you may want to introduce some sort of index that's going to speed up this query the next thing I want to introduce to my endpoint is the ability to specify a sort column and a sort order there are a few ways how you can approach this somebody could specify a string like this let's say name ascending and then you're going to sort the product by the name in the ascending order to sort in the reverse order you could pass in name descending and so on so a different alternative could be passing these values separately so you have one argument for the column and another for the sort order and it's also important to have a default sort order because we're going to be implementing pagination later on and sorting is critical for pagination to work correctly let's Implement support for this format here with specifying the sort column and the sort order separately so we're going to introduce this at the endpoint level so let's start by introducing values for the sort column and the sort order now you could say that these are required and make them not nullable or you can make them nullable strings and then handle this at your query level to see what the default value is let's pass these values to our query object so we're going to pass the sort column and the sort order and of course I need to introduce the respective properties on my query object so this is going to be the sort column and I also need to add the sort order and now I can use them in my query Handler to add support for sorting you are going to need a way to select which column you are sorting on so that you can pass it to the order by statement so if we check out the order by statement it accepts a function taking in a product and returning the key by which we are sorting I'm going to create a variable that is going to hold the expression that we're going to pass to the order by statement so this has to be an expression of a function accepting a product and returning some sort of object where the object is going to be our property that we are sorting on let's call it key selector and how we're going to get this value is we're going to write a switch expression on the sort column and then we're going to check whether the value in the sort column matches any of the supporters or columns that we want to have so let's first add the default sort column which is going to be our product ID so let's just return the product ID and now we want to check for the other columns that we have so let's say if this says skew then we sort by the product skew value and I'm just actually going to copy paste this and adjust it for the other properties so let's say we want to have sorting by name then we're going to use the name property let's also say we want to have price amount so we're going to sort by the price amount and let's also add support for Price currency so we can say currency or we can just say currency here and amount so whichever way you prefer let's use this because it's simpler I'm using lowercase values here which means that if somebody passes in any uppercase character then my columns are not going to match so I can do a two lower call here before my switch statement I'm applying this operator here to only invoke the two lower function if sort column is not null now that we have our sort column selector we need to check for sort order so I'm going to say if request sort order let's also convert it to a lower string is equal to descending then I'm going to apply sorting in descending sort order otherwise it's going to be in ascending sort order by default and this is also going to cover the case if the sort order is null so now our products query becomes order by descending and we're going to pass it the key selector and of course we need to assign back to the products query value otherwise it's going to be products query is equal to products query order by then this is going to be in ascending order and we're going to pass it the key selector to improve the readability I'm going to move this part here into a separate method which we're going to call get sort column or we can say get sort property because we are returning actually a property value and we can even go ahead and just pass this here without an intermediate value and we can get rid of the variable here and just make this into a switch expression so let's say return and just switch on the sort column and you can even turn this into an expression if you want to we're going to leave this like so and this is what our query looks like now so we have the filtering that we had before we have added support for sorting and then we are executing our query so let's check if sorting is actually working correctly I'm going to get rid of the search term so we're not going to be applying filtering I'm going to say sort column is amount and the sort order is descending so we want to sort all of the products based on the price amount in descending order if I check out my response you will see that the price here is 30 then we have 25 20 15 and 10. so it seems to be working let's try sorting in ascending order and let's see what changes so now we have the same products but the price is sorted in ascending order as you can see and if I also omit the sort order completely it's still going to be sorted in ascending order by default which you can see when I send this query sorting and filtering seems to be working so let's introduce support for pagination pagination is a technique to only return a subset or a given page from the database so that you're not loading all of the records from the database on every request it's also helpful in user interfaces because you usually only care about the first few values and then you can narrow done your search if you need anything more than that to introduce paging we're going to add two more query parameters which I'm going to call Page and page size so we're going to pass these to our get products query after sort column and sort order and then we're going to use them to implement pagination in the query Handler so here I need to add page and then I need page size the page number is typically going to start from 1 and Page size is typically going to be predetermined on your user interface after filtering and sorting on the product we want to introduce paging paging is relatively straightforward we're going to skip some records then we're going to take some records and finally project everything from the database how many records you skip or take is determined by the page and Page size so for take it's easy you always take the page size number of records for the skip argument you need to determine what page you're on so let's say we start our pagination from page one so we want to reduce this to zero for the first page and multiply by the page size so for the first page we're going to be skipping zero rows because 1 minus 1 is going to be 0 multiplied by whatever is the page size we end up skipping no records and then we take the first page from the database for page two we're going to be skipping one times whatever is the page size and so on until we get to the end of the table if we take a look at this in practice let's leave the amount as the sort column so we're going to sort by the amount in ascending and let's say we want to get the first page and the page size is equal to 5. if I send this request I'm going to get back five records from the database and now if I try to reduce the page size to 2 I'm only going to get back the first two records on the first page so now how I go further into my records is by incrementing the page size so let's say I want to get the second page and I get the next two products and if I try to get page free I'm going to get only one product because I have a total of five products in the database so paging is working but this implementation isn't ideal right now we are always returning a list of products from our query Handler now this is missing some critical information like how many records are there in total because we are applying pageant and if the next or previous page in the database actually exists so I'm going to create a helper class that you will commonly see if you're working with pagination and I'm going to call it paged list so what do we want to add here so I'm going to make this public class and it's going to be generic just like our list is generic some implementations go ahead and Implement list of T so that the page list is just an extension of this collection I'm not going to go down this route because I like returning a sort of an envelope response which contains my items and then contextual information so let's expose the properties that I want to return so a list of T of items then we're going to expose let's say the current page we also may want to expose the page size then what could be interesting is the total count of all the records in the database and then based on this you can deduce if there's a next or a previous page so if there's a next page so let's call it has next page how you can calculate this is by multiplying page and Page size and checking if this is less than the total count of all the records in the database and for the has previous page it's much simpler so has previous page you can calculate this by checking if the page size is greater than one if you are on any page other than the first one then the previous page exists let me add a private Constructor for my page list and I'm going to auto complete this to accept a list of items the page page size and total count arguments and now I'm going to actually pass these values to my page list is by exposing aesthetic Factory method so this is going to be a public static method returning a page list of T let's call it create async because I'm going to be running some asynchronous queries so I actually want this asynchronous and returning a task of page list of T it doesn't have to be a generic method and what I need is an like variable instance of T so this is going to be my query and I'm going to need the page and Page size arguments and now this method is going to do two things so we first need to fetch the total count by executing this query and calling the count async method which is going to return an integer value representing the number of products in the database now we're going to get the items for our list by applying paging so I'm going to say query Skip and we're going to skip page -1 times page size then we're going to take page size from the database and we can call to list async and finally I can just return a new instance and pass in the items page page size and the total count of records how we are going to use this is if we go back to the get products query I'm going to replace this with a paged list or product responses now I need to update my query Handler to return a page list of product responses and also my handle method and now what I need to do is get rid of this part here I'm also going to get rid of the call to to list async and product is now going to be an i queryable of product responses so product responses ready and we're going to use it to pass it to our paged list Factory method so products that we're going to return from the database is going to be a paged list of product responses then we can call the create async method and pass it the product responses query and also the page and Page size from our query object so let's pass in the page and Page size and of course we're going to need to await this so that we can fetch the actual products and now I can return my page list of products from this query Handler if we give this a try let's say we want to fetch the first page and let's say the page size is five we are sorting by the amount and the sort order is ascending by default so if I send this query I'm going to get back my envelope response so here are my items which contain the five items that I have in the database and if I check the other values you're going to see that we have a total of five records in the database we don't have a next page and we don't have a previous page so if I reduce the page size to 2 and I send this query again now we do have an X page because we are on the first page the page size is 2 and the total number of Records is five so let's go to the second page and keep the page size at two we're only going to get two records in our items and we're going to have a next page and a previous page as well this is how our handle method looks like in the end we have the first part here applying the filtering then we have the Sorting which is dynamic based on the sort column and sort order parameters and lastly we are applying our projection then we have paging at the database level returning a page list of product responses if you want to see how to implement a crud API in.net check out this video here smash the like button if you enjoyed this video and until next time stay awesome

Channel: Milan Jovanović

Views: 20,505

Keywords: rest api, rest api explained, rest api tutorial, rest api tutorial for beginners, rest api c#, rest api design, rest api .net, rest api .net core, rest api .net 6, rest api .net 7, rest api ddd, rest api sql, rest api endpoints, rest api best practices, rest api filtering, rest api sorting, rest api pagination, rest api search, rest api sort, rest api paginate, rest api paging, sorting, filtering, pagination, paging, rest api searching, rest api filter sort paginate

Id: X8zRvXbirMU

Length: 24min 2sec (1442 seconds)

Published: Tue Jun 20 2023