Tech – Noesis

The more you know, you know how little you know

«
»

Linq to Sql: Dynamic Sorting without using Complete Dynamic Linq Libraries

Posted by Pradeep Mishra on March 3, 2008

This problem may occur while implementing sorting in GridView. If a storedprocedure is being used either dynamic sql can be created or multiple of case statements can be used. However what if you are just using linq queries. Here are the options

  1. Using Dynamic Linq
  2. Some work arround so that linq query can be generated at runtime.

Essentially 2nd approach is the same as that used in 1st one. But if you just want to implement sorting and do not want to digg into Dynamic Linq libraries you can follow the article…

Let’s assume following method expects sortExpression parameter directly passed by UI layer GridView.

public DataTable GetSomeData(par1, par2…., string sortExpression)
{
var query = (//Linq query goes here )
// We want something like this which is not possible as of now
var query = (some query) (OrderBy SortExpression)
}
Here is the extension method you would like to follow…

public DataTable GetSomeData(par1, par2…., string sortExpression)
{
var query = (//Linq query goes here )
// We want something like this which is not possible as of now
var query = (some query) (OrderBy SortExpression)
}
public static Util
{
//Thanks to Ernesto for pointing out a small correction in method signature.
public static IQueryable OrderBy(this IQueryable 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 [ColoumnName 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
}
var property = type.GetProperty(orderByProperty);
var parameter = Expression.Parameter(type, “p”);
var propertyAccess = Expression.MakeMemberAccess(parameter, property);
var orderByExp = Expression.Lambda(propertyAccess, parameter);
MethodCallExpression resultExp = Expression.Call(typeof(Queryable), methodName,
new Type[] { type, property.PropertyType },
source.Expression, Expression.Quote(orderByExp));
return source.Provider.CreateQuery(resultExp);
}
}
Usage will be as of follows…

public DataTable GetSomeData(par1, par2…., string sortExpression)
{
var query = (//Linq query goes here )
// We want something like this which is not possible as of now
var query = (some query)
return query.OrderBy(SortExpression).ToDataTable(rec => new object[] { query}));
}
Again OrderBy is an extension method. Hope this helps!

This entry was posted on March 3, 2008 at 7:57 am and is filed under technical. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

18 Responses to “Linq to Sql: Dynamic Sorting without using Complete Dynamic Linq Libraries”

  1. Ernesto said

    March 13, 2008 at 3:05 pm

    Hi, I’m having a problem with the signature of your extension method, well, actually the problem is that I don’t fully understand it. Coould you explain a little further?


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

    especially the TEntity, where is that defined, I get a sintax error there.
    Thanks.

    Reply

  2. Ernesto said

    March 13, 2008 at 3:08 pm


    Hi, I’m having a problem with the signature of your extension method, well, actually the problem is that I don’t fully understand it. Coould you explain a little further?

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

    especially the TEntity, where is that defined, I get a sintax error there.
    Thanks.

    I get this in the where TEntity:class(I do know this is a constrain for a generic declaration):
    “Constraints are not allowed on non-generic declarations”

    Reply

  3. Danijel said

    March 17, 2008 at 2:17 pm

    I didn’t succeeded to use OrderBy extension method in mine code, because there were no “using area” along with class declaration in both code examples, or something else.
    If you could provide it, it will help more.
    Thanks

    Reply

  4. Pradeep Mishra said

    March 18, 2008 at 12:55 am

    Ernesto:
    You need not define the TEntity Class. It’s a source class. However you are right about syntax error. Here is the correct one

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

    Suppose you are applying the order by in Anonymous type

    var query = (from b in DataContext.Books select new {b.BookId, b.BookName, b.Author, b.PublishedDate});
    List bookList = query.OrderBy(sortExpression).ToList();

    The query returns anonymous type containing values of type So this defines TEntity Here. Let me know if it helps you. If you still have doubts let me know.
    Thanks a lot for pointing out the error in code. I will update the pose with the correct code.

    Danijel:
    There was small correction in code pointed out by Ernesto. You can write this static method in an Utility class in the class library having your Entities (dbml file). Let me know if you still face any problem.

    Reply

  5. pdxJaxon said

    July 9, 2008 at 11:53 pm

    I’m still having trouble with this….What is the “TEntity” in all these examples?

    I’ve been reading tons of online blogs, articles, etc and they all seem to reference this mythical thing but I can find no concrete explanation of what it is.

    I get errors in code when I duplicate the code as .NET cannot resolve TEntity.

    Reply

  6. Leather said

    September 25, 2008 at 2:35 pm

    Great post – so thanks! I looked at various other attempts on the web and they all seemed to fail on certain field types etc. Anyway I tweaked this slightly to suit my own needs and came out with this:


    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Linq.Expressions;
    using System.Data.Linq;
    using System.Text;

    namespace LinqSorter
    {
    public static class GenericSorter
    {
    public enum SortDirection
    {
    Ascending,
    Descending
    }

    public static IQueryable OrderBy(this IQueryable source, string fieldName) where TEntity : class
    {
    return OrderBy(source, fieldName, SortDirection.Ascending);
    }

    public static IQueryable OrderBy(this IQueryable source, string fieldName, SortDirection sortDirection) where TEntity : class
    {
    const string ORDER_BY_METHOD_NAME = "OrderBy";
    const string ORDER_BY_DESCENDING_METHOD_NAME = "OrderByDescending";
    const string PARAMETER_NAME = "Entity";

    // Get the type of the entity being sorted.
    var type = typeof(TEntity);

    // Create a parameter to pass into the Lambda expression (Entity => Entity.OrderByField).
    var parameter = Expression.Parameter(type, PARAMETER_NAME);

    // Get a reference to the type of the property being sorted.
    var property = type.GetProperty(fieldName);

    // Get a reference to the properties access member ( Entity.OrderByField ).
    var propertyAccess = Expression.MakeMemberAccess(parameter, property);

    // Create the order by expression.
    var orderByExp = Expression.Lambda(propertyAccess, parameter);

    string methodName;

    // Determine the method to actually call on the IQueryable interface.
    if(sortDirection == SortDirection.Ascending)
    methodName = ORDER_BY_METHOD_NAME;
    else
    methodName = ORDER_BY_DESCENDING_METHOD_NAME;

    // Get a reference to the method call.
    MethodCallExpression resultExp = Expression.Call(typeof(Queryable), methodName,
    new Type[] { type, property.PropertyType },
    source.Expression, Expression.Quote(orderByExp));

    // Now apply the sort.
    return source.Provider.CreateQuery(resultExp);
    }
    }

    This can then be called on an IQueryable object as follows (I am using a basic repository here):

    Repository r = new Repository();
    var sortedOrderDetails = r.GetEntities().OrderBy("DateShipped", GenericSorter.SortDirection.Descending);

    Reply

  7. Leather said

    September 25, 2008 at 2:37 pm

    Sorry – the post seems to have removed all of my generic references from the code above…

    Reply

  8. Haitham A. El Refaie said

    February 12, 2009 at 1:47 am

    thanks a lot for this code it really helped me a lot,
    but I suggest the following modification to improve it.
    Why we need to modify:
    in case of linking to datagrid at first built of the page the sort expression is an empty string so the property at line 27 of the code will be null which will cause error on line 29, the solution will be on of the following:
    1- validate the sortexpression before the calling of the sort function and give it initial value if it’s empty.
    2- to update or extension method to retun the same query if the sort expression is empty which is better, and it’ll be like this:

    public static IQueryable OrderBy(this IQueryable source, string sortExpression) where TEntity : class
    {
    if (!string.IsNullOrEmpty(sortExpression))
    {
    //our function body goes here
    }
    else
    {
    return source;
    }
    }

    Reply

  9. Stuart Thompson said

    February 23, 2009 at 6:50 pm

    This solution would evaluate your list and is more specific to sorting any collection than just with LINQ to SQL, but might also be a valuable addition to your toolbox in this space. http://blogs.sftsrc.com/stuart/archive/2009/02/19/130.aspx

    Reply

  10. DoYouKnow.IN said

    August 21, 2009 at 7:51 am

    Hi

    I want to do sorting of record from higher number of repeating in particular list…

    any one suggest how to do that?

    Reply

  11. Surendran said

    January 13, 2010 at 9:40 am

    Thanks for the great post! But I couldn’t get it the original code working. Without the generic declaration of it was comlaining about “Constraints are not allowed on non-generic declarations”. Do you know how to get rid of this error? Please see the version below that I got working eventually:

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using System.Linq.Expressions;

    namespace DataObjects.Linq.Implementation
    {
    public enum SortDirection
    {
    Ascending,
    Descending
    }

    public static class GenericSorter
    {
    public static IQueryable OrderBy(this IQueryable source, string fieldName) where TEntity : class
    {
    return OrderBy(source, fieldName, SortDirection.Ascending);
    }

    public static IQueryable OrderBy(this IQueryable source, string fieldName, SortDirection sortDirection) where TEntity : class
    {
    const string ORDER_BY_METHOD_NAME = “OrderBy”;
    const string ORDER_BY_DESCENDING_METHOD_NAME = “OrderByDescending”;
    const string PARAMETER_NAME = “Entity”;

    // Get the type of the entity being sorted.
    var type = typeof(TEntity);

    // Create a parameter to pass into the Lambda expression (Entity => Entity.OrderByField).
    var parameter = Expression.Parameter(type, PARAMETER_NAME);

    // Get a reference to the type of the property being sorted.
    var property = type.GetProperty(fieldName);

    // Get a reference to the properties access member ( Entity.OrderByField ).
    var propertyAccess = Expression.MakeMemberAccess(parameter, property);

    // Create the order by expression.
    var orderByExp = Expression.Lambda(propertyAccess, parameter);

    string methodName;

    // Determine the method to actually call on the IQueryable interface.
    if (sortDirection == SortDirection.Ascending)
    methodName = ORDER_BY_METHOD_NAME;
    else
    methodName = ORDER_BY_DESCENDING_METHOD_NAME;

    // Get a reference to the method call.
    MethodCallExpression resultExp = Expression.Call(typeof(Queryable), methodName,
    new Type[] { type, property.PropertyType },
    source.Expression, Expression.Quote(orderByExp));

    // Now apply the sort.
    return source.Provider.CreateQuery(resultExp);
    }
    }
    }

    Reply

  12. Surendran said

    January 13, 2010 at 9:44 am

    Sorry, the actual code that did compile without any error was as follows. I would appreciate if someone could point out how to get the original code working.

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using System.Linq.Expressions;

    namespace DataObjects.Linq.Implementation
    {
    public enum SortDirection
    {
    Ascending,
    Descending
    }

    public static class GenericSorter
    {
    public static IQueryable OrderBy(this IQueryable source, string fieldName) where TEntity : class
    {
    return OrderBy(source, fieldName, SortDirection.Ascending);
    }

    public static IQueryable OrderBy(this IQueryable source, string fieldName, SortDirection sortDirection) where TEntity : class
    {
    const string ORDER_BY_METHOD_NAME = “OrderBy”;
    const string ORDER_BY_DESCENDING_METHOD_NAME = “OrderByDescending”;
    const string PARAMETER_NAME = “Entity”;

    // Get the type of the entity being sorted.
    var type = typeof(TEntity);

    // Create a parameter to pass into the Lambda expression (Entity => Entity.OrderByField).
    var parameter = Expression.Parameter(type, PARAMETER_NAME);

    // Get a reference to the type of the property being sorted.
    var property = type.GetProperty(fieldName);

    // Get a reference to the properties access member ( Entity.OrderByField ).
    var propertyAccess = Expression.MakeMemberAccess(parameter, property);

    // Create the order by expression.
    var orderByExp = Expression.Lambda(propertyAccess, parameter);

    string methodName;

    // Determine the method to actually call on the IQueryable interface.
    if (sortDirection == SortDirection.Ascending)
    methodName = ORDER_BY_METHOD_NAME;
    else
    methodName = ORDER_BY_DESCENDING_METHOD_NAME;

    // Get a reference to the method call.
    MethodCallExpression resultExp = Expression.Call(typeof(Queryable), methodName,
    new Type[] { type, property.PropertyType },
    source.Expression, Expression.Quote(orderByExp));

    // Now apply the sort.
    return source.Provider.CreateQuery(resultExp);
    }
    }
    }

    Reply

  13. Surendran said

    January 13, 2010 at 9:47 am

    Amazing.. when I copy the code it doesn’t show the in the method declaration!!!!!

    Reply

  14. Surendran said

    January 13, 2010 at 9:50 am

    Now I know why, though.

    When I copied earlier, it wasn't copying the  type declaration in the method signatures above.
    Reply

  15. Surendran said

    January 13, 2010 at 9:52 am

    I give up! It was the <TEntity> I was mentioning about..

    Reply

  16. Jonathan Mullaney said

    May 6, 2010 at 6:37 am

    Got it working!

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using System.Linq.Expressions;

    using Type = System.Type;

    namespace TVWinners.Data.LinqToSQL.Domain
    {
    public enum SortDirection
    {
    Ascending,
    Descending
    }

    public static class GenericSorter
    {

    public static IQueryable OrderBy(this IQueryable source, string fieldName) where TEntity : class
    {
    return OrderBy(source, fieldName, SortDirection.Ascending);
    }

    public static IQueryable OrderBy(this IQueryable source, string fieldName, SortDirection sortDirection) where TEntity : class
    {
    const string ORDER_BY_METHOD_NAME = “OrderBy”;
    const string ORDER_BY_DESCENDING_METHOD_NAME = “OrderByDescending”;
    const string PARAMETER_NAME = “Entity”;

    // Get the type of the entity being sorted.
    var type = typeof(TEntity);

    // Create a parameter to pass into the Lambda expression (Entity => Entity.OrderByField).
    var parameter = Expression.Parameter(type, PARAMETER_NAME);

    // Get a reference to the type of the property being sorted.
    var property = type.GetProperty(fieldName);

    // Get a reference to the properties access member ( Entity.OrderByField ).
    var propertyAccess = Expression.MakeMemberAccess(parameter, property);

    // Create the order by expression.
    var orderByExp = Expression.Lambda(propertyAccess, parameter);

    string methodName;

    // Determine the method to actually call on the IQueryable interface.
    if (sortDirection == SortDirection.Ascending)
    methodName = ORDER_BY_METHOD_NAME;
    else
    methodName = ORDER_BY_DESCENDING_METHOD_NAME;

    // Get a reference to the method call.
    MethodCallExpression resultExp = Expression.Call(typeof(Queryable), methodName,
    new System.Type[] { type, property.PropertyType },
    source.Expression, Expression.Quote(orderByExp));

    // Now apply the sort.
    return source.Provider.CreateQuery(resultExp);
    }
    }
    }

    Reply

  17. toshiba top said

    November 29, 2011 at 5:55 pm

    toshiba top…

    […]Linq to Sql: Dynamic Sorting without using Complete Dynamic Linq Libraries « Tech – Noesis[…]…

    Reply

  18. afla.md said

    July 10, 2017 at 2:22 am

    afla.md

    Linq to Sql: Dynamic Sorting without using Complete Dynamic Linq Libraries « Tech – Noesis

    Reply

Leave a comment

«
»