none
How do I use LINQ to sort items using a variable "sort-ordering/subordering" scheme? RRS feed

  • Question

  • WHAT I HAVE:

    VB 2010, .NET 4.0, EF 4.0, SQL Express

    MY PROBLEM:

    Let's say I have an entity or other list of items that I want to sort using LINQ by multiple criteria, but I want the program to be able to choose dynamically what sequence in which the criteria are used when it comes to determining the overall sort order. For instance, if my query sorts based on the values of 4 fields--say, fieldA, fieldB, fieldC, and fieldD--I can do up to 24 different "Order By"'s---i.e.,

    From listofitems OrderBy fieldA, fieldB, fieldC, fieldD  ... , or

    From listofitems OrderBy fieldB, fieldA, fieldC, fieldD  ... , or

    From listofitems OrderBy fieldD, fieldB, fieldA, fieldC  ... , or

    From listofitems OrderBy fieldA, fieldC, fieldB, fieldD  ... , or

    any 1 of 20 other query arrangements. Suppose I want the program/user to be able to pick any of these arbitrary sorting schemes at run-time, but I don't want to have to write 24 different queries that differ only in the order fields are specified for "Order By". Is there someway to "build" the "Order By" list at run-time (i.e., using a string or multiple queries)--and remember, I want to use LINQ so it's general-purpose for any kind of list and I'm not tied to using a (specific) database and SQL-query strings.

    What should I do?


    Robert Gustafson






    Wednesday, February 19, 2014 3:02 AM

Answers

  • I just did. Like I just added above, you first get the (unsorted) query results to list/array then use the Sort method with a custom comparison class that includes a custom property/constructor to tell its Compare function what sorting scheme to use.

    Robert Gustafson

    Thursday, February 20, 2014 10:17 PM
  • Just the 500. The point is that (based on the intended use) it's quite possible that you will eventually only want the first 20 out of those 500. Or skip the first 20 and show the next 20 in case you are showing the second page.

    I generally try to do as much as possible on IQueryable and thus in the database, but that's because I work on a mostly web project and eventually display just a short page of the matching rows.

    Jenda


    http://jendaperl.blogspot.com<br/> A Perl developer in the world of C#

    Wednesday, March 5, 2014 7:36 AM

