none
Error trying to sort IQueryable RRS feed

  • Question

  • Hi,

    I’m trying to sort a IQueryable dynamicaly.

    Dim activities =From a In db.VW_ActivitiesSelect a
     activities = activities.OrderBy(Function(a)New ActivityComparer(SortFields(0).Sort))

    definition of SortFields:

    Public SortFields()As List(OfSortField)

    Class Sortfield has 3 properties:

    Public Property Column()As String
     Public Property Type()As Type
     Public Property Sort()As SortOrder

    Definition of class ActivityComparer

    PrivateClass ActivityComparer
     Implements System.Collections.IComparer
     Private sortOrderModifier As Integer = 1
     Public SubNew(ByVal sortOrderAs System.Windows.Forms.SortOrder)
     If sortOrder = System.Windows.Forms.SortOrder.DescendingThen
     sortOrderModifier = -1
     ElseIf sortOrder = System.Windows.Forms.SortOrder.AscendingThen
     sortOrderModifier = 1
     End If
     End Sub
     Public Function CompareActivity(xAs Object, y As Object) AsInteger _
     Implements System.Collections.IComparer.Compare
     Dim a1As VW_Activitie =CType(x, VW_Activitie)
     Dim a2 As VW_Activitie = CType(y, VW_Activitie)
     Dim CompareResultAs Integer = 0
     For nIndex = 0 To SortFields.Count - 1
     If CompareResult = 0 AndAlso SortFields.Count >= nIndex + 1 Then
     CompareResult = GetActivityCompareResult(a1, a2, SortFields.Item(nIndex))
     Else
     Exit For
     End If
     Next
     Return CompareResult * sortOrderModifier
     End Function
     Public Function GetActivityCompareResult(ByRef a1As VW_Activitie,ByRef a2 As VW_Activitie, ByRef sf As SortField) As Integer
     Dim cr As Integer = 0
     Dim colProp AsPropertyInfo() = a1.[GetType]().GetProperties()
     For Each propInfoAs PropertyInfoIn colProp
     If propInfo.Name = sf.Column Then
     Dim act1 AsObject = propInfo.GetValue(a1, Nothing)
     Dim act2 AsObject = propInfo.GetValue(a2, Nothing)
     SelectCase sf.Type
     Case GetType(System.String)
     cr = System.String.Compare(act1.ToString(), act2.ToString())
     Case GetType(System.DateTime)
     cr = System.DateTime.Compare(CDate(act1),CDate(act2))
     Case GetType(System.Int64)
     Dim n1 As Int64 = CLng(act1)
     Dim n2 As Int64 = CLng(act2)
     If n1 < n2 Then
     cr = -1
     ElseIf n1 = n2 Then
     cr = 0
     Else
     cr = 1
     End If
     Case GetType(System.Decimal)
     Dim n1 As Decimal = CDec(act1)
     Dim n2 As Decimal = CDec(act2)
     If n1 < n2 Then
     cr = -1
     ElseIf n1 = n2 Then
     cr = 0
     Else
     cr = 1
     End If
     End Select
     End If
     Next
     Return cr
     End Function
     EndClass

    I see the error in Locals window while debugging or in the report viewer that needs to show a report based on the IQueryable activities as datasource.

    Cannot order by ‘MyNameSpace.FrmActivities+ActivityComparer’
    In order to evaluate an indexed property, the property must be qualified and the arguments must be explicitly supplied by the user.

    What does this error message mean?

    Thanks

    Ruwel

     

     

     

    Tuesday, October 4, 2011 1:19 PM

