Blogs

Sorting in Entity Framework Core

Continuing from my last post. Let’s add a few endpoints that support sorting. If you’d like the source code, it can be found here.

First, I will add a PagingViewModel.cs file to the Database project:

public class PagingViewModel
{
    public int Start { get; set; }
    public int Count { get; set; }
    public string? OrderBy { get; set; }
    public bool IsAscending { get; set; }
}

This contains the Start index and the Count (which is used for pagination). Also, it has an OrderBy string as well as IsAscending which are both used for sorting.

Next, let’s look at the first implementation in the SchoolService.cs file:


public async Task<List<Enrollment>> GetEnrollmentsV1(PagingViewModel model)
{
    var query = db.Enrollments.AsNoTracking()
        .Include(x => x.Course)
        .Include(x => x.Student)
        .OrderBy(x => x.EnrollmentID)
        .Skip(model.Start)
        .Take(model.Count);
    switch(model.OrderBy?.ToLower())
    {
        case "courseid":
            query = model.IsAscending
                ? query.OrderBy(x => x.CourseID)
                : query.OrderByDescending(x => x.CourseID);
            break;
        case "studentid":
            query = model.IsAscending
                ? query.OrderBy(x => x.StudentID)
                : query.OrderByDescending(x => x.StudentID);
            break;
        case "coursetitle":
            query = model.IsAscending
                ? query.OrderBy(x => x.Course.Title)
                : query.OrderByDescending(x => x.Course.Title);
            break;
        case "studentlastname":
            query = model.IsAscending
                ? query.OrderBy(x => x.Student.LastName)
                : query.OrderByDescending(x => x.Student.LastName);
            break;
        default:
            query = model.IsAscending
                ? query.OrderBy(x => x.EnrollmentID)
                : query.OrderByDescending(x => x.EnrollmentID);
            break;
    }

    return await query.ToListAsync();
}

This grabs the enrollments, using pagination (skip & take), then performs a switch on the OrderBy field. If the field matches a few pre-defined strings, then it orders the query accordingly. Note, in the case statements, it uses IsAscending to sort in ascending or descending order. Let’s look at the Traditional API School controller changes for this version:

private const int MaxPagingSize = 500;

[HttpPost, Route("v1")]
public async Task<IActionResult> GetEnrollmentsV1([FromBody] PagingViewModel paging)
{
    if(paging == null || paging.Count > MaxPagingSize)
    {
        return BadRequest();
    }

    var result = await schoolService.GetEnrollmentsV1(paging);
    return Ok(result);
}

This function is a Post rest call, and the route is v1. It receives the pagination view model in the body. First, it validates the paging view model. If it’s null or requests too many records, then it returns a BadRequest (400). Next, it retrieves the enrollments from the school service and returns them. Let’s test this via the swagger endpoint.

As you can see, the request body sets the paging parameters, and the response contains the results, sorted accordingly. Not bad.

In C# there’s usually more than one way to accomplish a task and now I will talk about another one for sorting. This one will use Dynamic LINQ. Dynamic LINQ is a helper library that allows the user to specify fields using strings. Rather than having a large switch statement, we can simply use the order by field directly. First, we need to install the library. Right-click on the Database project, and select “Manage Nuget Packages”. Search for “System.Linq.Cynamic.Core” and install it.

Next, let’s modify the school service in the database project to use dynamic LINQ.

public async Task<List<Enrollment>> GetEnrollmentsV2(PagingViewModel model)
{
    var query = db.Enrollments.AsNoTracking()
        .Include(x => x.Course)
        .Include(x => x.Student)
        .OrderBy(x => x.EnrollmentID)
        .Skip(model.Start)
        .Take(model.Count);

    query.OrderBy(model.OrderBy + (model.IsAscending ? " ASC" : " DESC"));
    return await query.ToListAsync();
}

Like the previous example, this function sets the pagination parameters. Unlike the previous example, this function uses the order by field directly and sets the order via the IsAscending variable (ASC or DESC). This looks nice but it does come with a few caveats. Firstly, if you set the order by field to something that doesn’t exist, this will throw an exception. So the API code should surround this with a try/catch block. Second, if you would like to sort by the fields in the foreign key relationships, then you must format them with a ‘.’ in them. For example, to sort by Course Title, the string would be “Course.Title”. Let’s look at the endpoint in the Traditional API:

[HttpPost, Route("v2")]
public async Task<IActionResult> GetEnrollmentsV2([FromBody] PagingViewModel paging)
{
    if (paging == null || paging.Count > MaxPagingSize)
    {
        return BadRequest();
    }

    if (string.IsNullOrWhiteSpace(paging.OrderBy))
    {
        paging.OrderBy = "enrollmentid";
    }

    try
    {
        var result = await schoolService.GetEnrollmentsV2(paging);
        return Ok(result);
    }
    catch(Exception ex)
    {
        return BadRequest(ex.ToString());
    }
}

This is very similar to the first approach, however, if the order by field is blank or null then it sets that field to the enrollment id. Next it gets the enrollments and that call is surrounded by a try/catch block. Let’s test it via Swagger.

As you can see, I’m pulling back the first 100 enrollments sorted by the student’s last name. It’s all working as intended. Great!

That’s all I’ve got for this post. Which one you prefer is up to you and either is a fine implementation. Thanks for stopping by!

kereszte

, , ,