Hey guys, I am Venkat and this is part 12 of web development with blazor video series. If
your application deals with huge volumes of data, performing operations like sorting and
paging on the client side is not a recommended approach. Trying to load the entire data set onto
the client machine in one go may explode both, the browser memory and rendering engine. So,
the recommended approach is to perform sorting and paging on the database server. This is the
approach that we typically use in real world web applications especially if the applications
deal with huge volumes of data. In this video let's discuss implementing both sorting
and paging on the database server. Our first step is to modify the server side rest api to provide
sorted and page data. For the rest api to be able to do that we need to provide it
with three things - the number of rows to skip, the number of rows to take and the columns by
which we want the data to be sorted. The rest api will then forward those three parameters
to entity framework core. Entity framework core will then forward them to the database engine.
The database engine will then perform these two operations sorting and paging and then return
the sorted and page data to the rest api. The rest api will then return that to the data grid
which will then display the data as expected. Our server side api already supports paging,
notice in the url we are passing two query string parameters - skip and take. We want to skip 10
rows and take the next 20 rows and look at the data that is returned by our api, it returned 20
rows starting with employee id 11 to employee id 30. We discussed implementing server-side
paging in part 10 of this video series. I'll include the link to the complete course page
in the description box below this video. Take a look at our api url, it is "api/employees",
so it's the employees controller that provides this employee data and if we take a look at our
employees controller, GetEmployees() method is right here. Notice, it is already taking in these
two parameters - skip and take. In addition to these two parameters, we want a third parameter,
this is going to be of type string and let's call it orderBy. Notice, next to the orderBy
parameter we have a little red squiggly and when I have the mouse over, it says optional parameters
must appear after all the required parameters. These two parameters, skip and take are optional.
Why are they optional? Well, that's because we specified the default values. Similarly, we also
want to make this orderBy parameter optional. Just in case if the end user did not sort the
data in the grid by any specific column we want the data to be sorted by EmployeeId column. So,
let's specify a default value of "EmployeeId". If you want, you can also include sort
direction - ascending or descending. If you don't explicitly specify the sort direction, by default,
the data will be sorted anyway in ascending order, so you may or may not include the sort direction.
To keep it simple, I'm going to actually remove it. Now, it is this GetEmployees() method of
employee repository that actually retrieves employee data from the database. So, even on this
GetEmployees() method of our employee repository we need to include this orderBy parameter. So,
I have both the employee repository class and the interface files open. If you're wondering where
these files are present? Well, they are present in the "Models" folder in BlazorProject.Server. So,
first on the GetEmployees() method within the interface, let's include the third parameter
string orderBy. Let's do the same thing even in this employee repository class. So,
if we scroll down a bit, our GetEmployees() method is right here, in addition to skip and take, we
want the third parameter, string orderBy and the default value is EmployeeId column. Notice, this
Employees property on this appdbcontext instance is of type DbSet. Just like how it is
supporting these two link methods, skip and takeit also supports orderby and to this orderby method
we pass this incoming orderBy parameter. We have a red squiggly under OrderBy() and that's because
we cannot pass a string parameter to orderby. For example, if we want the data to be sorted by
EmployeeId column, we have to specify a lambda here, something like employee goes to employee dot
and then we specify the property by which we want the data to be sorted, but this is not what we
want. We want to be able to pass this incoming string parameter because it is going to contain
the comma separated list of columns by which we want the data to be sorted. If this is not clear
at the moment, don't worry it will be in just a bit. So to be able to pass string parameters
to these link methods like orderby and where, we're going to use something called dynamic linq.
The website address is dynamic-linq.net. So, click on getting started, documentation,
basic usage, simple querying and then if we scroll all the way to the bottom, notice here
we are passing a string parameter to the orderby linq method and this string parameter contains
the list of columns by which we want the data to be sorted. We want to download and install
dynamic linq nuget package. Notice, on the top right hand corner we have this download link,
click on that and then we want to download the nuget package. So, click on nuget download.
Here is the command that we want to use to install this dynamic linq package. So, let me copy that.
In visual studio, go to package manager console, make sure BlazorServer project is
selected here and then let's paste the command that we just copied and execute it. Notice,
we still have a red squiggly under orderby and that's because we're missing a namespace.
So using System.Linq.Dynamic.Core and then if we scroll down, notice we no longer have that error
on OrderBy. Next, in our employees controller let's pass this incoming orderby parameter
to GetEmployees() method on employee repository we're already passing skip and take, so let's also
do the same with orderby. With all these changes in place, let's run the project and test our API.
There we go, we see our application home page as expected. Before we navigate to our API
URL and test, let's go to the output window in visual studio and then from the drop-down list
here select web server and let's clear everything that's already logged here and then in the browser
let's navigate to our api url "api/employees". We want to skip 10 rows and take 20 rows and
we want to sort the data by EmployeeId column, so let's use orderBy = EmployeeId descending.
Now, one important point to keep in mind here is, don't include double quotes around orderby
query string parameter value. If you include double quotes it doesn't work. Notice, the data
in the result set is sorted by EmployeeId column in descending order. In the database we have a
total of 34 rows, so employee data is sorted by EmployeeId column, it skipped the first 10 rows
and then took the next 20 rows, that's why we have rows from 24 to 5, and if we take a look
at the query that is issued to the database, here is our select query, select all the columns from
employees table order by EmployeeId column in descending order and then we have the parameter
values for skip and take, p0 and p1, p0 is 10 and p1 is 20. Our data grid that displays the
data is present in this razor file index.razor. At the moment, only paging is enabled. Let's also
enable sorting and for that, all we need to do is set this property AllowSorting ="true".
To provide data to this grid we are using a custom adapter EmployeeAdaptor and this EmployeeAdaptor
is present in the "Services" folder in our client project. So let's open this file.
Notice, we are injecting our client-side service IEmployeeService into the EmployeeAdaptor and on
this service, we have GetEmployees() method, it is this method that calls our server side REST API.
Both, the service interface IEmployeeService and the implementation class are present in
the Services folder in our client project and I have these two files already open here. Here is
the interface. So on this GetEmployees() method, in addition to skip and take,
let's include orderby parameter and let's do the same thing even in the EmployeeService class.
We have our GetEmployees() method here, in addition to skip and take we want orderby.
Notice our REST API URL here, we are passing these two incoming parameters skip and take
as query string parameters, we want to do the same thing with orderby as well. Let me actually bring
this method call to the next line, so we can see the entire code without having to scroll
to the right. So, in addition to skip and take you also want to pass orderby, the value for it
is the incoming orderby parameter. Next, in index.razor page, in addition to enabling sorting,
let's also specify some sort settings, for that we use component. We don't want to
allow unsort, so let's set AllowUnsort = "false". When the data grid first loads, we want the data
to be sorted by EmployeeId column in ascending order, for that let's include ,
inside this we include and then specify the field we want to sort by,
EmployeeId column, so let's specify EmployeeId. Finally, in EmployeeAdaptor class where we are
calling GetEmployees() method, in addition to passing skip and take parameter values,
we also have to pass value for orderBy parameter, so for that let's create a new variable here,
let's call it orderByString and initialize it with null. On this incoming parameter
dataManagerRequest, just like how we have skip and take properties,
we also have Sorted property. So first let's check if sorted property is not null,
notice this Sorted property returns a list of Sort objects, so i'm creating a variable here of type
List and storing that list in this variable and then each sort object contains these two
properties Name and Direction. For example, on the UI in the data grid if I click on EmployeeId
column to sort the data by EmployeeID column in ascending order, Name property will contain
EmployeeId and Direction will contain ascending or descending. So here we are building our orderBy
string dynamically, so what we want to be able to do is build a string that looks like this,
remember the data grid supports multi-column sorting, so here we are sorting by EmployeeId
column first in descending order and then by FirstName column in ascending order. So,
between the name of the column and direction there has to be a space and then between each column,
we need a comma and that's what we are doing with this line of code right here. So, we are selecting
Name and Direction with a space between both of them and then each column must be separated by
a comma and to get comma, we're using the string Join() method. All that is left is,
pass this orderBy string to GetEmployees() method. So, in addition to skip and take we
are passing orderBy as well. With all these changes in place let's run our project.
There we go, on the initial page load, data is sorted by EmployeeId column in ascending
order. Let's click on the Id column again, notice now the data is sorted in descending
order. Let's also test if multi-column sorting works as expected. So first the data is sorted
by Id column in descending order, next I want the data to be sorted by FirstName column
in ascending order, so I hold down the control key and then click on the FirstName column.
Notice multi-column sorting is not working as expected. We want the data to be sorted
first by EmployeeId column in descending order, but if you look at these id values
they are not in descending order, so let's understand what's going on? First let's close
the browser and place a breakpoint in ReadAsync() method and run our project in debug mode.
Notice on the initial page load, data is sorted by EmployeeID column in ascending order,
we want to further sort this data by FirstName column, so hold the ctrl key down and then click
on the FirstName column. Our breakpoint is hit, let me place another breakpoint here and then
continue the execution. Remember, we want the data to be first sorted by EmployeeID column and
then FirstName column, but notice when I hover the mouse over this orderBy string, the order here is
not correct. First we have FirstName column and then EmployeeId column. To correct this order,
all we need to do is reverse the elements in this sortlist. So, let's stop debugging,
on the sortlist variable, let's call Reverse() method. Run our project again and test.
Hold the control key down and then click on the FirstName column, notice first the data is
sorted by EmployeeId column in ascending order and then by FirstName column in
ascending order. That's it in this video. Thank you for listening.