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

[@MilanJovanovicTech]

Get the source code for this video for FREE → the-dotnet-weekly.ck.page/rest-api-p2 Want to master Clean Architecture? Go here: bit.ly/3PupkOJ Want to unlock Modular Monoliths? Go here: bit.ly/3SXlzSt

[@rafaelg0225]

That's a hot topic in my dev team right now, we are debating if we should use SignalR or C# HTTP Listener class with and encrypted inbound port and a DNS address, our system are stand alone desktop apps and not web apps, so security is a huge factor. Biggest problem with SignalR route has been a reliable way to reconnect automatically :(

[@CreativeB34ST]

On 13:19, you could create an IQueryable extension method that takes the sort order field and your expression and does the decision for OrderBy or OrderByDescending behind the scenes. Removes a bit of boilerplate code that you don't need to copy paste for each search endpoint in your API.

[@MilanJovanovicTech]

I know, that could've been the next step

[@rasheed9947]

Man You Dont Know How Valueble These Comments are And Its Great Idea im gonna Do It Rn xD

[@kodindoyannick5328]

I've been looking for a channel like yours for a long time and now I found it. It's your channel Milan and I'm so happy. Thank you very much for your knowledge sharing. I still have to find a good mentor on the front end like you are for me on the back end 🙏🙏🙏

[@MilanJovanovicTech]

Welcome!

[@zheniachubarov3384]

If we talking about separation of concerns and clean architecture, where am I supposed to put such PagedList?

[@MilanJovanovicTech]

Common/Shared stuff? Probably works fine

[@daveanderson8348]

A few years ago I used a library for parsing OData query options called Moon.OData. I could send an OData query and the program translated it into a normal t-sql. Very simple and safe for both user and developer. I believe there are more such libraries nowadays. Would definitely recommend that to anyone.

[@MilanJovanovicTech]

Pretty cool!

We want the Dapper version too.

[@MilanJovanovicTech]

I see there's demand for it, already added it to my backlog 😁

[@swift8995]

Where should I put the PagedList model? In the domain layer or in the application?

[@MilanJovanovicTech]

Shared kernel

[@zakariasmahi2263]

Thanks for sharing this insightful, in all my api projects i use rye same logic for paging, filtering and sorting. For the pagination i return an object that contains items page number and page size as you did, however instead of returning a boolean HasNextPage or HasPreviousPage, i return string which are url for the previous and next pages already prepared (when i'm one the first page, previous page is null and if i'm in the last page next page is gonna be null )so in case one of clients of my api is working on a batch for frtching data url are already prepared by the api 😃 Thanks for sharing i really like your helpful content

[@MilanJovanovicTech]

HATEOAS, that's smart! I didn't want to introduce that as I thought it would be too much, but I might just prepare that for the next video

[@mohamedal-qadeery6530]

Hello that's really good can you share the code for it ?

[@adeni4359]

Have you already done a full tutorial on your channel? I like your teaching style! I wish there is a full tutorial or even a course

[@MilanJovanovicTech]

There's another video for CRUD endpoints

[@adeni4359]

@@MilanJovanovicTech I mean a full tutorial from the beginning (Include the setup). This is for us beginners 😁

[@emirsalihovic6616]

Bravo doktore 🤝

[@MilanJovanovicTech]

Hvala puno! 😁

[@gusflopes]

Waiting for the Dapper version too! Great video.

[@MilanJovanovicTech]

Hah! That would actually be interesting! Adding it to my video backlog 💥

[@rafaelg0225]

I had to create something similar but the backend DB was Mongo so I had to use projection, and for the search I used reflection to get the Data Annotation for the BsonElement name and type, the documents had over 150 fields ... It would be nice to add reflection to this tutorial to avoid hard coding the name of the properties, maybe using JsonProperty annotation??? Thanks for the great tutorial

[@MilanJovanovicTech]

That would be a nice improvement, agreed

[@thecodeman_]

Great, in-detail tutorial!

[@MilanJovanovicTech]

Thanks, bro!

[@pilotboba]

