locked
LINQ to SQL: Generic OrderBy RRS feed

  • Question

  • User-1839033458 posted

    Hi people,

     

    i'm new in LINQ to SQL and MVC, and i'm trying to make my data paginable and sorteable, the paginate issue was succesful solved with .Skip and .Take, but now the problem is with the OrderBy method. I want to make it generic, now to sort it i have to write "u => u.UserID" as orderExpression paraemter.

     

    UsersService.GetAllPaginated(page ?? 0, PageSize, u => u.UserID, sortAscending.Value, out TotalRows);


    this is the repository method:

    public IEnumerable<TEntity> GetAllPaginated<S>(int pageIndex, int pageSize, Expression<Func<TEntity, S>> orderByExpression, bool ascending, out int totalRows)
            {
                ObjectSet<TEntity> ReturnSet;            
                ReturnSet = ObjectContext.CreateObjectSet<TEntity>();
                
                totalRows = ReturnSet.Count();
    
                return (ascending)
                                    ?
                                        ReturnSet.OrderBy(orderByExpression)
                                         .Skip(pageIndex * pageSize)
                                         .Take(pageSize)
                                         .ToList()
                                    :
                                        ReturnSet.OrderByDescending(orderByExpression)
                                         .Skip(pageIndex * pageSize)
                                         .Take(pageSize)
                                         .ToList();
            }


     

     And now i tried to call the GetAllPaginated method in this way:

    var param = Expression.Parameter(typeof(Entities.User), "user");
                
    var SortExpr = Expression.Lambda<Func<Entities.User, object>>(Expression.Convert(Expression.Property(param, sortColumn), typeof(object)), param);
    
    UsersData = UsersService.GetAllPaginated(page ?? 0, PageSize, SortExpr, sortAscending.Value, out TotalRows);


    But i got this error after the ReturnSet.OrderBy method is executed:

    Unable to cast the type 'System.Int32' to type 'System.Object'. LINQ to Entities only supports casting Entity Data Model primitive types.

     

     The problem is that in the "SortExpr" assignation i'm passing object as property type, instead the real property type.

    There is a better way to Order it by a generic way, or how can i assign SortExpr by reflection?

     

    Thanks in advance

    Monday, July 19, 2010 11:22 AM

Answers

  • User2050624116 posted

    Thanks for your reply, but it doesn't work for me.

    i'm receiving from the HttpPost the  column name to be sorted. It is a string, and i need to build the Lambda sort expression dynamically to pass Expression<Func<TEntity, S>> as parameter.

     

    Some idea?

     

     

    public static Expression<Func<T, TKey>> OrderExpression<T, TKey>(string memberName)
    {
        ParameterExpression[] typeParams = new ParameterExpression[] { Expression.Parameter(typeof(T), "") };
    
        Expression<Func<T, TKey>> orderByExpression
            = (Expression<Func<T, TKey>>)Expression.Lambda(
                Expression.Property(typeParams[0], memberName),
                typeParams
              );
    
        return orderByExpression;
    }


     

    Alternatively, you may want to take a look at the dynamic linq sample that comes with Visual Studio code samples (..\Program Files (x86)\Microsoft Visual Studio 10.0\Samples\1033\CSharpSamples.zip / LinqSamples\DynamicQuery\DynamicQuery ). It has everything you need to build expressions and queries from strings.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, July 20, 2010 2:30 AM
  • User2050624116 posted

    Thanks KristoferA, but  the exposed code has the same issue, i don't know the TKey generic type, it must be dynamic. I will check the LinqSamples anyway and if i find something i will post it.

     

    Ok, I see what you're saying. Here's an orderby extension method that takes only a member name...

     

    public static IOrderedQueryable<T> OrderBy<T>(this IQueryable<T> query, string memberName)
    {
        ParameterExpression[] typeParams = new ParameterExpression[] { Expression.Parameter(typeof(T), "") };
    
        System.Reflection.PropertyInfo pi = typeof(T).GetProperty(memberName);
    
        return (IOrderedQueryable<T>)query.Provider.CreateQuery(
            Expression.Call(
                typeof(Queryable),
                "OrderBy",
                new Type[] { typeof(T), pi.PropertyType },
                query.Expression,
                Expression.Lambda(Expression.Property(typeParams[0], pi), typeParams))
        );
    }
    


     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, July 20, 2010 11:29 PM

All replies

  • User626880745 posted

    I don't think I'm grasping your issue completely, but you can use a generic sort extension. 

    take a look at the example I've posted here: http://forums.asp.net/p/1558872/3844536.aspx#3843168


    Monday, July 19, 2010 12:56 PM
  • User-1839033458 posted

    Thanks for your reply, but it doesn't work for me.

    i'm receiving from the HttpPost the  column name to be sorted. It is a string, and i need to build the Lambda sort expression dynamically to pass Expression<Func<TEntity, S>> as parameter.

     

    Some idea?

    Monday, July 19, 2010 1:18 PM
  • User2050624116 posted

    Thanks for your reply, but it doesn't work for me.

    i'm receiving from the HttpPost the  column name to be sorted. It is a string, and i need to build the Lambda sort expression dynamically to pass Expression<Func<TEntity, S>> as parameter.

     

    Some idea?

     

     

    public static Expression<Func<T, TKey>> OrderExpression<T, TKey>(string memberName)
    {
        ParameterExpression[] typeParams = new ParameterExpression[] { Expression.Parameter(typeof(T), "") };
    
        Expression<Func<T, TKey>> orderByExpression
            = (Expression<Func<T, TKey>>)Expression.Lambda(
                Expression.Property(typeParams[0], memberName),
                typeParams
              );
    
        return orderByExpression;
    }


     

    Alternatively, you may want to take a look at the dynamic linq sample that comes with Visual Studio code samples (..\Program Files (x86)\Microsoft Visual Studio 10.0\Samples\1033\CSharpSamples.zip / LinqSamples\DynamicQuery\DynamicQuery ). It has everything you need to build expressions and queries from strings.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, July 20, 2010 2:30 AM
  • User-1839033458 posted

    Thanks KristoferA, but  the exposed code has the same issue, i don't know the TKey generic type, it must be dynamic. I will check the LinqSamples anyway and if i find something i will post it.

    Tuesday, July 20, 2010 8:51 AM
  • User2050624116 posted

    Thanks KristoferA, but  the exposed code has the same issue, i don't know the TKey generic type, it must be dynamic. I will check the LinqSamples anyway and if i find something i will post it.

     

    Ok, I see what you're saying. Here's an orderby extension method that takes only a member name...

     

    public static IOrderedQueryable<T> OrderBy<T>(this IQueryable<T> query, string memberName)
    {
        ParameterExpression[] typeParams = new ParameterExpression[] { Expression.Parameter(typeof(T), "") };
    
        System.Reflection.PropertyInfo pi = typeof(T).GetProperty(memberName);
    
        return (IOrderedQueryable<T>)query.Provider.CreateQuery(
            Expression.Call(
                typeof(Queryable),
                "OrderBy",
                new Type[] { typeof(T), pi.PropertyType },
                query.Expression,
                Expression.Lambda(Expression.Property(typeParams[0], pi), typeParams))
        );
    }
    


     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, July 20, 2010 11:29 PM