Sorting and filtering using dynamic parameters

Question

I have this code which I am upgrading to .NET 8.

The intention is to sort and filter the resultset using the parameters. Right now it has a lot of if else and redundant code inside the blocks.

I would ideally like to refactor this code to avoid redundancy and I am thinking about using a predicate, but I am unsure on how to proceed.

Code:

var model = new PagedResourceModel(); var resources = _context.DistrictResources .Where(t => t.ResourceType == resourceType); var resourceCount = resources.Count(); // there are 6 similar filters if (!string.IsNullOrEmpty(request.filter.FName)) { resources = resources .Where(r => r.FName.ToUpper().Contains(request.filter.FName.ToUpper())); } if (!string.IsNullOrEmpty(request.filter.Type)) { // same as above using request.filter.Type } model.LastRow = resources.Count(); var sortField = request.sortField.ToUpper(); var sortDirection = request.sortDirection.ToUpper(); switch (sortField) { case "FNAME": if (sortDirection == "ASC") { resources = resources.OrderBy(r => r.FName) .Skip(request.pageNumber - 1).Take(request.pageSize); } else { resources = resources.OrderByDescending(r => r.FName) .Skip(request.pageNumber - 1).Take(request.pageSize); } break; case "TYPE": //same as above using r.Type break; default: resources = resources.OrderBy(x => x.DisplayName) .Skip(request.pageNumber - 1).Take(request.pageSize); break; } model.Rows = resources.ToList(); return model; 

The request type is:

public class ResourceRequest { public int pageNumber { get; set; } public int pageSize { get; set; } public string sortField { get; set; } public string sortDirection { get; set; } public RequestFilter filter { get; set; } } public class RequestFilter { // many more filters here public string? FName { get; set; } public string? Type { get; set; } } 

What would be a good way to refactor this?

_context used here is of type Microsoft.EntityFrameworkCore.DbContext

Answer 1

Ordering dynamically

This can be easily achieved via an extension method, something like this

public static IEnumerable<T> Order<T, TKey>(this IEnumerable<T> source, Func<T, TKey> selector, bool ascending) => ascending ? source.OrderBy(selector) : source.OrderByDescending(selector); 

Usage

resources = resources.Order(r => r.FName, sortDirection == "ASC"); 

Filtering dynamically

Similarly you can define an extension method here as well, something like this

public static IEnumerable<T> WhereIf<T>(this IEnumerable<T> source, Func<bool> condition, Func<T, string> selector, string propertyValue) => condition() ? source.Where(m => selector(m).ToUpper().Contains(propertyValue.ToUpper())) : source; 

Usage

var fNamePresent = () => !string.IsNullOrEmpty(request.filter.FName); resources = resources.WhereIf(fNamePresent, r => r.FName, request.filter.FName); 

---

Sandbox: https://dotnetfiddle.net/UW8ZmF

Answer 2

When working with DbSet in Entity Framework, you are working with IQueryable and Expression which will be translated into a raw sql. When you convert that into IEnumerable (e.g. ToList, ToArray ..etc.) it would execute the current query against the database, and the returned results will be stored in memory.

This approach is somewhat fine with single or a very small batch of results. However, when you have more rows to fetch from the database, or multiple calls, it would be a problematic. As it would result in a high memory allocation.

To solve this, you either create a view, store procedure, or a table function in your database to return the desired results and define it in your context, or extend the IQueryable by building your custom filtering. Both options are valid approaches, and depends in which are you more comfortable working with.

This is also applicable in any ORM that uses IQueryable such as LINQ to DB.

Now, for your code, you have multiple filters, we can start with filters that would commonly used such as Sorting.

The simplest way to extend IQueryable is reusing existing Linq extensions. like Peter Csala answer's, but for IQueryable instead of IEnumerable like this :

public static IOrderedQueryable<T> Order<T, TKey>(this IQueryable<T> source, Func<T, TKey> selector, bool ascending) => ascending ? source.OrderBy(selector) : source.OrderByDescending(selector); 

However, for more complex filtering, you may need to implement a filter builder that would handle all filters and applying them accordingly then return the results as IQueryable.Next, you need to implement an IQueryable extension that would use the builder.

Something like this :

public sealed class RequestFilterBuilder<T> { private readonly IQueryable<T> _source; private readonly RequestFilter _requestFilter; public RequestFilterBuilder(IQueryable<T> source, RequestFilter requestFilter) { _source = source; _requestFilter = requestFilter; } public IQueryable<T> Build() { // apply filters against _source then return the results // you can extend this class to have multiple classes // or just do private methods for each filter // to handle each filter separately. return _source; // after applying the filters. } } 

then your extension method would be :

public static IOrderedQueryable<T> UseRequestFilter<T>(this IQueryable<T> source, RequestFilter filter) { var builder = new RequestFilterBuilder(source, filter); return builder.Build(); } 

Now, you can do this :

var result = resources .UseRequestFilter(request) .ToList(); 

You can also apply UseRequestFilter to any other entity that would use RequestFilter as well.