I highly suggest you determine a maximum page size and use that instead of what the client is requesting if their pagesize exceeds your maximum. :) Also, I think I like returning the pagination information in headers, rather than in the response payload.

[@MilanJovanovicTech]

Whoops, missed to mention that! But yeah, that's important to consider. (Can't say I never tried to harass some APIs by seeing if they had a max_page_size on the backedn 😅)

[@shahryarsaljoughi6073]

How would having pagination information within hdeaders benefit? Is it common?

[@pilotboba]

@@shahryarsaljoughi6073 I prefer keeping paging out of the query string which I reserve for filters.

[@Karthik-ug8ll]

Thanks for this awesome video.I was searching from long time 😊.

[@MilanJovanovicTech]

Most welcome 😊

[@EHBRod13]

Your videos are SO good! Thank you for teaching me so much!

[@EHBRod13]

Also, where did you learn these techniques? I'm curious because I feel it's difficult to find good resources and tutorials that use industry standards unless you work in the industry. Or what books do you recommend to learn more about these techniques in depth?

[@MilanJovanovicTech]

1. I do work in the industry 😅 At least I did until this year, now I try to stay in the loop with consulting. 2. Pluralsight has a lot of good resources for .NET, to be honest. Courses from Kevin Dockx about APIs cover all of this in much more detail.

[@ramytawfik9168]

Excellent as usual Milan 👌

[@MilanJovanovicTech]

Thanks a lot!

[@christianguitarcovers6021]

Milan, this was a very useful video tutorial. thx a lot

[@MilanJovanovicTech]

Glad it was helpful!

[@ccuenca24]

Excellent video! Really helpful! Thanks for sharing! by the way, can you explain in a short video format the record and sealed modifiers from your perspective and it's importance the documentation not consider optimization benefits. Thanks

[@MilanJovanovicTech]

Ahh good idea for some shorts :)

[@samuniv987]

It seems like a good tutorial but I am having difficulty following your architecture and stuff about record and sku. Are there any prior videos that I need to watch? This has been happening to me for quite a few videos so any help in that will be very appreciated.

[@MilanJovanovicTech]

Check out the playlists

[@Rodrick.]

I was expecting something more generic like Sieve for automatic pagination sorting and filtering. Or OData or graph

[@MilanJovanovicTech]

Sometimes it's worthwhile to know the fundamentals

[@gakshay9537]

Thank you for covering those topics which are helpful in handling performance issues. On filtering, if the table has multiple columns, then the number of parameters may grow. is there a generic way to easily achieve it. On pagination, if the result fits in 5 pages, the client should call the API for 5 times. So, the end query will be executed 5 times by considering the page number. will it not affect the sql performance

[@RaZziaN1]

For filtering u need to use reflection and build your own - use queryable, there is no in-built solution. For pagination u should add some kind of limit parameter which could be set to number representing numbers of records on each page (Skip + Take)

[@MilanJovanovicTech]

You can achieve most of it with reflection, and there's even a cool library out there called Dynamic.Linq

[@jamalalbatta8231]

I loved the Expression with switch to cover the which column to sort by, in my project I have the same amount of search choices I want to cover, so I'm using if else statements, can you please show case or at least right here as a respond an Expression with switch that is used in Where() instead of OrderBy(). I tried it but I'm messing something and i can't get it to work. Thanks a lot for the video, it helped a lot

[@MilanJovanovicTech]

You need Expression

[@phuongtran-qh5kq]

Hi Milan, tks for the video. It would be great if you could make a video on the Expression that you created? I could not wrap my head around that.😂

[@MilanJovanovicTech]

Ah, sorry about that. It's an EF trick I use from time to time. I'll see what I can do

[@phuongtran-qh5kq]

@@MilanJovanovicTech thanks Milan. What is the keyword of this Expression? i wanna search it up first

[@cancurva]

Thank you for the quality content I have a question. For sorting and searching why didnt you use reflection to search or sort in every column. Also if you add new fields you dont need to add new contains to the where part.

[@MilanJovanovicTech]

I find this approach more explicit, so that's why. You could've also done it with reflection, but it would've been a lot more cumbersome.

[@allannielsen4752]

normally you apply indexes to search columns

