none
Order by linq RRS feed

  • Question

  • Hi

    How to set the order by in Linq as parameterised   eg

    Sring OrderBy =”Name”

    Var names = ( from n in db.name

                              Select n).OrderBy(OrderBy)

    Friday, January 4, 2013 2:02 PM

Answers

  • Hi Rushdy;

    In order to use the OrderBy method you need to use a strongly type object. OrderBy is defined as follows:

    public static IOrderedEnumerable<TSource> OrderBy<TSource, TKey>(
    	this IEnumerable<TSource> source,
    	Func<TSource, TKey> keySelector
    )

     
    The column that is sorted on is the keyDelector. So you can't use a string to select the sort order. In order to do what you need one way to do it would be to create the query dynamically. The following is sample code showing how this can be done.

    String OrderBy = "Name";
    int skip = 0;
    
    var orderNotInvoiced = (from o in context.Orders
                            where (o.MapId == _IVmapId) && !(from i in invoice select i.Order_no).Contains(o.Order_no)
                            select o);
                            
    .OrderBy(OrderBy).Skip(skip).Take(15).ToList<Order>();
    
    // OrderBy Name                                
    if( OrderBy == "Name" )
    {
        orderNotInvoiced = orderNotInvoiced.OrderBy( ob => ob.Name );
    }
    // OrderBy Company
    if( OrderBy == "Company" )
    {
        orderNotInvoiced = orderNotInvoiced.OrderBy( ob => ob.Company );
    }
    
    // Other if statements here as needed
    
    // In order to use the Skip method in the query it needs to be sorted
    // Because we are building the query in steps we need to make sure that
    // we have assigned a OrderBy method otherwise we do not add the Skip method
    if( orderNotInvoiced.Expression.ToString().Contains("OrderBy") )
    {
        orderNotInvoiced = orderNotInvoiced.Skip(skip).Take(15);
    }
    
    // Then cast to List of Order when you use it for example 
    dataGridView.DataSource = orderNotInvoiced.ToList<Order>();
      


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

    • Marked as answer by Alexander Sun Wednesday, January 16, 2013 9:31 AM
    Friday, January 4, 2013 7:55 PM
  • What you are looking for is found in the System.Linq.Expressions namespace.  While I see that you are using C# I am including a VB sample of code to show how I use the Expressions library to Sort an Arbitrary list using a property name (no reflection used).

    Imports System.Linq.Expressions
    
    Public Class GenericListSorter(Of T)
    	Public Function SortAscending(ByVal source As IEnumerable(Of T), ByVal sortBy As String) As IEnumerable(Of T)
    		Dim param = Expression.Parameter(GetType(T), "item")
    		Dim sortExpression = Expression.Lambda(Of Func(Of T, Object))(Expression.Convert(Expression.[Property](param, sortBy), GetType(Object)), param)
    
    		Dim IQue As IQueryable(Of T) = source.AsQueryable
    		Dim IRet As IQueryable(Of T) = (IQue.OrderBy(Of Object)(sortExpression))
    
    		Return IRet
    
    	End Function
    
    	Public Function SortDescending(ByVal source As IEnumerable(Of T), ByVal sortBy As String) As IEnumerable(Of T)
    		Dim param = Expression.Parameter(GetType(T), "item")
    		Dim sortExpression = Expression.Lambda(Of Func(Of T, Object))(Expression.Convert(Expression.[Property](param, sortBy), GetType(Object)), param)
    
    		Dim IQue As IQueryable(Of T) = source.AsQueryable
    		Dim IRet As IQueryable(Of T) = (IQue.OrderByDescending(Of Object)(sortExpression))
    
    		Return IRet
    
    	End Function
    
    End Class
    

    An untested C# version of the above is:

    using System.Linq.Expressions;
    
    public class GenericListSorter<T>
    {
    	public IEnumerable<T> SortAscending(IEnumerable<T> source, string sortBy)
    	{
    		object param = Expression.Parameter(typeof(T), "item");
    		object sortExpression = Expression.Lambda<Func<T, object>>(Expression.Convert(Expression.Property(param, sortBy), typeof(object)), param);
    
    		IQueryable<T> IQue = source.AsQueryable;
    		IQueryable<T> IRet = (IQue.OrderBy<object>(sortExpression));
    
    		return IRet;
    
    	}
    
    	public IEnumerable<T> SortDescending(IEnumerable<T> source, string sortBy)
    	{
    		object param = Expression.Parameter(typeof(T), "item");
    		object sortExpression = Expression.Lambda<Func<T, object>>(Expression.Convert(Expression.Property(param, sortBy), typeof(object)), param);
    
    		IQueryable<T> IQue = source.AsQueryable;
    		IQueryable<T> IRet = (IQue.OrderByDescending<object>(sortExpression));
    
    		return IRet;
    
    	}
    }
    

    Hope this helps (and BTW this library will allow you to do things like Query by Example using the library to build the Linq query),

    LS


    Lloyd Sheen

    • Marked as answer by Alexander Sun Wednesday, January 16, 2013 9:31 AM
    Friday, January 4, 2013 11:19 PM

