EF Core Specification pattern add all column for sorting data with custom specification

Question

I applied Specification Pattern for my .net core project. I also created custom specification for Include, Sorting, Paging etc.

I get sort value from api url with queryString and passing to custom specification class. In this class i added some switch case for determine which column should orderBy or orderByDescending

But there is too much columns in that table. So is there any way to apply that sort variable to all column at once ? or do i have to write all columns in to the switch ?

here is my custom specification class.

public class PersonsWithGroupsAndPrivileges : BaseSpecification<Person>
{
public PersonsWithGroupsAndPrivileges(string sort) : base()
{
    AddInclude(x => x.Group);
    AddInclude(x => x.Privilege);
    
    if(!string.IsNullOrEmpty(sort))
    {
        switch (sort)
        {
            case "gender": ApplyOrderBy(x => x.Gender); break;
            case "genderDesc": ApplyOrderByDescending(x => x.Gender); break;
            case "roomNo": ApplyOrderBy(x => x.RoomNo); break;
            case "roomNoDesc": ApplyOrderByDescending(x => x.RoomNo); break;
            default: ApplyOrderBy(x => x.Name); break;
        }
    }
}

}

ISpecification.cs Interface

using System;
using System.Collections.Generic;
using System.Linq.Expressions;

namespace XXXX.Core.Specifications
{
    public interface ISpecification<T>
    {
        Expression<Func<T, bool>> Criteria { get; }
        List<Expression<Func<T, object>>> Includes { get; }
        List<string> IncludeStrings { get; }
        Expression<Func<T, object>> OrderBy { get; }
        Expression<Func<T, object>> OrderByDescending { get; }
        Expression<Func<T, object>> GroupBy { get; }

        int Take { get; }
        int Skip { get; }
        bool IsPagingEnabled { get; }
    }
}

BaseSpecification.cs

using System;
using System.Collections.Generic;
using System.Linq.Expressions;

namespace XXXX.Core.Specifications
{
    public abstract class BaseSpecification<T> : ISpecification<T>
    {
    protected BaseSpecification(Expression<Func<T, bool>> criteria)
    {
        Criteria = criteria;
    }
    protected BaseSpecification()
    {

    }
    public Expression<Func<T, bool>> Criteria { get; }
    public List<Expression<Func<T, object>>> Includes { get; } = new List<Expression<Func<T, object>>>();
    public List<string> IncludeStrings { get; } = new List<string>();
    public Expression<Func<T, object>> OrderBy { get; private set; }
    public Expression<Func<T, object>> OrderByDescending { get; private set; }
    public Expression<Func<T, object>> GroupBy { get; private set; }

    public int Take { get; private set; }
    public int Skip { get; private set; }
    public bool IsPagingEnabled { get; private set; } = false;

    protected virtual void AddInclude(Expression<Func<T, object>> includeExpression)
    {
        Includes.Add(includeExpression);
    }

    protected virtual void AddInclude(string includeString)
    {
        IncludeStrings.Add(includeString);
    }

    protected virtual void ApplyPaging(int skip, int take)
    {
        Skip = skip;
        Take = take;
        IsPagingEnabled = true;
    }

    protected virtual void ApplyOrderBy(Expression<Func<T, object>> orderByExpression)
    {
        OrderBy = orderByExpression;
    }

    protected virtual void ApplyOrderByDescending(Expression<Func<T, object>> orderByDescendingExpression)
    {
        OrderByDescending = orderByDescendingExpression;
    }

    protected virtual void ApplyGroupBy(Expression<Func<T, object>> groupByExpression)
    {
        GroupBy = groupByExpression;
    }
    }
}

SpecificationEvaluator.cs

using System.Linq;
using DesTech.Core.Entities;
using DesTech.Core.Specifications;
using Microsoft.EntityFrameworkCore;

namespace XXXX.Infrastructure.Data
{
public class SpecificationEvaluator<TEntity> where TEntity : BaseEntity
{
    public static IQueryable<TEntity> GetQuery(IQueryable<TEntity> inputQuery, ISpecification<TEntity> specification)
    {
        var query = inputQuery;

    if (specification.Criteria != null)
    {
        query = query.Where(specification.Criteria);
    }

    query = specification.Includes.Aggregate(query, (current, include) => current.Include(include));

    query = specification.IncludeStrings.Aggregate(query, (current, include) => current.Include(include));

    if (specification.OrderBy != null)
    {
        query = query.OrderBy(specification.OrderBy);
    }
    else if (specification.OrderByDescending != null)
    {
        query = query.OrderByDescending(specification.OrderByDescending);
    }

    if (specification.GroupBy != null)
    {
        query = query.GroupBy(specification.GroupBy).SelectMany(x => x);
    }
    if (specification.IsPagingEnabled)
    {
        query = query.Skip(specification.Skip)
                     .Take(specification.Take);
    }
    return query;
   }
}
}

Answer 1

