Intro
Son, pick the right tool for the problem and be a master of your work.
Walter McClain
My dad’s wise words ring loud as I get older, even when developing software. At Ronin we believe using a mature framework or component library is preferable over doing it ourselves. We go on many journeys of Digital Transformation with companies where one of our specialties is identifying existing line-of-business applications that are candidates for a complete born-in-the-cloud rewrite; allowing us to leverage all the power and features of Azure.
During these travels we notice common development patterns. We perfect them. We teach our Ronins and customers. One interesting pattern that has become one of our favorites is the use of Kendo Grid for Angular with NHibernate Linq provider and IQueryable.
Problem
One common problem we encounter when rewriting line-of-business applications for clients is displaying tabular data to the end user. Our clients are large. Most have been in existence for over 20+ years. They are in healthcare where regulations require them to retain data for long durations. This translates to data stores containing many, many millions of records.
The issue arises not in the data but in the development approach. Developers are lazy and when developing UI/UX that displays data they read it all into memory and allow a component to handle sorting, filtering and paging on the client-side. While this is fine for small datasets, performance issues occur for the user when you have record counts of over a 1000.
Solution
I assume that if you get this far you understand what Kendo Grid, IQueryable and NHibernate are. Many articles and blog posts have been made about them containing deep detail and you can just google them.
As I mentioned above we like mature frameworks and component libraries. Kendo and specifically Kendo Grid for Angular is one of our favorite to use.
https://www.telerik.com/kendo-angular-ui/components/grid/how-to/filter-all-columns/
On the server-side in your .NET Core API project just grab the Telerik NuGet Kendo.DynamicLinqCore when developing your controller. This contains the IQueryable extensions that make doing server-side processing amazingly simple and elegant.
PS> Install-Package Kendo.DynamicLinqCore
Example – Web API Controller
using Kendo.DynamicLinqCore;
[HttpPost]
public IActionResult Contacts([FromBody] DataSourceRequest requestModel)
{
return _dbSession.Query<Contact>()
.Select(c => new ContactViewModel // Not required but this shows how to do projection into a view model
{
ContactId = c.ContactId,
CompanyName = c.CompanyName,
ContactName = c.ContactName,
City = c.City,
ContactTitle = c.ContactTitle
})
.ToDataSourceResult(requestModel.Take, requestModel.Skip, requestModel.Sort, requestModel.Filter, requestModel.Aggregate, requestModel.Group);
}At first glance you may be wondering, would this not first read all the data in from the database and then do the filtering, sorting, paging, etc in memory? Yeah that’s what I thought too, but it doesn’t and I have done SQL tracing to see it in action. The library is using Dynamic Linq to create Linq queries based on the request that it is given. NHibernate’s Linq provider then translates this into a proper SQL query.
Example – Angular/TypeScript
On the client-side in your Angular Component implement the onStateChang(state: State) method and then in the Kendo Grid Component in your HTML template, set this method as the dataStateChange event handler.
Then in your method you get handed the new grid state which represents the client-side version of the DataSourceRequest object. Now you will take this and pass it to the service that will then POST it up to your API controller.
public onStateChange(state: State) {
this.gridState = state;
this.contactService.load(state);
}
And your service load code will look something similar to the following.
load(newGridState?: State): Observable<GridDataResult> {
return this.http.post<GridDataResult>(`${this.baseUrl}/search`, newGridState).pipe(
catchError(this.handleError('load contacts failed’, null))
);
}Finally, this approach works great for about 75-80% of the needs for display large amounts of data. It will break down when you have very complex search needs across complicated data models. In those cases, you can still use the DataSourceRequest to get all the grid state information you need. With that information you can then construct your own NHibernate QueryOver queries. Again, like dad says… pick the right tool for the right problem.
Cheers!
