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(
//some search criteria here,
//default sort order is start date asc
if (string.IsNullOrEmpty(orderBy)) orderBy = "StartDate";
//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
//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
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;
//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));
dataQry = compQry.OrderByDescending(Expression.Lambda<Func<YouDataObject, DateTime>>(expr, tableParam));
if (sortDirection.ToUpper() == "ASC")
dataQry = compQry.OrderBy(Expression.Lambda<Func<YouDataObject, object>>(expr, tableParam));
dataQry = compQry.OrderByDescending(Expression.Lambda<Func<YouDataObject, object>>(expr, tableParam));
//return the resulting query
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.