[@joseagustincarrenogallego3704]

Hi! again, I'm glad with your nice explanation! But I have a cuple of questions, what about create queries (with pagination as you show us) with multiples tables in our database? and by the other hand, what do you think about the EF against the Dapper performance?

[@MilanJovanovicTech]

Use whichever ORM you like 😁 And what about create endpoints?

[@azraiqbal187]

Really helpful 😊 Which machine are you using for editing and recoding? Specs Thanks 👍

[@MilanJovanovicTech]

Ryzen 7-based machine, RTX 3060, pretty solid setup.

[@azraiqbal187]

@@MilanJovanovicTech are you using liquid cooling or air cooling for pc ?

[@mahmoudabdel-moughiss9950]

thans alot, this was very useful .🙏🙏

[@MilanJovanovicTech]

Don't mention it. I'm here to provide value :)

[@sajithsageer182]

Great video as usual. Any chance of an Odata tutorial?

[@MilanJovanovicTech]

I haven't used it in Production, so I'll see if I can find some time to research it

[@codingbloke]

This is good stuff. EF is fine when returning simple entities from the DB. At 13:46 I would have taken the refactoring a little further and eliminate the "8-liner" (as I like to to call those if..else pairs that simply assign a variable) with the ternary operator. I'm not a fan the PageList stuff, returning the TotalCount is a very expensive choice with, IMO, limited value. Whether there is a previous or next page is of even less value. (E.g. I know I just got page 1 so I don't really need the server to tell me there isn't a previous page and for reasonable page sizes (say 20) 95% of the time I can tell there is no next page because the count of results returned is < pageSize). I prefer the simplicity of just returning the set of results but I guess it is down to your specific use cases.

[@davearkley7014]

>> returning the TotalCount is a very expensive choice An extra 86ms on my desktop machine with a 1000000 row table

[@codingbloke]

@@davearkley7014 Were you filtering a text value using a contains predicate? How many concurrent users are also demanding an extra 86ms of DB servers' time? That's huge right?

[@vivekkaushik9508]

I had the same idea reg ternary operator.

[@nothingisreal6345]

I would need to check if EF converts the CountAsync into SELECT COUNT(*) FROM (

[@codingbloke]

@@nothingisreal6345 No that still will not be "ok". Pretty sure it does do that and without any criteria the DB could respond with internal stats it maintains, it won't have to actually count records. However, if you are using say a Name Like '%' + term + '%' its going have to do a table scan, and search each string. Considering the use case for this feature is pretty weak to start with, I personally don't include this in my designs.

[@azhariagand6930]

Thanks for the tutorial and great video, Is there a series for this video?

[@MilanJovanovicTech]

Yes: 1) kzbin.info/www/bejne/pHaVfp2EapmZm9k 2) kzbin.info/www/bejne/jmndg6mOl86lg7c 3) kzbin.info/www/bejne/a2qmlmeFaK9qetE

[@azhariagand6930]

@@MilanJovanovicTech thank you milan

[@junior.santana]

Is there a standard way for adding specific/complex filters? For example, filtering by a date interval or a price range. Creating this in a generic way to be reused in several endpoints

[@alanbreeee]

Search for odata

[@MilanJovanovicTech]

Unless you'll be using it in quite a few endpoints, it's probably not worth it implementing a custom abstraction

[@petherpettersson6152]

LinqKit has a PredicateBuilder. We add attributes to our search parameter properties that defines what and how comparisons should be applied on what on the search result. Ours handles standard ones, exact, like, ranges, enum matching, lists, nullables, etc. Should be possible to find a start on google.

[@the-nasim]

​@@alanbreeeeDoes OData work with the Minimal API?

[@crazyfox55]

Does the select hurt performance of the take, skip, or count async? Calling ToListAsync is when the queryable is actually executed but how does it know to reorder the operations in a performant way?

[@MilanJovanovicTech]

No, EF doesn't execute anything until the ToList call - it's only then that everything is assembled into a SQL query so it know how to re-order them to achieve the optimal result

[@phw1009]

Ahhhh .... you are amazing

[@MilanJovanovicTech]

