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!

Advertisements

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

  1. Ernesto said

    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.

  2. Ernesto said


    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”

  3. Danijel said

    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

  4. 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.

  5. pdxJaxon said

    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.

  6. Leather said

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

  7. Leather said

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

  8. Haitham A. El Refaie said

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

  9. 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

  10. Hi

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

    any one suggest how to do that?

  11. Surendran said

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

  12. Surendran said

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

  13. Surendran said

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

  14. Surendran said

    Now I know why, though.

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

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

  16. 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);
    }
    }
    }

  17. toshiba top…

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

  18. afla.md said

    afla.md

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

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

 
%d bloggers like this: