CraigWardman.com

[ Dynamic OrderBy using LINQ to SQL ]

/Blog/Post

Dynamic OrderBy using LINQ to SQL

Following on from my last post on Virtual Paging with Silverlight/WCF Services, the next topic to go hand in hand with this is dynamically ordering your data when using LINQ to SQL as a backend.

Ordinarily I use my own data architecture, so haven’t run into this problem of using LINQ to SQL and trying to pass your order by clause from the UI before; but recently I was working on a project which does use LINQ to SQL (actually its Entity Framework but I guess these things fit together) so this came up, after having accomplished proper server side paging.

When you are paging data you want to page it based on a certain order. The order of the data is seldom hard coded, you want the user to define at runtime how the data should be ordered, e.g. by selecting the column in a grid view. In LINQ to SQL this isn’t as simple as it should be, unless you download the ‘Dynamic LINQ Library’ that Microsoft have published in some of their samples.

Not one for including code libraries unnecessarily I decided to tackle it with a little extra Googling and understanding.

What I came across was basically the use of LINQ Expressions in conjunction with reflection to dynamically build the parameters to the LINQ order by clause at runtime.

I only needed this to be very simple, i.e. one column at a time. I therefore defined a simple string which would represent a ‘sort expression’ of one column and one direction.

Here is the code to my solution:

private IQueryable<YouDataObject> SearchYourDataQuery(
EFDBConnection conn, 
//some search criteria here,
string orderBy)
{
 
//default sort order is start date asc
if (string.IsNullOrEmpty(orderBy)) orderBy = "StartDate";
orderBy=orderBy.Trim();
 
//parse the order by statement 
string sortExprRegex = @"^(?<orderBy>.+?)\s?(?<direction>ASC|DESC)?$";
string sortDirection = "ASC";
 
if (Regex.IsMatch(orderBy, sortExprRegex, RegexOptions.IgnoreCase))
{
    Match orderByParts = Regex.Match(orderBy, sortExprRegex, RegexOptions.IgnoreCase);
    orderBy = orderByParts.Groups["orderBy"].Value;
    if(!string.IsNullOrEmpty(orderByParts.Groups["direction"].Value)) sortDirection = orderByParts.Groups["direction"].Value;
}          
 
//generate the query to get the filtered records
IQueryable<YouDataObject> dataQry = (from a in conn.YourDatas
                                            //put your joins, where clauses etc
                                            select a);
 
    //build an order by statement based on the column name passed to us
ParameterExpression tableParam = Expression.Parameter(typeof(YouDataObject), "d");
 
//awlays starting property access from the top table
Expression expr = tableParam;
 
//if it has dots then its a child property, i.e of another type
if (orderBy.Contains("."))
{
    string[] props = orderBy.Split('.');
 
    Type type = typeof(YouDataObject);
    foreach (string prop in props)
    {
        // use reflection (not ComponentModel) to mirror LINQ
        PropertyInfo pi = type.GetProperty(prop);
        expr = Expression.Property(expr, pi);
        type = pi.PropertyType;
    }
 
}
else
{
    //no dots means a direct property accessor of the top level table
    expr = Expression.Property(tableParam, orderBy);
}
 
//create a test of data type, but default is to use 'object'
if (expr.Type == typeof(DateTime))
{
    if(sortDirection.ToUpper() == "ASC")
        dataQry = compQry.OrderBy(Expression.Lambda<Func<YouDataObject, DateTime>>(expr, tableParam));
    else
        dataQry = compQry.OrderByDescending(Expression.Lambda<Func<YouDataObject, DateTime>>(expr, tableParam));
}
else
{
    if (sortDirection.ToUpper() == "ASC")
        dataQry = compQry.OrderBy(Expression.Lambda<Func<YouDataObject, object>>(expr, tableParam));
    else
        dataQry = compQry.OrderByDescending(Expression.Lambda<Func<YouDataObject, object>>(expr, tableParam));
}
 
//return the resulting query
return dataQry;
 
}

There are probably ways to neaten up the code and make it more reusable, but I think as a starting point that is hopefully helpful.

Share This!

1 Trackback to Dynamic OrderBy using LINQ to SQL

  1. By DotNetKicks.com on August 24, 2011 at 10:19 am

Leave a Reply

CraigWardman.com
Car Leasing | Lease Cars