Thank you!

[@Guillen8606]

Could you show us how to implement the sorting if we are using generic classes and we don't know the class attributes before runtime?

[@MilanJovanovicTech]

You'd need a "smart" mechanism to standardize it, and you'll probably end up using reflection or something similar

[@joseantonioteransuarez6840]

Great video, but I have a question: is it possible to reference the DBContext from the application layer with a clean architecture?

[@MilanJovanovicTech]

It's possible - but it "breaks" the architecture

[@oziomaene7335]

Good question @joseantonio Please can you explain what you mean by break the architecture. @MilanJovanovicTech I recently worked on a project where I fetched data as queryables. I did this by creating the interface for each entities' repository in the application layer and then implementing it in the infrastructure layer. Do you have a better way to achieve this.

[@mahmoudsayed133]

I have the same consideration, So i was creating an interface in application layer *IApplicationDbContext* and it contains all DbSet's and methods like saveChanges and others that you need in application layer, then let your DbContext implements IApplicationDbContext and add it to dependency injection And then you can use IApplicationDbContext through application layer.

[@itsabhinav100]

How do you rate Biarity Sieve for Sorting , Filtering and Pagination? although it has some challenges for VO or complex types

[@MilanJovanovicTech]

I can't rate it since I never used it 🤷‍♂️

[@odd2b1]

I love that library and other one which i use is Gridify

[@swift4511]

Can I use the CreateAsync method of the PagedList with the repository, build the query inside method, and then pass it to the PagedList? Or is it better to move CountAsync() and ToListAsync() out of the PagesList to maintain separation of concerns?

[@MilanJovanovicTech]

Sure, you can just pass in the pre-computed data to PagedList

[@mohamedadel936]

Great! Thanks Milan ❤

[@MilanJovanovicTech]

You bet!

[@beaufindlay2832]

Thanks for the great video, I've followed your clean architecture course which is great too. I'm building a project off the back of that and wondering how you've structured the IApplicationDbContext interface here to use directly in the handler as oppose to a repository interface as the DbContext is in the infrastructure layer. How is this interface being registered in the DI container? Do you have any code examples of what that interface could look like?

[@MilanJovanovicTech]

services.AddDbContext(_rest of ef config_); You can use this overload 👆

[@Pawelogll]

Why you didn’t use graphql with EF Core ?

[@MilanJovanovicTech]

REVERSE: Why SHOULD I use it in the first place?

[@GeekDriven]

Any thoughts on creating a tutorial for Cursor Pagination or any other types along with explaining the benefits of the same? As always great videos. 😇

[@MilanJovanovicTech]

I already made a video on Cursor pagination

[@tibba69]

How would you do dynamic select/projection?

[@MilanJovanovicTech]

I'd say only if you're not used to the second approach. We can argue for both approaches, if we want to

[@toragodzen]

Could you make implicit operator for Sku, so we will remove type casting and just use Sku where string is expected?

[@MilanJovanovicTech]

EF has to know that you want it to be a string and not a Sku :/

[@allannielsen4752]

A better approach in my opinion since it is a value object is to use a EF Converter... Sku.HasConversion... I think it is.

[@khanfaizan05]

Wonderful. Thanks ❤

[@MilanJovanovicTech]

Thank you too!

[@grzegorzfedoryka5045]

You could do sorting using dynamic linq public static IQueryable SetOrdering(this IQueryable query, string orderColumn, string orderingDirection) { if (string.IsNullOrEmpty(orderColumn)) { return query; } return query.OrderBy($"{orderColumn.Trim()} {orderingDirection.Trim().ToLower()}"); } Ofc column and sort order would be validated in validation step

[@MilanJovanovicTech]

I used that library before, works pretty well

[@cristhianjuliancubides6269]

I have a question, you are getting all the products with context.Products, if I have 5000 items it gets them all and then you are applying the filtering. Therefore the filter would not make sense because the objects are being loaded into memory and then the paging is applied. I have a doubt, thank you

[@MilanJovanovicTech]

No, the first is just an IQueryable and it doesn't touch the database until we call ToListAsync

[@cristhianjuliancubides6269]

