entity framework core 3 dynamic order by not working

Question

public class Branch
 {
    [Sortable(OrderBy = "BranchId")]
    public long BranchId { get; set; }
    public string Name { get; set; }
    public string Code { get; set; }
    public string Type { get; set; }
}

this is my Model class and I also create a custom attribute

public class SortableAttribute : Attribute
    {
        public string OrderBy { get; set; }
    }

now i create a pagination with orderby descending but this code not working

public static async Task<IPagedList<T>> ToPagedListAsync<T>(this IQueryable<T> source,
            GeneralPagingRequest pagingRequest, int indexFrom = 0,
            CancellationToken cancellationToken = default(CancellationToken))
        {
            if (indexFrom > pagingRequest.PageNumber)
            {
                throw new ArgumentException(
                    $"indexFrom: {indexFrom} > pageNumber: {pagingRequest.PageNumber}, must indexFrom <= pageNumber");
            }

            var count = await source.CountAsync(cancellationToken).ConfigureAwait(false);
            var items = source.Skip(((pagingRequest.PageNumber - 1) - indexFrom) * pagingRequest.PageSize)
                .Take(pagingRequest.PageSize);
            var props = typeof(T).GetProperties();
            PropertyInfo orderByProperty;
            orderByProperty =
                    props.FirstOrDefault(x=>x.GetCustomAttributes(typeof(SortableAttribute), true).Length != 0);


            if (pagingRequest.OrderBy == "desc")
            {
                items = items.OrderBy(x => orderByProperty.GetValue(x));
            }
            else
            {
                items = items.OrderBy(x => orderByProperty.GetValue(x));
            }

            var result = await items.ToListAsync(cancellationToken).ConfigureAwait(false);
            var pagedList = new PagedList<T>
            {
                PageNumber = pagingRequest.PageNumber,
                PageSize = pagingRequest.PageSize,
                IndexFrom = indexFrom,
                TotalCount = count,
                Items = result,
                TotalPages = (int) Math.Ceiling(count / (double) pagingRequest.PageSize)
            };

            return pagedList;
}

but the result variable create exception

timur · Answer 1 · 2019-12-16T22:02:59.237

.OrderBy() requires a delegate that would tell it HOW to select a key, not the key value itself. So you are looking at some meta-programming here.

Naturally, you will look at building a dynamic LINQ Expression tree that will fetch a property for you:

// your code up above
PropertyInfo orderByProperty = props.FirstOrDefault(x => x.GetCustomAttributes(typeof(SortableAttribute), true).Length != 0);

var p = Expression.Parameter(typeof(T), "x"); // you define your delegate parameter here
var accessor = Expression.Property(p, orderByProperty.GetMethod); // this basically becomes your `x => x.BranchId` construct
var predicate = Expression.Lambda(accessor, p).Compile(); // here's our problem: as we don't know resulting type at compile time we can't go `Expression.Lambda<T, long>(accessor, p)` here

if (pagingRequest.OrderBy == "desc")
{
    items = items.OrderByDescending(x => predicate(x)); // passing a Delegate here will not work as OrderBy requires Func<T, TKey>
}
else
{
    items = items.OrderBy(x => predicate(x)); // passing a Delegate here will not work as OrderBy requires Func<T, TKey>
}

var result = await items.ToListAsync(cancellationToken).ConfigureAwait(false);
// your code down below

Problem with the above code - you don't know TKey upfront. Therefore we will have to go a level deeper and build the whole items.OrderBy(x => x.BranchId) expression dynamically. The biggest leap of faith here will be the fact the OrderBy is an extension method and it actually resides on IQueryable type. After you've got the generic method reference, you will need to build a specific delegate type when you know your property type. So your method becomes something like this:

public static class ExtToPagedListAsync
{
    private static readonly MethodInfo OrderByMethod = typeof(Queryable).GetMethods().Single(method => method.Name == "OrderBy" && method.GetParameters().Length == 2); // you need your method reference, might as well find it once

    private static readonly MethodInfo OrderByDescendingMethod = typeof(Queryable).GetMethods().Single(method => method.Name == "OrderByDescending" && method.GetParameters().Length == 2); // you need your method reference, might as well find it once

    public static async Task<IPagedList<T>> ToPagedListAsync<T>(this IQueryable<T> source, GeneralPagingRequest pagingRequest, int indexFrom = 0, CancellationToken cancellationToken = default(CancellationToken))
    {

        if (indexFrom > pagingRequest.PageNumber)
        {
            throw new ArgumentException(
                $"indexFrom: {indexFrom} > pageNumber: {pagingRequest.PageNumber}, must indexFrom <= pageNumber");
        }

        var count = await source.CountAsync(cancellationToken).ConfigureAwait(false);
        var items = source.Skip(((pagingRequest.PageNumber - 1) - indexFrom) * pagingRequest.PageSize)
            .Take(pagingRequest.PageSize);
        var props = typeof(T).GetProperties();
        PropertyInfo orderByProperty = props.FirstOrDefault(x => x.GetCustomAttributes(typeof(SortableAttribute), true).Length != 0);

        var p = Expression.Parameter(typeof(T), "x");
        var accessor = Expression.Property(p, orderByProperty.GetMethod);
        var predicate = Expression.Lambda(accessor, p); // notice, we're not yet compiling the predicate. we still want an Expression here

        // grab the correct method depending on your condition
        MethodInfo genericMethod = (pagingRequest.OrderBy == "desc") ? OrderByDescendingMethod.MakeGenericMethod(typeof(T), orderByProperty.PropertyType)
                                                                    :OrderByMethod.MakeGenericMethod(typeof(T), orderByProperty.PropertyType);

        object ret = genericMethod.Invoke(null, new object[] { items, predicate });
        items = (IQueryable<T>)ret; // finally cast it back to Queryable with your known T

        var result = await items.ToListAsync(cancellationToken).ConfigureAwait(false);
        var pagedList = new PagedList<T>
        {
            PageNumber = pagingRequest.PageNumber,
            PageSize = pagingRequest.PageSize,

            IndexFrom = indexFrom,
            TotalCount = count,
            Items = result,
            TotalPages = (int)Math.Ceiling(count / (double)pagingRequest.PageSize)
        };

        return pagedList;
    }
}

I must disclose I did get some inspiration from this answer here, so do check it out for further reading.

entity framework core 3 dynamic order by not working

1 Answers1