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

23 09 2008

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);

}

About these ads

Actions

Information

7 responses

2 04 2009
mike

great job

16 04 2009
km

Thanks for this, very helpful.

I used SortDirection, which comes from the GridView event. I changed your code to have the following signature:

public static IQueryable OrderBy(
this IQueryable source,
string sortExpression,
SortDirection sortDirection) where TEntity : class

13 01 2010
SRIDHAR

Great!! this made my life simpler. Thanks.

19 01 2010
Jason

Thanks helped a lot.

9 02 2012
Timbilt

You are amazing! This is no where to be found except here… Thank you so much. You solved my problem!!!

2 04 2013
Barbra

Oh my goodness! Incredible article dude! Many thanks, However I am having troubles with your RSS.

I don’t understand the reason why I am unable to subscribe to it. Is there anybody else getting the same RSS problems? Anybody who knows the solution can you kindly respond? Thanks!!

2 04 2013
Vincent Germain

Indeed, this little workaround seems to help many people :)
Regarding your subscription, you have to click the follow button in the top bar. In your wordpress reader, you will find a subscription action on the right menu and click edit. select the blogs that you want to follow.
Is that what you wanted to do ?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s




Follow

Get every new post delivered to your Inbox.

%d bloggers like this: