Can't sort data using my WCF service functions

[Stefan]

Hey!

First of all great product!

I have run into a small problem with the sorting of the data inside the grid.

I fetch the data using WCF Data Service and custom functions to filter the data.

A function could look like this:

<WebGet()> _

Public Function Search(price As Integer) As IQueryable(Of tblCustomers)

 

    Dim context As OnlmCRMEntities = Me.CurrentDataSource

 

    Try

 

        Dim customers = From c In context.tblCustomers

          Where c.fldActive = True _

          And c.tblCustomerProducts.Any(Function(s) s.fldPrice = price And s.fldActive = True)

          Order By c.fldEmail

          Select c

 

        Return customers

    Catch ex As Exception

        Throw New ApplicationException("An error occurred: {0}", ex)

    End Try

End Function

I have also used the Data Service JSONP extension (http://archive.msdn.microsoft.com/DataServicesJSONP ) to make it work as described in many posts.

Everything works as intended except the "Order By c.fldEmail" in my Search Function.
The output will always be ordered by the id field when it's converted to JSON.
I know i can add $orderby to sort it but this is not the functionality i'm looking for as i might want to sort data based on related tables in the database.

What is causing this and how do I fix it?

This is my kendo datasource - works as intended:

dataSource: {

            type: "odata",

            transport: {

                read: pathname + "/model/WcfData.svc/Search?price=6000"

            },

            pageSize: 20,

            serverPaging: true,

            serverFiltering: true,

            serverSorting: true

        }

A small bonus question :)
Is it possible to sort by a column at load? (not really important if i can get above to work)

Best regards and thanks in advance!

Stefan

[Atanas Korchev]

Hello,

 We are not really sure what is causing this because the JSON serialization is not performed by any Telerik code. It is done by the WCF runtime. Still you can try calling ToList and see if the right result is returned:
Regards,

Dim result = customers.ToList()
'check in the debugger whethere "result" is properly sorted

Return result;

Atanas Korchev
the Telerik team

[Stefan]

I have narrowed down the problem to the "JSONSupportBehavior" class

For some reason it gets the data unsorted even though it filters and does everything correctly from my function. It makes no sense to me :/

This is my JSONSupportBehavior Class:

Imports System.ServiceModel

Imports System.ServiceModel.Channels

Imports System.ServiceModel.Description

Imports System.ServiceModel.Dispatcher

Imports System.Text

Imports System.Xml

 

Public Class json

    Implements IDispatchMessageInspector

 

    Private Shared encoding As Encoding = Encoding.UTF8

 

    Public Function AfterReceiveRequest(ByRef request As System.ServiceModel.Channels.Message, channel As IClientChannel, instanceContext As InstanceContext) As Object Implements IDispatchMessageInspector.AfterReceiveRequest

        If request.Properties.ContainsKey("UriTemplateMatchResults") Then

            Dim httpmsg As HttpRequestMessageProperty = DirectCast(request.Properties(HttpRequestMessageProperty.Name), HttpRequestMessageProperty)

            Dim match As UriTemplateMatch = DirectCast(request.Properties("UriTemplateMatchResults"), UriTemplateMatch)

 

            Dim format As String = match.QueryParameters("$format")

            If "json".Equals(format, StringComparison.InvariantCultureIgnoreCase) Then

                ' strip out $format from the query options to avoid an error

                ' due to use of a reserved option (starts with "$")

                match.QueryParameters.Remove("$format")

 

                ' replace the Accept header so that the Data Services runtime

                ' assumes the client asked for a JSON representation

                httpmsg.Headers("Accept") = "application/json"

 

                Dim callback As String = match.QueryParameters("$callback")

                If Not String.IsNullOrEmpty(callback) Then

                    match.QueryParameters.Remove("$callback")

                    Return callback

                End If

            End If

        End If

        Return Nothing

    End Function

 

    Public Sub BeforeSendReply(ByRef reply As System.ServiceModel.Channels.Message, correlationState As Object) Implements IDispatchMessageInspector.BeforeSendReply

        If correlationState IsNot Nothing AndAlso TypeOf correlationState Is String Then

            ' if we have a JSONP callback then buffer the response, wrap it with the

            ' callback call and then re-create the response message

 

            Dim callback As String = DirectCast(correlationState, String)

 

            Dim reader As XmlDictionaryReader = reply.GetReaderAtBodyContents()

            reader.ReadStartElement()

            Dim content As String = json.encoding.GetString(reader.ReadContentAsBase64())

 

            content = callback & "(" & content & ")"

 

            Dim newreply As Message = Message.CreateMessage(MessageVersion.None, "", New Writer(content))

            newreply.Properties.CopyProperties(reply.Properties)

 

            reply = newreply

        End If

    End Sub

 

    Private Class Writer

        Inherits BodyWriter

        Private content As String

 

        Public Sub New(content As String)

            MyBase.New(False)

            Me.content = content

        End Sub

 

        Protected Overrides Sub OnWriteBodyContents(writer As XmlDictionaryWriter)

            writer.WriteStartElement("Binary")

            Dim buffer As Byte() = json.encoding.GetBytes(Me.content)

            writer.WriteBase64(buffer, 0, buffer.Length)

            writer.WriteEndElement()

        End Sub

    End Class

 

End Class

 

 

 

' Simply apply this attribute to a DataService-derived class to get

' JSONP support in that service

<AttributeUsage(AttributeTargets.[Class])> _

Public Class JSONPSupportBehaviorAttribute

    Inherits Attribute

    Implements IServiceBehavior

 

    Private Sub IServiceBehavior_AddBindingParameters(serviceDescription As ServiceDescription, serviceHostBase As ServiceHostBase, endpoints As System.Collections.ObjectModel.Collection(Of ServiceEndpoint), bindingParameters As System.ServiceModel.Channels.BindingParameterCollection) Implements IServiceBehavior.AddBindingParameters

    End Sub

 

    Private Sub IServiceBehavior_ApplyDispatchBehavior(serviceDescription As ServiceDescription, serviceHostBase As ServiceHostBase) Implements IServiceBehavior.ApplyDispatchBehavior

        For Each cd As ChannelDispatcher In serviceHostBase.ChannelDispatchers

            For Each ed As EndpointDispatcher In cd.Endpoints

                ed.DispatchRuntime.MessageInspectors.Add(New json)

            Next

        Next

    End Sub

 

    Private Sub IServiceBehavior_Validate(serviceDescription As ServiceDescription, serviceHostBase As ServiceHostBase) Implements IServiceBehavior.Validate

    End Sub

 

End Class

I have narrowed it down to the $top=20 that causes the problem. 
...../WcfData.svc/Search?price=6000&$callback=ds&$format=json&$inlinecount=allpages  works as a charm, but
...../WcfData.svc/Search?price=6000&$callback=ds&$format=json&$inlinecount=allpages&$top=20 doesn't!

I can't remove the $top query since it destroys the paging and speed, so how do i sort by a related table?
I could use the $orderby query but i don't 't know how when the relation is a one to many relation.
I list all Customers with the related table Products. There are several products assigned to each customer. So Customer1 can have Product1, Product2 and Product3 assigned. Is it even possible to sort by Customer/Products/fldDate ?

Else I guess I need to have a 1 to 1 related table with sorting data for each customer and update that data when products are updated.

Thanks