All replies

  • I am providing the code for a Generic Sorter using Linq in VB:

     

    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
    
    


    Hope this helps

    Lloyd

     


    Lloyd Sheen
    Wednesday, October 5, 2011 6:25 PM
  • Hi Ruwel,

    You can refer this link: http://weblogs.asp.net/scottgu/archive/2008/01/07/dynamic-linq-part-1-using-the-linq-dynamic-query-library.aspx

    Have a nice day.


    Alan Chen[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Thursday, October 6, 2011 1:57 AM
    Moderator
  • Hi Lloyd,

    Thanks for your reply.

    The code of the GenericSorter class you posted could work if you have only one column to sort on.

    In my case there can be no sorting, one column to sort on (asc or desc), two columns to sort on (both asc or desc) , three columns to sort on (all three of them asc or desc) or four columns to sort on (all four of them asc or desc).

    The GenericSorter class would look more like this then:

    Imports System.Linq.Expressions

    Public Class GenericListSorter(Of T)

           Public Function Sort(ByVal source As IEnumerable(Of T), ByVal sortfields As List(Of SortField)) As IEnumerable(Of T)
                
    Dim IQue As IQueryable(Of T) = source.AsQueryable
                
    Dim IRet As IQueryable(Of T) = Nothing

                 Select Case sortfields.Count
                        Case 0
                               IRet = IQue

                        Case 1
                               Dim param = Expression.Parameter(GetType(T), "item")
                               Dim sf As SortField = sortfields(0)
                               Dim sortExpression = Expression.Lambda(Of Func(Of T, Object))(Expression.Convert(Expression.[Property](param, sf.Column), GetType(Object)), param)
                               Select Case sf.Sort
                                      Case SortOrder.Ascending
                                            IRet = (IQue.OrderBy(Of Object)(sortExpression))
                                      Case SortOrder.Descending
                                            IRet = (IQue.OrderByDescending(Of Object)(sortExpression))
                               End Select

                        Case 2
                               Dim param = Expression.Parameter(GetType(T), "item")
                               Dim sf0 As SortField = sortfields(0)
                               Dim sf1 As SortField = sortfields(1)

                               Dim sortExpression0 = Expression.Lambda(Of Func(Of T, Object))(Expression.Convert(Expression.[Property](param, sf0.Column), GetType(Object)), param)
                               Dim sortExpression1 = Expression.Lambda(Of Func(Of T, Object))(Expression.Convert(Expression.[Property](param, sf1.Column), GetType(Object)), param)

                               Select Case sf0.Sort
                                      Case SortOrder.Ascending
                                            Select Case sf1.Sort
                                                   Case SortOrder.Ascending
                                                          IRet = (IQue.OrderBy(Of Object)(sortExpression0).ThenBy(Of Object)(sortExpression1))
                                                   Case SortOrder.Descending
                                                          IRet = (IQue.OrderBy(Of Object)(sortExpression0).ThenByDescending(Of Object)(sortExpression1))
                                            End Select

                                      Case SortOrder.Descending
                                            Select Case sf1.Sort
                                                   Case SortOrder.Ascending
                                                          IRet = (IQue.OrderByDescending(Of Object)(sortExpression0).ThenBy(Of Object)(sortExpression1))
                                                   Case SortOrder.Descending
                                                          IRet = (IQue.OrderByDescending(Of Object)(sortExpression0).ThenByDescending(Of Object)(sortExpression1))
                                            End Select
                               End Select

                        Case 3
                               Dim param = Expression.Parameter(GetType(T), "item")
                               Dim sf0 As SortField = sortfields(0)
                               Dim sf1 As SortField = sortfields(1)
                               Dim sf2 As SortField = sortfields(2)

                               Dim sortExpression0 = Expression.Lambda(Of Func(Of T, Object))(Expression.Convert(Expression.[Property](param, sf0.Column), GetType(Object)), param)
                               Dim sortExpression1 = Expression.Lambda(Of Func(Of T, Object))(Expression.Convert(Expression.[Property](param, sf1.Column), GetType(Object)), param)
                               Dim sortExpression2 = Expression.Lambda(Of Func(Of T, Object))(Expression.Convert(Expression.[Property](param, sf1.Column), GetType(Object)), param)

    '...

                        Case 4
                               Dim param = Expression.Parameter(GetType(T), "item")
                               Dim sf0 As SortField = sortfields(0)
                               Dim sf1 As SortField = sortfields(1)
                               Dim sf2 As SortField = sortfields(2)
                               Dim sf3 As SortField = sortfields(3)

                               Dim sortExpression0 = Expression.Lambda(Of Func(Of T, Object))(Expression.Convert(Expression.[Property](param, sf0.Column), GetType(Object)), param)
                               Dim sortExpression1 = Expression.Lambda(Of Func(Of T, Object))(Expression.Convert(Expression.[Property](param, sf1.Column), GetType(Object)), param)
                               Dim sortExpression3 = Expression.Lambda(Of Func(Of T, Object))(Expression.Convert(Expression.[Property](param, sf2.Column), GetType(Object)), param)
                               Dim sortExpression4 = Expression.Lambda(Of Func(Of T, Object))(Expression.Convert(Expression.[Property](param, sf3.Column), GetType(Object)), param)

    '...

                 End Select

                 Return IRet
           End Function

    End Class

     

    This is not realy a favorable sollution.

     RuWel

     

     

     

     

     

     

     


    • Edited by RuWel Friday, October 7, 2011 1:26 PM
    Friday, October 7, 2011 1:24 PM
  • Hi Alan,

     

    thanks for your reply.

    Do I need to compile the DynamicLINQVB\App_Code\DynamicLinq.vb first and make it into a dll? Because when I import this vb file into my project I get conflicts with the namespaces already in my project.

     

    RuWel

    Friday, October 7, 2011 1:39 PM
  • I remember in a past version of VS (most likely 2008) I had to do something to get it to work.  I just created a generic WPF project in VS2010 and added some dummy data to sort, added the class, added code to use the class and executed.

    In this configuration I did not have to do anything extra.

    I also took a look into the problem of multiple sort fields.  I cannot at the present time give you a solution to this problem.  The Expression class takes only on "expression" at a time that I can find.  Even if you attempt to sort once for each field there is no guarantee that the original sort order will be respected.

     


    Lloyd Sheen
    Monday, October 10, 2011 8:59 PM
  • Hi Lloyd,

     

    thanks for your reply.

    I will make a List of the IQueryable and sort the list. Until I find a sollution.

     

    Ruwel

    Thursday, October 13, 2011 8:27 AM