Entity Framework linq orderby function

asp.net-mvc-5 c# entity-framework-6 function linq

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?

1
1
8/24/2017 7:14:19 AM

Accepted Answer

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

0
8/29/2017 5:20:58 PM

Popular Answer

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.



Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow