Entity Framework linq orderby function

Question

I am new to Entity Framework and linq. I am working with asp.net mvc 5 and C#. I write a query with dynamic sorting as follows:

public static IEnumerable<T> OrderByDynamic<T>(this IEnumerable<T> source, string propertyName, bool Ascending)
{
    if (Ascending)
        return source.OrderBy(x => x.GetType().GetProperty(propertyName).GetValue(x, null));
    else
        return source.OrderByDescending(x => x.GetType().GetProperty(propertyName).GetValue(x, null));
    }

and in my repository I can write:

string sortExpr = "name";
_objDB.hotels.Where(s => (s.city_id = 1))
             .OrderByDynamic(sortExpr, Ascending).ToList();

This code works fine when sorting is on a column of a table, but I need to sort by a SQL function. I entered the function into the .edmx model with the following code

[EdmFunction("hotelReservation.Core.Data", "getHotelMinPrice_cap")]
public static int getHotelMinPrice_cap(int Hotel_id, int capacity, DateTime enter_date, DateTime exit_date)
{
    throw new NotSupportedException("Direct calls are not supported.");
}

and my SQL selection is something like:

select * 
from hotel
where city_id = 1
order by dbo.getHotelMinPrice_cap(hotel.id,1,'2001-01-01', '2021-01-01')

How can I write the last SQL query with dynamic sorting in linq?

Order by using a function is a bad practice. You should optimize your query first. Provide your function `getHotelMinPrice_cap` so that optimize way can be provided. — Manprit Singh Sahota, Aug 24 '17 at 07:20
select min(room_price.price) from room_price, room_definition where room_price.roomDef_id = room_definition.ID and room_definition.Hotel_id = Hotel_id and room_definition.capacity=capacity and ( room_price.start_date between enter_date and exit_date or room_price.stop_date between enter_date and exit_date ) — saeedeh, Aug 24 '17 at 07:27
the function returns min(room_price.price) which i mentioned in previous comment. room_price is joined to room_definition and room definition is joined to hotel — saeedeh, Aug 24 '17 at 07:33

Harald Coppoolse · Answer 1 · 2017-08-29T13:20:55.237

Your solution introduces several problems:

You OrderBy a property by name, hoping that the objects you order have this property. What if your objects don't have this property?
SQL does not understand functions like GetProperty(), so this ordering has to be done in local memory (AsEnumerable) instead of the much faster SQL server (AsQueryable).
You use a stored procedure to order by.

The first two problems can be solved easily by changing the parameter propertyName by a Func<TSource, TKey> keySelector:

public static IEnumerable<T, TKey> OrderByDynamic<T>(this IEnumerable<T> source, 
    Func<T, TKey> keySelector, System.ComponentModel.ListSortDirection sortDirection)
{
    if (sortDirection == ListSortDirection.Ascending)
         return source.OrderBy(keySelector);
    else
         return source.OrderByDescending(keySelector);
}

Usage would be like:

var result = Students.OrderByDynamis(student => student.Name, ListSortDirection.Ascending);

The advantage of this method is that your compiler will complain if you try to order by a non-existing property. Besides this OrderBy can be performed AsQueryable; it can be performed by your database instead of in local memory.

It is really a bad idea to use a string to select the property you want.

If you make a typing error you'll only detect this at run-time. Besides: if you know what to type as string for your propertyName during development of your code, you also know the type of objects you will be sorting, so you could write a keySelector instead.

Your second problem is calling the stored procedure as sort order. This is fairly easy to solve if you first call the stored procedure and then order by the returned value:

var result = Hotels.Where(hotel => hotel...)
    .Select(hotel => new
    {
        StoredProcedureValue = CallStoredprocedure(hotel,...),
        Hotel = hotel,
    })
    .AsEnumerable()  // bring the result to local memory
    .Orderby(item => item.StoredProcedureValue)
    .Select(item => item.Hotel);

Only the hotels that will be in your end-result are transferred to local memory, together with the result of the StoredProcedures. They have only been called for the hotels you will use in your end result.

However, the sorting is done in local memory. If you also want this sorting to be done on database side you'll have to create a new stored procedure that will call the other stored procedure before performing the sort.

thanks alot for your answer. I dont know what are 'StoredProcedureValue ' and 'CallStoredprocedure' you wrote! what are them?? I want to use a function named 'getHotelMinPrice_cap' — saeedeh, Aug 28 '17 at 08:17
It seems you are not familiar with anonymous types. In the `Where` statement, I take the collection of `Hotels` and from every item in this collection, which I conveniently identify with identifier `hotel` I create a new object of anonymous class, with two properties: `StoredProcedureValue` and `Hotel`. I don't know how you get the value of the stored procedure, so I just called a function that would do that the way you normally do. The Orderby takes as input this anonymous type http://geekswithblogs.net/BlackRabbitCoder/archive/2012/06/21/c.net-little-wonders-the-joy-of-anonymous-types.aspx — Harald Coppoolse, Aug 29 '17 at 06:42

score 2 · Accepted Answer · answered Aug 29 '17 at 17:20

Thanks to Harald Coppoolse for the answer I finally did it like this:

_objDB.hotels .Select(h=> new { id= h.id, name=h.name, star=h.star,
                    minPrice1 = hotel.getHotelMinPrice_cap(h.id, 1, model.date_check_in, model.date_check_out)})
.Where(s => (s.city_id = 1))
.OrderByDynamic(sortExpr, Ascending).ToList();

In this case, i can choose sortExpr = minPrice1; and it will be sort by the sql function.

also I changed the OrderByDynamic function as bellow:

public static IQueryable<T> OrderByDynamic<T>(this IQueryable<T> q, string SortField, bool Ascending)
        {
            var param = Expression.Parameter(typeof(T), "p");
            var prop = Expression.Property(param, SortField);
            var exp = Expression.Lambda(prop, param);
            string method = Ascending ? "OrderBy" : "OrderByDescending";
            Type[] types = new Type[] { q.ElementType, exp.Body.Type };
            var mce = Expression.Call(typeof(Queryable), method, types, q.Expression, exp);
            return q.Provider.CreateQuery<T>(mce);
        }

which i found on page: https://stackoverflow.com/a/7265354/8509940

Entity Framework linq orderby function

2 Answers2