All replies

  • I had to write a dynamic query to query a database table based on selections made by the UI user on the criteria given. I chose to use Entity SQL to dynamically do it. You can do the same thing with ESQL with results returned that can be read by ESQL datareader and populate objects off the model in a List<T>.

    http://msdn.microsoft.com/en-us/library/bb738684(v=vs.110).aspx?cs-save-lang=1&cs-lang=vb#code-snippet-2

    sorting is being shown in the link.

    http://blogs.msdn.com/b/adonet/archive/2007/05/30/entitysql.aspx

    http://msdn.microsoft.com/en-us/library/bb387145(v=vs.110).aspx

    You can go with ESQL this way too.

    http://www.informit.com/articles/article.aspx?p=1273361&seqNum=3

    Using Linq is not a stops all and ends all, and it can't do everything.

    Wednesday, February 19, 2014 6:21 AM
  • There's a solution! Use LINQ to perform the pre-sort query, convert it to a list (using .ToList) or an array (using .ToArray), then sort it using .Sort(comparerclass), where comparerclass is a comparison class that implements IComparer(Of comparisontype) and the Compare function--which implements IComparer(Of comparisontype).Compare, where comparisontype is the type of the list/array. The trick is to implement a custom property and/or constructor that indicates what sorting scheme the Compare function is to use. Since the sort is done without LINQ, a previously-undetermined sorting scheme can be implemented dynamically.

    (NOTE: If the original query projects an anonymous type for its result, then you must convert it to a list/array--particularly if you want to use it outside of the procedure--then assign that to an Object variable. In that case, comparisontype must also be Object.)

    Here's an example of what the comparison class might look like (I've simplified my original example to sorting on just 2 fields--"First" and "Last"--for convenience, but it'd work on 4 fields or any other number just as easily):

    Public Class CompareItems : Implements IComparer(Of Object)
    '   set property to "First,Last" or "Last,First" depending on desired ordering scheme
    Public Property SortKey As String
     
    Public Sub New(ByVal sk As String)
    	Me.SortKey = sk
    End Sub
     
    Public Function Compare(x As Objecty As ObjectAs Integer _
    	Implements IComparer(Of Object).Compare
    Dim Keys() As String = Me.SortKey.Split(",") : Dim c As Integer = 0
    For each key In Keys 'compare field by field until a difference is found
    	c = CompareField(keyxy)
    	If c <> 0 Then Return c
    Next key
    Return 0 'no difference
     
    End Function
     
    Private Function CompareField(ByVal Field As Stringx As Objecty As Object)
    If Field = "First" Then
    	Return String.Compare(x.Firsty.First)
     Else
    	Return String.Compare(x.Lasty.Last)
    End If
    End Function
    End Class










    Thursday, February 20, 2014 3:43 AM
  • I don't know man. It is something you are going to have to figure it out.

    http://msdn.microsoft.com/en-us/library/b0zbh7b6(v=vs.110).aspx

    Thursday, February 20, 2014 10:02 PM
  • I just did. Like I just added above, you first get the (unsorted) query results to list/array then use the Sort method with a custom comparison class that includes a custom property/constructor to tell its Compare function what sorting scheme to use.

    Robert Gustafson

    Thursday, February 20, 2014 10:17 PM
  • You can add the sorts to the query one after another so something like this would be enough:

    public IQueryable<ListItem> OrderBy(this IQueryable<ListItem> query, string firstCol, params string[] otherCols) {
      switch (firstCol) {
        case "fieldA": query = query.OrderBy(l => l.fielA);break;
        case "fieldB": query = query.OrderBy(l => l.fielB);break;
        case "fieldC": query = query.OrderBy(l => l.fielC);break;
        case "fieldD": query = query.OrderBy(l => l.fielD);break;
        default: throw new ArgumentException("invalid column name","firstCol");
      }
      foreach (var col in otherCols) {
        switch (col) {
          case "fieldA": query = query.ThenBy(l => l.fielA);break;
          case "fieldB": query = query.ThenBy(l => l.fielB);break;
          case "fieldC": query = query.ThenBy(l => l.fielC);break;
          case "fieldD": query = query.ThenBy(l => l.fielD);break;
          default: throw new ArgumentException("invalid column name","otherCols");
        }
      }
      return query;
    }

    Jenda


    ----------------------------------
    http://jendaperl.blogspot.com
    A Perl developer in the world of C#

    Saturday, March 1, 2014 11:56 AM
  • Care to translate that into VB?

    Robert Gustafson

    Sunday, March 2, 2014 8:23 PM
  • Care to translate that into VB?

    http://blogs.msdn.com/b/kevin_halverson/archive/2007/07/10/how-to-implement-iqueryable.aspx

    That would be IQuerable(of ListItem)

    I'll assume you know how to do the foreach loop.

    The String[] is just an array -- String() in VB, and the rest of the method signature, you should be able to figure out,

    I don't know about the query.ThenBy which is just a extension Linq method you can do in VB too I suspect. I'll assume you know how to do a Case statement and how to exit the Foreach loop in VB.

    case "fieldB": query = query.ThenBy(l => l.fielB);break;

    (l => l.fielB)  --- a Lambda expression is being used in C#

    (Function(I) I.fielB) is the VB Lambda equivalent.

    Sunday, March 2, 2014 8:51 PM
  • Vey interesting approach! However, I still think my own solution--creating a comparison class with a property to indicate the sort scheme--is more flexible.

    In this general-purpose routine, QueryType is the type of the query list/array to be sorted (Object if of an anonymous type), FieldxName (x = 1,2,3,4,...) is the string-name of each field, FieldxType is the type of each field, and Fieldx is the property-name of each field.

    Public Class CompareByFields

       Implements IComparer(Of QueryType)

       Public Property SortKeys As String 'list of successive fields to sort on

       Public Sub New(sk As String)

       Me.SortKeys = sk

       End Sub

       Public Function Compare (x As QueryType, y As QueryType) _

          Implements IComparer(Of QueryType).Compare

       Dim Keys() As String = Me.SortKeys.Split(",").Trim() : Dim c As Integer = 0

       For Each key In Keys()

          c = CompareField(key, x, y) 'compare one field at a time

          If c <> 0 Then Return c        'until a difference is found

       Next key

       Return 0 'both are the same in all fields

       End Function

       Private Function CompareField(key As String, x As QueryType, y As QueryType)

       Select Case key 'compare based on specified field

         Case "Field1Name"

            Return Field1Type.Compare(x.Field1, y.Field1)

         Case "Field2Name"

            Return Field2Type.Compare(x.Field2, y.Field2)

         Case "Field3Name"

            Return Field3Type.Compare(x.Field3, y.Field3)

         Case "Field4Name"

            Return Field4Type.Compare(x.Field4, y.Field4)

         '   any other fields

         ' ...

        Case Else

            Throw New Argument Exception("Invalid field name: """ & key & """")

       End Select

       End Function

    End Class

    To use the sort, one does the following, where query is the original query:

    QueryList As List(Of QueryType) = query.ToList,   or

    QueryList() As QueryTypequery.ToArray,   followed by:

    '   set up sort order

    Dim SortScheme As New CompareByFields()

    SortScheme.SortKeys = "Field1Name,Field2Name,Field3Name,Field4Name, ..." 'ordering rule

    QueryList.Sort(SortScheme)


    Robert Gustafson






    Monday, March 3, 2014 11:07 PM
  • The catch is that this way the sorting is done on the .Net side, not in the database.

    It's not so important if you intend to consume all the data, even though it does prevent the use of database indexes, but if you then want to take and show just the first 20 rows ... well you'd better hope the query doesn't return millions of rows.

    Of course if the source of the data is not a database, then this doesn't matter.

    Jenda


    ----------------------------------
    http://jendaperl.blogspot.com
    A Perl developer in the world of C#

    Tuesday, March 4, 2014 1:13 AM
  • You can use Dynamic LINQ.

    Refer:

    http://www.codeproject.com/Tips/666957/Dynamic-Sorting-in-LINQ-Part


    Thanks, Rocky Fernandes

    Tuesday, March 4, 2014 1:29 AM
  • How about manually copying the query results into a array or list, using a "For Each" loop, instead of the ".ToArray"/".ToList" method? That should avoid the consumption of the entire data. Or is there too much potential for a performance penalty?

    (BTW, does "consume all the data" mean the data from which the query is done, or just the data returned by the query? If a query returns 500 rows from an entity with 20,000 rows, does .ToArray/.ToList cause just the 500 to be loaded, or are the other 19,500 rows also loaded--even though they're not used? I'd want to load all the 500, but only the 500, and Jenda's response regarding data consumption above sounds confusing.)


    Robert Gustafson







    Wednesday, March 5, 2014 1:35 AM
  • Just the 500. The point is that (based on the intended use) it's quite possible that you will eventually only want the first 20 out of those 500. Or skip the first 20 and show the next 20 in case you are showing the second page.

    I generally try to do as much as possible on IQueryable and thus in the database, but that's because I work on a mostly web project and eventually display just a short page of the matching rows.

    Jenda


    http://jendaperl.blogspot.com<br/> A Perl developer in the world of C#

    Wednesday, March 5, 2014 7:36 AM
  • Thank you for clearing that up. (In any case, I would want to perform the sort on the 500, before using a secondary query for any paging.)

    Robert Gustafson

    Wednesday, March 5, 2014 7:38 AM