Milan, but when I debug the IQueryable already has all the results, without the TolistAsync, why does this happen?, I am waiting for your answer, thanks

[@matthewrossee]

@@cristhianjuliancubides6269 Probably because debugger triggers the evaluation of the iqueryable so you can see the results. Are you sure you don't click something like "show me the results (this will force the enumeration)"?

[@PGWalkthrough]

can you post the result query generated by EF? It would be nice to know how its being done in Sql Server.

[@MilanJovanovicTech]

Will try if I can find some time - but nothing special, just your standard WHERE/ORDER BY/OFFSET query

[@PGWalkthrough]

@MilanJovanovicTech thanks for your reply. I am actually trying to implement pagination and sorting in sql query but i dont seem to find the best approach. The main problem is sorting as they mainly recommend to have a massive switch statement with a case for every column in the table or i have to write a dynamic query but have the risk of sql injection as the column name param is coming from front end. Thanks.

[@ДаниїлТерентьєв]

Thank you for useful video! I was wondering, whether we are able to create generic method for sorting not only for the specific class, but for all entites. Is it possible to do in correct way? Because I tried and it looks like sh!t)

[@MilanJovanovicTech]

Yes you can! What does it look like?

[@ДаниїлТерентьєв]

@@MilanJovanovicTech for now, I used specification pattern. Was inspired by your previous video). In my test assignment I receive sorting and paging via query parameters, which can be null. Therefore I decided to create specification inheritor from base specification. And IN RUNTIME apply different things, like sorting by column name and so on to it. It is not predefined like you shown in the video. What do you think about this. Is this a good approach? Thank you for reply :)

[@devilspower100]

The issue on 06:00 is it because of value object thing having setup with valueConverters? I run into this issue a few times.

[@MilanJovanovicTech]

Yeah, precisely why

[@reinoboonstra]

Thank you, very helpful. Nice clean coding as well 👍

[@MilanJovanovicTech]

Thanks! Glad you liked it :)

[@swift8995]

Can I return the PagedList with a list of domain models from the repository and map it in a query handler to a PagedList of DTOs to send as a response?

[@MilanJovanovicTech]

Too much overhead, no?

[@krccmsitp2884]

17:22 "by incrementing the page *number" 😉

[@MilanJovanovicTech]

Happens 😁

[@Wfmike]

Sorting input can be a bit better using enum string.

[@MilanJovanovicTech]

That's a nice approach

[@milanlimbu4417]

This theme looks beautiful can you provide me the name of it... and Thank for the tutorial Learned alot from you

[@MilanJovanovicTech]

ReSharper syntax highlighting

[@valcron-1000]

At 21:02 aren't you performing the query twice at the DB level? Once to get the `totalCount` and a second one to get `items`? If so, how can we avoid this?

[@MilanJovanovicTech]

This is a typical issue with this type of pagination. You could write SQL and a batched query that would fetch both values in one round trip.

[@EnriqueAndreHernandezLeon]

OMG you're the man! Thanks

[@MilanJovanovicTech]

Sure thing! :)

[@Tamer_Ali]

Hi Milan, thank for the video. are you going to refactor the sorting method to be extension method and generic? var list =await context .Users .OrderDescendingByField(nameof(User.Name)) // OrderByField for asc .ToListAsync();

[@MilanJovanovicTech]

Yeah, could be doable - not planning on it. But perhaps with reflection.

[@petherpettersson6152]

System.Linq.Dynamic.Core makes it possible to just order by a string, a little easier but will need to check if the column is valid in some other way,

[@VegaAldrinM]

Hi, what if in page 2 and tried to search an item available in page 1 do I still get that item? Or every search is need to reset the pageNumber to 1?

[@MilanJovanovicTech]

You'd get what matches that search - but on page 2

[@mariomandzukic8676]

Is there a way to set up filtering and orderby to be like pagedlist generic?

[@MilanJovanovicTech]

With paged list it's easy to fetch current page / count all records. But how would you build generic filtering with different entity types? I'm sure we can come up with some abstraction to provide the desired filter and sorting. But is it worth it?

[@mariomandzukic8676]

