Returning Total for ServerPaging

[Divyang]

Hi,

I have two tables "Workers" and "Skills" with Many-to-Many relationship. I want to populate a kendoGrid for workers which should allow to filter on Skills. For this I've create a Web API controller which is returning the first row to avoid duplicates because of Many-to-Many:

Return query.GroupBy(Function(t) t.WorkerID).Select(Function(grp) grp.First()).AsQueryable()

In this scenario how can I return the Total for kendo"DataSource" object to make the ServerPaging work?

Thanks.

[Hristo Valyavicharski]

Hi,

You should use DataSourceRequest object. Look at this sample: Kendo Grid WebAPI CRUD.

Regards,
Hristo Valyavicharski
Telerik

 

 

[Divyang]

Thanks Hristo

I've gone through the sample project and it is all ASP.Net MVC whereas I am using Javascript/HTML at client side. I've learnt that to enable server-side paging I need to return "Total" property of "DataSource" object explicitly. In the controller I am returning only row from the query, for example:

query result,
ID   Value

1    a

1    b

2    c

3    d

3     e
Out put would be,
ID   Value

1    a

2    c

3    d

I want to return total number of rows as 3 and not 5 and the best way to do it.

DataSource configuration:

var dataSource = new kendo.data.DataSource({
    type: "odata",
    transport: {
        read: {
            url: function (options) {
                return "../api/workersearch";
            },
            dataType: "json"
        }
    },
    schema: {
        model: {
            fields: {
                WorkerID: { type: "number" },
                Title: { type: "string" },
                ForeName: { type: "string" },
                SurName: { type: "string" },
            }
        },
        //--------------------------------------------------------------
        //Show the data & count on the grid (otherwise it'll throw the error - "Unable to get __count of undefined or null...."
        data: function (data) {
            return data;
        },
        total: function (data) {
            //NEED TO KNOW HOW TO RETURN TOTAL?
        }
        //--------------------------------------------------------------
    },
    pageSize: 20,
    serverPaging: true,
    serverFiltering: true,
    serverSorting: true,
});

Controller method:

Public Function GetValues() As IQueryable(Of WorkerSearch)
    Dim query As New List(Of WorkerSearch)

    Try
        Using context As ServerApplicationContext = ServerApplicationContext.CreateContext()

            Using conn As New SqlConnection(ConfigurationManager.ConnectionStrings(
              context.DataWorkspace.TempIDData.Details.Name).ConnectionString)

                Dim cmd As New SqlCommand()
                cmd.Connection = conn
                cmd.CommandText = "uspWorkerSearch"
                cmd.CommandType = CommandType.StoredProcedure
                cmd.Connection.Open()

                Dim da As SqlDataAdapter = New SqlDataAdapter
                da.SelectCommand = cmd
                Dim ds As DataSet = New DataSet
                da.Fill(ds)

                Dim table As DataTable = ds.Tables(0)

                For Each row As DataRow In table.Rows

                    query.Add(New WorkerSearch With {
                            .WorkerID = row.Field(Of Integer)("WorkerID"),
                            .Title = row.Field(Of String)("Title"),
                            .ForeName = row.Field(Of String)("ForeName"),
                            .SurName = row.Field(Of String)("SurName")
                        })
                Next
            End Using
        End Using
    Catch castEx As System.InvalidCastException
        Throw
    Catch ex As Exception
        Throw
    End Try

    Return query.GroupBy(Function(t) t.WorkerID).Select(Function(grp) grp.First()).AsQueryable()
End Function

[Vladimir Iliev]

Hi,

You can return the Total from the server side by wrapping the data array in another object that have for example the following fields:

• "Data" field - here you can add the previous data
• "Total" field - here you can add the Total count

On the client side you can use the dataSource schema "data" and "total" options as demonstrated below:

data: function(data) {
    if (data && data.Data) {
        return data.Data;
    }
    return [];
},

total: function(data) {
    if (data && data.Total) {
        return data.Total;
    }
    return 0;
},

Regards,
Vladimir Iliev
Telerik

 

 

[Divyang]

Hi Vladimir,

