Creating a dynamic sort method for linq to entities

Question

Possible Duplicate:
Dynamic LINQ OrderBy

I'm using the Entity Framework and would like to create my own dynamic sorting method that will let you enter a string to do your sorting such as query.DynamicSort("Name, Age DESC").

I know MS has put out some dynamic linq code but it's more than I need and I'm trying to understand how to write a simplified version of it myself just for sorting.

I've had some luck in getting it to work for a single field...but when I try to sort by multiple fields I get the following error:

The parameter 'x' was not bound in the specified LINQ to Entities query expression.

Here's my code. Can anyone see where I've gone wrong and perhaps suggest some revisions? I'm weak on constructing expressions dynamically.

public static class DynamicQuery
{
    private class SortField
    {
        public Expression Expression;
        public bool       Descending = false;
        public Type       Type;
    }

    public static IOrderedQueryable<T> DynamicSort<T>(this IQueryable<T> source, string sortOrder)
    {
        var fields     = sortOrder.Split(',');
        var sortFields = new List<SortField>();

        ParameterExpression arg = null;

        for (int i = 0; i < fields.Length; i++)
        {
            var sf = new SortField();
            sortFields.Add(sf);

            //Handle Descending Sort Fields
            if (fields[i].EndsWith(" DESC", StringComparison.OrdinalIgnoreCase))
            {
                sf.Descending = true;
                fields[i] = fields[i].Substring(0, (fields[i].Length - 5));  //Remove " DESC"
            }

            fields[i] = fields[i].Trim();

            //Handle fields that have nested properties
            var props = fields[i].Split('.');
            sf.Type = typeof(T);
            sf.Expression = arg = Expression.Parameter(sf.Type, "x");  //Create an x parameter of type T

            foreach (string prop in props)
            {
                PropertyInfo pi = sf.Type.GetProperty(prop);
                sf.Expression = Expression.Property(sf.Expression, pi);
                sf.Type = pi.PropertyType;
            }
        }

        //Now that we have the SortFields we can do the sorting
        Expression queryExpr = source.Expression;
        string methodAsc = "OrderBy";
        string methodDesc = "OrderByDescending";
        foreach (var sf in sortFields)
        {
            LambdaExpression lambda = Expression.Lambda(sf.Expression, arg);

            queryExpr = Expression.Call(
                typeof(Queryable), sf.Descending ? methodDesc : methodAsc,
                new Type[] { source.ElementType, sf.Type },
                queryExpr, Expression.Quote(lambda));
            methodAsc = "ThenBy";
            methodDesc = "ThenByDescending";
        }
        return (IOrderedQueryable<T>)source.Provider.CreateQuery(queryExpr);
    }
}

Thanks very much.

It is similar, and I've taken some ideas from that post, but what I'm trying to do is a bit different. I've updated my question to more fully explain. — user169867, May 21 '11 at 01:41
@user, I do not understand what you mean. The link I provided does precisely what you are asking for. To sort by two fields, first you use `OrderBy`, and then you use `ThenBy`. If you are having trouble, I recommend you look at the code in that question until you understand it -- I use it myself and can attest that it works great. — Kirk Woll, May 21 '11 at 01:58
The problem is it doesn't allow you to pass in a valid SQL sorting string. You'd need to know ahead of time each field you wished to sort by and make the multiple calls. — user169867, May 21 '11 at 02:03
@user, what do you mean by "a valid SQL sorting string"? Can you provide an example? — Kirk Woll, May 21 '11 at 02:05
I did. You are familiar with how sorting works in SQL right? Each field name comma seperated and followed by an optional ASC/DESC. At any rate I listed my specific error message that I'm interested in finding the cause of. — user169867, May 21 '11 at 02:09
@user, are you **sure** you actually read the duplicate in question? It does **exactly that**: `queryable.OrderBy("Column1").ThenByDescending("Column2")` That snippet will order by "Column1" ascending and "Column2" descending. **Please** give an example where this is insufficient. — Kirk Woll, May 21 '11 at 02:11
@user, the *reason* your code is failing is because you are reusing the `arg` variable. When you have this line: `LambdaExpression lambda = Expression.Lambda(sf.Expression, arg);`, **which** `ParameterExpression` is being bound? Clearly only the last one, since `arg` gets overidden upon each iteration of the initial sort field loop. My suggestion: 1) Add a new field to class `SortField`: `public ParameterExpression Arg;`. 2) Remove the `arg` variable altogether and instead assign this field. 3) Refer to this field when creating the lambda: `Expression.Lambda(sf.Expression, sf.Arg);` — Kirk Woll, May 21 '11 at 02:25
(I made those changes to your code and tested it: `db.Users.DynamicSort("UserId, UserName").ToArray();`. No error anymore. And I suspect you were trying to re-use instances of `ParameterExpression` because they were defined the same way. This is a no-no when using expression trees.) — Kirk Woll, May 21 '11 at 02:26

Creating a dynamic sort method for linq to entities

0 Answers0