Linq To SQL : How to order with a string typed expression ?

After using Linq To SQL queries, I was quickly confronted to sort my data with a string expression provided, for example, by a gridview when a user require it. At this moment, I understood that the strongly typed system used with Linq cannot help me for this task… Understanding the fact that allowing a string expression representing an object property is contrary to Linq purpose, I began to search with our best friend “Google” a soluce to resolve quickly my problem. But the best that I can obtain was a “switch case solution” which, according to the string property name, build the linq query sort filter with the associated object property. Immediatly, I guess my switch case code with 8-10 columns… No…

So, I started to write an extended method “OrderBy” allowing to take a string sort expression. Here the result.

/// <summary>

/// Extends method which allow to sort by string field name.

/// Allow to use a relative object definition for sorting (ex:LinkedObject.FieldsName1)

/// </summary>

/// <typeparam name=”TEntity”>Current Object type for query</typeparam>

/// <param name=”source”>list of defined object</param>

/// <param name=”sortExpression”>string name of the field we want to sort by</param>

/// <returns>Query sorted by sortExpression</returns>

public static IQueryable<TEntity> OrderBy<TEntity>(

this IQueryable<TEntity> source, string sortExpression) where TEntity : class

{

var type = typeof(TEntity);

// Remember that for ascending order GridView just returns the column name and

// for descending it returns column name followed by DESC keyword

// Therefore we need to examine the sortExpression and separate out Column Name and

// order (ASC/DESC)

string[] expressionParts = sortExpression.Split(‘ ‘); // Assuming sortExpression is like [ColumnName DESC] or [ColumnName]

string orderByProperty = expressionParts[0];

string sortDirection = “ASC”;

string methodName = “OrderBy”;

//if sortDirection is descending

if (expressionParts.Length > 1 && expressionParts[1] == “DESC”)

{

sortDirection = “Descending”;

methodName += sortDirection; // Add sort direction at the end of Method name

}

MethodCallExpression resultExp = null;

if (!orderByProperty.Contains(“.”))

{

var property = type.GetProperty(orderByProperty);

var parameter = Expression.Parameter(type, “p”);

var propertyAccess = Expression.MakeMemberAccess(parameter, property);

var orderByExp = Expression.Lambda(propertyAccess, parameter);

resultExp = Expression.Call(typeof(Queryable), methodName,

new Type[] { type, property.PropertyType },

source.Expression, Expression.Quote(orderByExp));

}

else

{

Type relationType = type.GetProperty(orderByProperty.Split(‘.’)[0]).PropertyType;

PropertyInfo relationProperty = type.GetProperty(orderByProperty.Split(‘.’)[0]);

PropertyInfo relationProperty2 = relationType.GetProperty(orderByProperty.Split(‘.’)[1]);

var parameter = Expression.Parameter(type, “p”);

var propertyAccess = Expression.MakeMemberAccess(parameter, relationProperty);

var propertyAccess2 = Expression.MakeMemberAccess(propertyAccess, relationProperty2);

var orderByExp = Expression.Lambda(propertyAccess2, parameter);

resultExp = Expression.Call(typeof(Queryable), methodName,

new Type[] { type, relationProperty2.PropertyType },

source.Expression, Expression.Quote(orderByExp));

}

return source.Provider.CreateQuery<TEntity>(resultExp);

}

/// <summary>

/// Allow to add another sorting on a query with a string representation of the field to sort by.

/// </summary>

/// <typeparam name=”TEntity”>Current Object type for query</typeparam>

/// <param name=”source”>list of defined object</param>

/// <param name=”sortExpression”>string name of the field we want to sort by</param>

/// <returns>Query sorted by sortExpression</returns>

public static IOrderedQueryable<TEntity> ThenBy<TEntity>(

this IOrderedQueryable<TEntity> source, string sortExpression) where TEntity : class

{

var type = typeof(TEntity);

// Remember that for ascending order GridView just returns the column name and

// for descending it returns column name followed by DESC keyword

// Therefore we need to examine the sortExpression and separate out Column Name and

// order (ASC/DESC)

string[] expressionParts = sortExpression.Split(‘ ‘); // Assuming sortExpression is like [ColumnName DESC] or [ColumnName]

string orderByProperty = expressionParts[0];

string sortDirection = “ASC”;

string methodName = “ThenBy”;

//if sortDirection is descending

if (expressionParts.Length > 1 && expressionParts[1] == “DESC”)

{

sortDirection = “Descending”;

methodName += sortDirection; // Add sort direction at the end of Method name

}

MethodCallExpression resultExp = null;

if (!orderByProperty.Contains(“.”))

{

var property = type.GetProperty(orderByProperty);

var parameter = Expression.Parameter(type, “p”);

var propertyAccess = Expression.MakeMemberAccess(parameter, property);

var orderByExp = Expression.Lambda(propertyAccess, parameter);

resultExp = Expression.Call(typeof(Queryable), methodName,

new Type[] { type, property.PropertyType },

source.Expression, Expression.Quote(orderByExp));

}

else

{

Type relationType = type.GetProperty(orderByProperty.Split(‘.’)[0]).PropertyType;

PropertyInfo relationProperty = type.GetProperty(orderByProperty.Split(‘.’)[0]);

PropertyInfo relationProperty2 = relationType.GetProperty(orderByProperty.Split(‘.’)[1]);

var parameter = Expression.Parameter(type, “p”);

var propertyAccess = Expression.MakeMemberAccess(parameter, relationProperty);

var propertyAccess2 = Expression.MakeMemberAccess(propertyAccess, relationProperty2);

var orderByExp = Expression.Lambda(propertyAccess2, parameter);

resultExp = Expression.Call(typeof(Queryable), methodName,

new Type[] { type, relationProperty2.PropertyType },

source.Expression, Expression.Quote(orderByExp));

}

return (IOrderedQueryable<TEntity>)source.Provider.CreateQuery<TEntity>(resultExp);

}

Advertisement