Dynamic Sorting (OrderBy) Based On User Preference

Introduction

It is easy if the number of preferences is 1 but in case we need to generate a LINQ code where the OrderBy count is more, then we need to use the concept of the dynamic query builder.

Store user preference in DB

Let us suppose we have the data in an Employee table and our organization wants to sort the data with multiple columns. So, we need to store the data of preferences.

EmpId -> EmpName -> Email -> DOB -> Salary -> Experience

Now, we stored the preference in another table. And, the admin selected to order the data first by Employee Name, then Salary, and at last, Experience.

That means that the employee whose name comes first in alphabetical order and has the highest salary and more experience should come first. So, the LINQ query will be easy if the sort preferences are fixed; however, in case of dynamic sorting, it will be tough.

For normal cases, the LINQ query will look something similar to the following.

var EmpData = dbcontext.tbl_Emp.OrderBy(c => c.EmpName).ThenByDescending(c => c.salary).ThenBy(c => c.Experience).ToList();

To store the preference, we will create a new table tbl_UserPreferences.

PrefId -> UserId -> TableName -> ColumnName

Note
You can design the table based on your requirement. I have a few more columns in my table but am mentioning only the basic ones which we need.

Get preference data from the table

var preferences = dbcontext.sp_UserSortPreference(table_name,UserID).ToList();
List<string> sortBy = new List<string>();
foreach (var preference in preferences)
{
  ECNumberHelper obj = new ECNumberHelper();
  sortBy.Add(obj.GetFieldName(preference.Option_Text));
}

Now, we have the preference data in the variable sortBy.

Create a helper method

Here, we will use the generic concept to generate the OrderBy query based on the stored preference. So, the helper method will look something like this.

public static IQueryable<T> OrderBy<T>(this IQueryable<T> source, string columnName, bool isAscending = true)
        {
            if (String.IsNullOrEmpty(columnName))
            {
                return source;
            }

            ParameterExpression parameter = Expression.Parameter(source.ElementType, "");

            MemberExpression property = Expression.Property(parameter, columnName);
            LambdaExpression lambda = Expression.Lambda(property, parameter);

            string methodName = isAscending ? "OrderBy" : "OrderByDescending";

            Expression methodCallExpression = Expression.Call(typeof(Queryable), methodName,
                                  new Type[] { source.ElementType, property.Type },
                                  source.Expression, Expression.Quote(lambda));

            return source.Provider.CreateQuery<T>(methodCallExpression);
        }

** I found this method on Google search.

Source

IQueryable format to speed up the performance.

isAscending - If the column needs to be ordered by Ascending or not.

Write a query to fetch data from the Employee table and bridge the dynamic query

var empData = dbcontext.tbl_Emp.AsQueryable();

Now, we have a query ready. We can pass this query and generate the OrderBy query. As we already have preference added inside the sortBy variable, we will bridge those inside our query with the help of previous helper method.

foreach (var val in sortBy)
              {
                    empData = Helper.OrderBy(empData, val, false);
              }
        var resultList = empData?.ToList();
return View(resultList);

That’s all. Now, we have our data based on the sort preference. Pass the data from Controller to View and display in a grid (which I believe is an easy task for experts like you. Although I am happy to help if you stuck somewhere.)

Summary

Today, we tried a program where we can generate a dynamic OrderBy using the generic method. If you need any help in implementation, feel free to reach me via the comment box. That’s all for now. See you soon with a new article "Trying Python Django with Visual Studio 2019". Follow me to keep updated. Thank You!