@@MilanJovanovicTech Yes was wondering about that since orderby and filtering dont look if i may say appealing to me but as you say its not worth it to go that deep in abastraction

[@alang39564]

How would you approach a case where you need to paginate child entites of an aggregate?

[@MilanJovanovicTech]

How would that work from an API consumer perspective?

[@alang39564]

@@MilanJovanovicTech let's say you have a Shop (aggregate root) which has N employees and you want to get api/shop/{shopId)/employees?page=1&pageSize=10

[@zheniachubarov3384]

Could you please show how to unit test internal classes?

[@MilanJovanovicTech]

Expose it with InternalsVisibleTo

[@techpc5453]

[@MilanJovanovicTech]

What is your story?

[@MaksymVasylyshyn-dx8hy]

How about use approach which use only 3 models it's: PaginationOptions, SortingOptions, FilteringOptions and use enum instead string in case sorting for example, it's make code more cleaner and remove reducant parameter, I wish to know how to implement this thank, you, I can share the post in LinkedIn where I find it

[@MilanJovanovicTech]

All improvements

[@jesusorozco294]

With Dapper?

[@MilanJovanovicTech]

I'm using EF Core here

[@SureshKumar-rz7dn]

I use a nuget package called sieve to does this .

[@MilanJovanovicTech]

Oh cool, let me check that out

[@SureshKumar-rz7dn]

@@MilanJovanovicTech Yes , please let us know your thought about this 🙂

[@mrwalkan]

Why not GrapaQL?

[@MilanJovanovicTech]

Why _would_ it be GraphQL?

[@jayakumar2927]

Source code needed

[@MilanJovanovicTech]

I share it on Patreon

[@wolfgang5945]

How to view MVC with it 🤔? I was trouble

[@MilanJovanovicTech]

Haven't used that in ages

[@idanqwe1]

will that work with .net 6?

[@MilanJovanovicTech]

Yes

[@nghianguyen170192]

with OData, you dont need to do all implementation by youself.

[@MilanJovanovicTech]

It's good to know the fundamentals

[@RafiXWPT]

Actually what you did there is very bad. The much better solution in to make thing more generic by create more interfaces and base query handlers. Also, way you doing sorting is ugly. You can just use reflection and make it work with whatever object you like without any problem. I created such generic mechsnism in my project using cqrs and efcore and developers who are using it loves that you can add new lists in just minute by inheriring some base generic classess

[@MilanJovanovicTech]

I'm still a junior engineer, don't be so rude

[@RafiXWPT]

@@MilanJovanovicTech you are junior? Though you are senior who create content for less experienced devs. Sorry then.

[@OlivierDALET]

@@MilanJovanovicTech Don't mind him! Your content was pretty good! No reflection means better speed, base classes and generic interfaces may be needed if you were to craft a general-purpose lib such as Sieve or AutoQueryable, but what you demonstrated simply allows anyone aiming at this goal to get started and generalize any way they'd like What's more, promoting heavily layered and generic architecture is at the very least debatable (Heard of the heated debate between cmuratori and uncle Bob of "clean code" fame? One comes from the native video game world where performance is paramount, the other is a book-author consultant who goes full with DI, virtual dispatching and OO patterns). From bare-bone C-like code to 50-layer architectures there's a middle ground, but I must admit that, age helping, I'm leaning more and more towards the less abstraction side (and I've been professionally using C# for 20y now) Simply said, there's nothing in this video you'd have to feel ashamed of. Even as a senior dev, I found inspiration here for my own use cases!

[@MilanJovanovicTech]

@@OlivierDALET Thanks a lot, I appreciate it. I'm not really a junior, just messing with OP a little. I do tend to not overcomplicate my videos, and let the viewer find what value he can and take it from there into their own implementation. If they choose to make it more complex and entirely generic, that's great for them.

[@kdakan]

Too much coding to deliver such a simple standard feature. I have a library that does this and much more without any coding.

[@MilanJovanovicTech]

Sometimes it's good to know the fundamentals, instead of reaching for a library for everything

[@techpc5453]

[@MilanJovanovicTech]

💥💥