All replies

  • Hi
    How to set the order by in Linq as parameterised   eg

    Sring OrderBy =”Name”

    List<Order> orderNotInvoiced = (from o in context.Orders
                                 where (o.MapId == _IVmapId) && !(from i in invoice select i.Order_no).Contains(o.Order_no)
                                 select o).OrderBy(OrderBy).Skip(skip).Take(15).ToList<Order>();

    • Merged by Alexander Sun Monday, January 7, 2013 1:59 AM Duplicate
    Friday, January 4, 2013 2:15 PM
  • Hi Rushdy;

    In order to use the OrderBy method you need to use a strongly type object. OrderBy is defined as follows:

    public static IOrderedEnumerable<TSource> OrderBy<TSource, TKey>(
    	this IEnumerable<TSource> source,
    	Func<TSource, TKey> keySelector
    )

     
    The column that is sorted on is the keyDelector. So you can't use a string to select the sort order. In order to do what you need one way to do it would be to create the query dynamically. The following is sample code showing how this can be done.

    String OrderBy = "Name";
    int skip = 0;
    
    var orderNotInvoiced = (from o in context.Orders
                            where (o.MapId == _IVmapId) && !(from i in invoice select i.Order_no).Contains(o.Order_no)
                            select o);
                            
    .OrderBy(OrderBy).Skip(skip).Take(15).ToList<Order>();
    
    // OrderBy Name                                
    if( OrderBy == "Name" )
    {
        orderNotInvoiced = orderNotInvoiced.OrderBy( ob => ob.Name );
    }
    // OrderBy Company
    if( OrderBy == "Company" )
    {
        orderNotInvoiced = orderNotInvoiced.OrderBy( ob => ob.Company );
    }
    
    // Other if statements here as needed
    
    // In order to use the Skip method in the query it needs to be sorted
    // Because we are building the query in steps we need to make sure that
    // we have assigned a OrderBy method otherwise we do not add the Skip method
    if( orderNotInvoiced.Expression.ToString().Contains("OrderBy") )
    {
        orderNotInvoiced = orderNotInvoiced.Skip(skip).Take(15);
    }
    
    // Then cast to List of Order when you use it for example 
    dataGridView.DataSource = orderNotInvoiced.ToList<Order>();
      


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

    • Marked as answer by Alexander Sun Wednesday, January 16, 2013 9:31 AM
    Friday, January 4, 2013 7:55 PM
  •   

    Please see your other question, order by in Linq as parameterised, for the solution.

      


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

    Friday, January 4, 2013 8:00 PM
  • What you are looking for is found in the System.Linq.Expressions namespace.  While I see that you are using C# I am including a VB sample of code to show how I use the Expressions library to Sort an Arbitrary list using a property name (no reflection used).

    Imports System.Linq.Expressions
    
    Public Class GenericListSorter(Of T)
    	Public Function SortAscending(ByVal source As IEnumerable(Of T), ByVal sortBy As String) As IEnumerable(Of T)
    		Dim param = Expression.Parameter(GetType(T), "item")
    		Dim sortExpression = Expression.Lambda(Of Func(Of T, Object))(Expression.Convert(Expression.[Property](param, sortBy), GetType(Object)), param)
    
    		Dim IQue As IQueryable(Of T) = source.AsQueryable
    		Dim IRet As IQueryable(Of T) = (IQue.OrderBy(Of Object)(sortExpression))
    
    		Return IRet
    
    	End Function
    
    	Public Function SortDescending(ByVal source As IEnumerable(Of T), ByVal sortBy As String) As IEnumerable(Of T)
    		Dim param = Expression.Parameter(GetType(T), "item")
    		Dim sortExpression = Expression.Lambda(Of Func(Of T, Object))(Expression.Convert(Expression.[Property](param, sortBy), GetType(Object)), param)
    
    		Dim IQue As IQueryable(Of T) = source.AsQueryable
    		Dim IRet As IQueryable(Of T) = (IQue.OrderByDescending(Of Object)(sortExpression))
    
    		Return IRet
    
    	End Function
    
    End Class
    

    An untested C# version of the above is:

    using System.Linq.Expressions;
    
    public class GenericListSorter<T>
    {
    	public IEnumerable<T> SortAscending(IEnumerable<T> source, string sortBy)
    	{
    		object param = Expression.Parameter(typeof(T), "item");
    		object sortExpression = Expression.Lambda<Func<T, object>>(Expression.Convert(Expression.Property(param, sortBy), typeof(object)), param);
    
    		IQueryable<T> IQue = source.AsQueryable;
    		IQueryable<T> IRet = (IQue.OrderBy<object>(sortExpression));
    
    		return IRet;
    
    	}
    
    	public IEnumerable<T> SortDescending(IEnumerable<T> source, string sortBy)
    	{
    		object param = Expression.Parameter(typeof(T), "item");
    		object sortExpression = Expression.Lambda<Func<T, object>>(Expression.Convert(Expression.Property(param, sortBy), typeof(object)), param);
    
    		IQueryable<T> IQue = source.AsQueryable;
    		IQueryable<T> IRet = (IQue.OrderByDescending<object>(sortExpression));
    
    		return IRet;
    
    	}
    }
    

    Hope this helps (and BTW this library will allow you to do things like Query by Example using the library to build the Linq query),

    LS


    Lloyd Sheen

    • Marked as answer by Alexander Sun Wednesday, January 16, 2013 9:31 AM
    Friday, January 4, 2013 11:19 PM