A simple way to do this is to use Reflection to get the property by name and then build the Expression<Func<PersonWithGroupsAndPrivileges, object>> expression.

Let's assume a specification class like this:

public class BaseSpecification<T>
{
    public Expression<Func<T, object>> OrderBy { get; private set; }
    public Expression<Func<T, object>> OrderByDescending { get; private set; }
    
    protected virtual void ApplyOrderBy(Expression<Func<T, object>> orderByExpression)
        => OrderBy = orderByExpression;

    protected virtual void ApplyOrderByDescending(Expression<Func<T, object>> orderByDescendingExpression)
        => OrderByDescending = orderByDescendingExpression;
}

Here is a fully working sample console project, that implements a PersonWithGroupsAndPrivileges<T> class and uses it on a Person class:

using System;
using System.Linq.Expressions;
using System.Reflection;

namespace IssueConsoleTemplate
{
    public class Person
    {
        public int PersonId { get; set; }
        public string Gender { get; set; }
        public string RoomNo { get; set; }
    }

    public class BaseSpecification<T>
    {
        public Expression<Func<T, object>> OrderBy { get; private set; }
        public Expression<Func<T, object>> OrderByDescending { get; private set; }
        
        protected virtual void ApplyOrderBy(Expression<Func<T, object>> orderByExpression)
            => OrderBy = orderByExpression;

        protected virtual void ApplyOrderByDescending(Expression<Func<T, object>> orderByDescendingExpression)
            => OrderByDescending = orderByDescendingExpression;

        protected void ApplySorting(string sort)
        {
            if(!string.IsNullOrEmpty(sort))
            {
                const string descendingSuffix = "Desc";

                var descending = sort.EndsWith(descendingSuffix, StringComparison.Ordinal);
                var propertyName = sort.Substring(0, 1).ToUpperInvariant() +
                                   sort.Substring(1, sort.Length - 1 - (descending ? descendingSuffix.Length : 0));

                var specificationType = GetType().BaseType;
                var targetType = specificationType.GenericTypeArguments[0];
                var property = targetType.GetRuntimeProperty(propertyName) ??
                               throw new InvalidOperationException($"Because the property {propertyName} does not exist it cannot be sorted.");

                // Create an Expression<Func<T, object>>.
                var lambdaParamX = Expression.Parameter(targetType, "x");

                var propertyReturningExpression = Expression.Lambda(
                    Expression.Convert(
                        Expression.Property(lambdaParamX, property),
                        typeof(object)),
                    lambdaParamX);

                if (descending)
                {
                    specificationType.GetMethod(
                            nameof(ApplyOrderByDescending),
                            BindingFlags.Instance | BindingFlags.Public | BindingFlags.NonPublic)
                        .Invoke(this, new object[]{propertyReturningExpression});
                }
                else
                {
                    specificationType.GetMethod(
                            nameof(ApplyOrderBy),
                            BindingFlags.Instance | BindingFlags.Public | BindingFlags.NonPublic)
                        .Invoke(this, new object[]{propertyReturningExpression});
                }
            }
        }
    }

    public class PersonsWithGroupsAndPrivileges<T> : BaseSpecification<T>
    {
        public PersonsWithGroupsAndPrivileges(string sort)
        {
            ApplySorting(sort);
        }
    }
    
    internal static class Program
    {
        private static void Main()
        {
            var p1 = new PersonsWithGroupsAndPrivileges<Person>("gender");
            var p2 = new PersonsWithGroupsAndPrivileges<Person>("genderDesc");
        }
    }
}

The code works with any class T.

Answer 2

You basically need two helper methods - one which extracts sort information (name and descending) from sort string, and another which builds dynamically and applies Expression<Func<T, object>> from it. Both go to the base generic class.

The first which handles patterns like {property}[[ ]{Desc}] (case insensitive) could be like this:

protected virtual void ExtractSortInfo(string sort, out string propertyPath, out bool descending)
{
    const string Desc = "Desc";
    propertyPath = sort;
    descending = false;
    if (propertyPath.EndsWith(Desc, StringComparison.OrdinalIgnoreCase))
    {
        propertyPath = sort.Substring(0, sort.Length - Desc.Length).TrimEnd();
        descending = true;
    }
}

and the second like this:

public virtual void ApplySort(string sort)
{
    if (string.IsNullOrEmpty(sort)) return;
    ExtractSortInfo(sort, out var propertyPath, out var descending);
    var parameter = Expression.Parameter(typeof(T), "x");
    var body = propertyPath.Split('.').Aggregate((Expression)parameter, Expression.Property);
    if (body.Type.IsValueType) body = Expression.Convert(body, typeof(object));
    var selector = Expression.Lambda<Func<T, object>>(body, parameter);
    if (descending)
        ApplyOrderByDescending(selector);
    else
        ApplyOrderBy(selector);
}

As an extra, this supports dot separated nested properties like blog.name for class Person having navigation property Blog having property Name.

Expression.Convert is for supporting value type (int, decimal, DateTime etc.) properties