none
How to parameterize the sort order in a query? RRS feed

  • Question

  • In my WCF web method I am returning the data from a table.  The web method has a parameter to indicate which column the data is to be sorted by.  For example, 1 is sort by a column called Date, 2 is by a column called Lastname, and so on.

    How do I introduce this sorting column parameter in the LINQ to SQL query without writing a different code segment to handle each column sort order?

    Eg:

    from u in db.Mytable order by (?? how can I put the column selector here???) select u

    Thanks.

    Thursday, December 22, 2011 9:23 AM

Answers

  • > How do I introduce this sorting column parameter in the LINQ to SQL query without writing a different code segment to handle each column sort order?
     
     
    see "Linq OrderBy - how to dynamically pass expressions"

    or you can use Get Method below to create Expression.
     
    var dc = new DataStore(new SqlCeConnection("Data Source=Test.sdf"));
    var res = dc.Tests.OrderBy(Get<Test,string>("Name"));
    ...
    System.Linq.Expressions.Expression<Func<T,K>> Get<T,K>(string pp)
    {
        var p = System.Linq.Expressions.Expression.Parameter(typeof(T), "p");
        return System.Linq.Expressions.Expression.Lambda<Func<T, K>>(
            System.Linq.Expressions.Expression.PropertyOrField(p, pp), 
            p);
    }
    ...
    [Table(Name = "Tests")]
    public class Test
    {
        [Column(IsPrimaryKey = true, IsDbGenerated = true)]
        public int Id { get; set; }
        [Column]
        public string Name { get; set; }
    }
    public class DataStore : DataContext
    {
        public Table<Test> Tests;
        public DataStore(IDbConnection dc) : base(dc) { }
    }
     
      


    • Edited by Malobukv Thursday, December 22, 2011 8:17 PM
    • Marked as answer by K.Kong Friday, December 23, 2011 4:42 AM
    Thursday, December 22, 2011 10:31 AM

All replies

  • > How do I introduce this sorting column parameter in the LINQ to SQL query without writing a different code segment to handle each column sort order?
     
     
    see "Linq OrderBy - how to dynamically pass expressions"

    or you can use Get Method below to create Expression.
     
    var dc = new DataStore(new SqlCeConnection("Data Source=Test.sdf"));
    var res = dc.Tests.OrderBy(Get<Test,string>("Name"));
    ...
    System.Linq.Expressions.Expression<Func<T,K>> Get<T,K>(string pp)
    {
        var p = System.Linq.Expressions.Expression.Parameter(typeof(T), "p");
        return System.Linq.Expressions.Expression.Lambda<Func<T, K>>(
            System.Linq.Expressions.Expression.PropertyOrField(p, pp), 
            p);
    }
    ...
    [Table(Name = "Tests")]
    public class Test
    {
        [Column(IsPrimaryKey = true, IsDbGenerated = true)]
        public int Id { get; set; }
        [Column]
        public string Name { get; set; }
    }
    public class DataStore : DataContext
    {
        public Table<Test> Tests;
        public DataStore(IDbConnection dc) : base(dc) { }
    }
     
      


    • Edited by Malobukv Thursday, December 22, 2011 8:17 PM
    • Marked as answer by K.Kong Friday, December 23, 2011 4:42 AM
    Thursday, December 22, 2011 10:31 AM
  • I found Linq dynamic queries to be a very convenient solution to this.  For example, I could simply pass the SortExpression from a GridView (eg "LastName Desc") like: (from m in db.Members select m).OrderBy(SortExpression).

    http://weblogs.asp.net/scottgu/archive/2008/01/07/dynamic-linq-part-1-using-the-linq-dynamic-query-library.aspx You need to include the Dynamic.cs from the sample download into your project.  The expression cannot be empty ("").

    Thursday, January 19, 2012 12:46 AM