Previously we’ve talked about client-side and server-side loading. As I promised before, today we’ll look at examples of server-side loading using AutoSFaP.
AutoSFaP
AutoSFaP or Auto Sort, Filter and Paging is nuget package I implemented when I was learning ASP.Net core WebAPI. It is very easy to use and may help you create endpoints with server-side paging sorting and filtering. AutoSFaP will create LinQ based on model sent to API by client. It requires no work from you, everything is generic. AutoSFaP provides IQueryable<T> for EF to translate it to SQL that means you have to use EF to use AutoSFaP.
Project is Open Source and available on my repositories https://github.com/MartinBialkowski/AutoSFaP. Please notice that it’s my first nuget package and it’s still under development. It is usable and my test project is using it already, but it may take some work to setup it.
Configuration
Currently setup is kinda painful. First of all you have to specify mappers. Package has prepared converters using AutoMapper, all you need to do is create mapping profiles. I’ll think about solution to become independent from AutoMapper, but for now you have to bear with it.
CreateMap<StudentFilterDto, FilterField<Student>[]>()
.ConvertUsing(new FilterFieldsConverter<StudentFilterDto, Student>());
CreateMap<PagingDto, Paging>(MemberList.Source);
CreateMap<string, SortField<Student>[]>()
.ConvertUsing(new SortFieldsConverter<Student>());
After that register interface in your DI container
builder.RegisterType<DataLimiter>().As<IDataLimiter>();
You’ll need to create DTO for every entity. AutoSFaP use splitted models for Sorting, Filtering and Paging, thanks to that you can easily specify where you allow functionality. So it’s possible to have only server-side filtering and nothing more.
Look inside
We’ll go thru basic scenario. Let’s start from entity model.
public class Student: IEntityBase
{
public int Id { get; set; }
public int CourseId { get; set; }
[Required, StringLength(50)]
public string Name { get; set; }
public Course Course { get; set; }
}
After that let’s create DTO, everything is pretty straightforward. It will decide which properties can be filtered.
public class StudentFilterDto
{
public int? Id { get; set; }
public int? CourseId { get; set; }
public string Name { get; set; }
}
Now let’s look at models that are used by AutoSFaP.
public class FilterField<T> where T : class
{
public string PropertyName
{
get => propertyName;
set
{
...
}
}
public object FilterValue
{
get => filterValue;
set
{
...
}
}
public Type PropertyType { get; set; }
protected Type ModelType { get; }
private string propertyName;
private object filterValue;
}
FilterField will make some validation while setting properties. Simply, FilterField works as dictionary with key propertyName to filter and value.
public class Paging
{
public int PageNumber { get; set; }
public int PageLimit { get; set; }
public int Offset => (PageNumber - 1) * PageLimit;
}
In Paging client will be able to pass page number and page limit, offset is used to create linq and it is calculated.
public class SortField<T> where T : class
{
public SortOrder SortOrder { get; set; }
public string PropertyName
{
get => propertyName;
set
{
...
}
}
protected Type ModelType { get; private set; }
private string propertyName;
Finally SortField looks similar to FilterField, but doesn’t have Value. SortOrder will describe if we want to sort ascending or descending.
It’s optional to create DTOs for internal types, but I encourage you to do that. First you’ll be free from my dependencies, second you’ll have greater control. It’s good practice to have own models used for public methods. Now you’re ready to use AutoSFaP
Usage
// GET: /api/students?pageNumber=1&pageLimit=3&Name=Martin&sort=CourseId,Name-
public async Task<ActionResult<Model>> GetStudents([FromQuery] PagingDto pagingDto, [FromQuery] StudentFilterDto filterDto, [FromQuery] string sort = "Id")
{
var sortFields = mapper.Map<string, SortField<Student>[]>(sort);
var filterFields = mapper.Map<StudentFilterDto, FilterField<Student>[]>(filterDto);
var paging = mapper.Map<PagingDto, Paging>(pagingDto);
IQueryable<Student> query = Context.Students.Include(s => s.Course);
var pagedResult = await dataLimiter.LimitDataAsync(query, sortFields, filterFields, paging);
return Ok(pagedResult);
}
Everything is pretty simple. First you need to map DTOs to business models, it’s easy using preimplemented converters. When you have all models you need to get IQueryable<T> from EF DbContext, and call LimitData or LimitDataAsync passing all needed parameters. Methods will work even if client didn’t pass any FilterField it will return not filtered data.
In comment you can see how would look url for action. Everything is taken from Query Params. Additionally swagger will support it as well and display correct properties.
##LimitData vs LimitDataAsync
IQueryable<T> LimitData(IQueryable<T> query, SortField<T>[] sortFields, FilterField<T>[] filterFields);
Task<PagedResult<T>> LimitDataAsync(IQueryable<T> query, SortField<T>[] sortFields, FilterField<T>[] filterFields, Paging paging);
LimitData will return IQueryable with applied sorting and filtering, you will have to call ToList() to make call to database. I decided to leave it like that to give you more control. This method is used to load whole data without paging. Having IQueryable will let you do more stuff before returning data to client.
For paged data you need to use LimitDataAsync. This method will do the same except it will additionally call database for specified page. Paged data contains requested by client page of data and additionally every information about current page like offset, size total number of pages and total numbers of items. These information are needed by client application to display paging properly.
public class PagedResult<T>
{
public int PageNumber { get; set; }
public int PageSize { get; set; }
public int TotalNumberOfPages { get; set; }
public int TotalNumberOfRecords { get; set; }
public List<T> Results { get; set; }
}
Outro
You can find whole example on my GitHub or Azure DevOps .
You can also find there AutoSFaP code. Please take a look, and if you find something difficult to understand let me know, and I’ll do everything I can to make it work for you.