I've set server filtering as well and this doesn't work when a filter is applied. It always return total number of rows before filtering the data.

[Vladimir Iliev]

Hi,

The described behavior appears to be related to the server side code that you are using. Could you please try to modify it in order to get the total count after the filtering is applied to the data?

Regards,
Vladimir Iliev
Telerik

 

 

[Divyang]

I've tried to follow your suggestions to change server side method but it is not working. If I wrap the query result in an object and return it, I can't use OData query parameters. 

Here is the updated method:

 

<EnableQuery>
Public Function Getvalues(id As Integer) As Object 'returning an object instead of IQuarable()

    Dim query As New List(Of WorkerSearch)

    Dim userID As Short = SharedClass.GetCurrentUser.UserID
    Dim ownerID As Short = SharedClass.GetSystemOwner.OwnerID
    Try
        Using context As ServerApplicationContext = ServerApplicationContext.CreateContext()

            Using conn As New SqlConnection(ConfigurationManager.ConnectionStrings(
              context.DataWorkspace.ApplicationData.Details.Name).ConnectionString)

                Dim cmd As New SqlCommand()
                cmd.Connection = conn
                cmd.CommandText = "uspWorkerSearch"
                cmd.CommandType = CommandType.StoredProcedure
                cmd.Parameters.Add(New SqlParameter("@intSystemOwnerID", ownerID))
                cmd.Parameters.Add(New SqlParameter("@intUserID", userID))
                cmd.Connection.Open()

                Dim da As SqlDataAdapter = New SqlDataAdapter
                da.SelectCommand = cmd
                Dim ds As DataSet = New DataSet
                da.Fill(ds)

                Dim table As DataTable = ds.Tables(0)

                For Each row As DataRow In table.Rows

                    query.Add(New WorkerSearch With {
                            .WorkerID = row.Field(Of Integer)("WorkerID"),
                            .Title = row.Field(Of String)("Title"),
                            .ForeName = row.Field(Of String)("ForeName"),
                            .SurName = row.Field(Of String)("SurName")
                        })
                Next
            End Using
        End Using
    Catch castEx As System.InvalidCastException
        Throw
    Catch ex As Exception
        Throw
    End Try

    'Wraping up query in an array
    Dim myArr() As WorkerSearch = query.GroupBy(Function(t) t.WorkerID).Select(Function(grp) grp.First()).ToArray()
    'Returning the object with data and total
    Return New With {
        .Data = myArr,
        .Total = myArr.Length}
End Function

Can you please guide  me  to the right direction.

Thanks.

[Vladimir Iliev]

Hi Divyang,

In current case handling the request on the server side falls outside the scope of our support service as it covers the build-in functionality of the controls only. That why currently I could only suggest to make sure the query is executed before getting the tolal count. 

Another option would be to check the "Kendo.DynamicLinq" project which implements server paging (including total count), filtering, sorting and aggregating and you can use it to request the data in your case. 

Regards,
Vladimir Iliev
Telerik

 

 

[Simon]

Hi,

another solution would be to write an odata function that returns the correct number of rows and call this via an ajax call from the total function of the datasource?

 

[Divyang]

Does this mean if I want to return IQuearable(), I need to explicitly specify total number of rows every time a filter is applied? For example my controller method is returning 1000 rows and I have set server paging and filtering. The paging is fine for all rows, but when a filter is applied the paging doesn't change even if there is only 10 rows returned.

[Vladimir Iliev]

Hi,

When server operations (including paging) is used the server side should return back to the client side number of records that are equal or less than the selected page size. If filtering is applied you should first filter the records, then get the total count and finally get only the records for current page and page size. 

Regards,
Vladimir Iliev
Telerik

 

 

[Divyang]

Please correct me if I am wrong: At the moment because of <EnableQuery> statement when my web method receives a request for filter it applies the filter on IQuearable(?), therefore total count always return count of all rows. I need to change the method so that is knows which query parameters are sent and execute the query accordingly with correct total count. 

[Vladimir Iliev]

Hello,

As I mention in my previous replies modifying the custom server code that you are using falls outside the scope of this support service and depends entirely on you. 

Regards,
Vladimir Iliev
Telerik