How do I perform Server Side grouping and Aggregating

[HaBo]

 We have a heavy traffic data in DB, we don't want to dump huge data on to the client side and do grouping and aggregating.

So I started doing it on server side pagination, grouping and stuck with one column grouping. Need help with support multiple column grouping and then also able to aggregate data on server side.

Here is my grid Read Method

[AcceptVerbs(HttpVerbs.Post)]
       public ActionResult Read_Traffic([DataSourceRequest] DataSourceRequest request)
       {
           var results = _auditService.All(OperatingUser.CompanyId, request, out int totalRecords);
           if (request.Groups != null && request.Groups.Any())
           {
               //HaBo: Currently supporting only single grouping.
               //need to work on to support multiple groupings.
               return Json(new DataSourceResult
               {
                   Data = CustomGroupBy(results, request.Groups[0].Member),
                   Total = totalRecords
               });
           }
           //List<object> returnData = results.Cast<object>().ToList();
           //foreach (var groupDescriptor in request.Groups)
           //{
           //    returnData = (List<object>) CustomGroupBy(results, groupDescriptor.Member);
           //}
           return Json(new DataSourceResult
           {
               Data = results,
               Total = totalRecords // Total number of records
           });
       }

 

Here is my Grouping Method

private static IEnumerable<object> CustomGroupBy(List<Audit> results, string groupKey)
       {
           switch (groupKey)
           {
               case "SessionId":
                   return results.GroupBy(x => x.SessionId).Select(g =>
                       new { Aggregates = new { }, Key = g.Key, HasSubgroups = false, Member = "SessionId", Subgroups = new object[] { }, Items = g });
               case "AuditId":
                   return results.GroupBy(x => x.AuditId).Select(g =>
                       new { Aggregates = new { }, Key = g.Key, HasSubgroups = false, Member = "AuditId", Subgroups = new object[] { }, Items = g });
               case "UserName":
                   return results.GroupBy(x => x.UserName).Select(g =>
                       new { Aggregates = new { }, Key = g.Key, HasSubgroups = false, Member = "UserName", Subgroups = new object[] { }, Items = g });
               case "IpAddress":
                   return results.GroupBy(x => x.IpAddress).Select(g =>
                       new { Aggregates = new { }, Key = g.Key, HasSubgroups = false, Member = "IpAddress", Subgroups = new object[] { }, Items = g });
               case "UrlReferrer":
                   return results.GroupBy(x => x.UrlReferrer).Select(g =>
                       new { Aggregates = new { }, Key = g.Key, HasSubgroups = false, Member = "UrlReferrer", Subgroups = new object[] { }, Items = g });
               case "TimeAccessed":
                   return results.GroupBy(x => x.TimeAccessed).Select(g =>
                       new { Aggregates = new { }, Key = g.Key, HasSubgroups = false, Member = "TimeAccessed", Subgroups = new object[] { }, Items = g });
               case "CountryName":
                   return results.GroupBy(x => x.CountryName).Select(g =>
                       new { Aggregates = new { }, Key = g.Key, HasSubgroups = false, Member = "CountryName", Subgroups = new object[] { }, Items = g });
               case "CountryCode":
                   return results.GroupBy(x => x.CountryCode).Select(g =>
                       new { Aggregates = new { }, Key = g.Key, HasSubgroups = false, Member = "CountryCode", Subgroups = new object[] { }, Items = g });
           }
           return null;
       }

 

As you can notice currently I am only able to group with the first group member, how can I support multiple column grouping?

[Tsvetina]

Hi HaBo,

I should first note that if you let the ToDataSourceResult extension method handle the data, you will still get only the current page of data on the client. Applying the filter, sort, group and page parameters manually to the data is not required in order to return only a subset of the entire data.

You can see for yourself if you inspect the network requests of this demo:
Grid / Basic usage
If you open the browser developer tools (F12), navigate to the Network tab and then group the Grid, you will see in the Customers_Read request that the server returns only 20 items (the page size).

Would you consider using this approach or you still prefer to run your custom grouping logic?

Regards,
Tsvetina
Progress Telerik

[HaBo]

Hi Tsvetina,

Thanks for your suggestion. I tested that .ToDataSourceResult() but the problem is, on Sorting and Filtering I have different columns in Grid with are not used in server side sort or filter.

For Example I have foreign Key in grid like CountryId, but when user sorts, I run sort on Country.Name on server side, that will break if I again let .ToDataSourceResult() to go the rest of the work.

[Tsvetina]

Hi HaBo,

In such a case, going back to the custom operations, you can see the custom group handling implementation in the Custom Ajax Binding demo. It traverses the applied groups (in reverse order) and builds a group expression. The relevant source code is in the CustomAjaxBindingController.cs section. 

Regards,
Tsvetina
Progress Telerik

[HaBo]

Hi 

CustomAjaxBindingController.cs will do sorting, paging, grouping filter only how about aggregating?

[Tsvetina]

Hi Harsha,

We do not have an example of calculating the aggregates. You need to do it manually based on your specific scenario as this is not as trivial as applying the filters, sorting or paging. The ToDataSourceResult implementation for aggregates includes generating dynamic classes on the fly and complicated LINQ queries to populate them. Thus, unfortunately, this is not something which belongs to a general purpose example. 

Regards,
Tsvetina
Progress Telerik

[HaBo]

Hi Tsvetina

That is disappointing that you feel that Aggregating on server side is not of interest to large set of audiences.

But would you be able to share a pseudo code to give us a start on how to accomplish it?

[Tsvetina]

Hello HaBo,

I am sorry if my reply sounded like this. What I meant was that the code for calculating aggregates would be different for every scenario. We do not have a sample project for it because implementing code that would work in all or even most user scenarios would introduce great complexity to the sample, which most developers would not need.

As an example to follow, you can see how the DataSource widget calculates the aggregates if you download the Kendo.Mvc source project from the Source Code section here (requires login) and add it to an MVC Grid app instead of using the pre-built Kendo.Mvc DLL. The logic is mostly in the QueryableExtensions.cs and AggregateFunctionsGroup.cs file. It would be easier to follow it if you attach a breakpoint in the Read controller method of a Grid that does grouping with aggregates and then step through the code.

If you want to avoid the complexity of following the DataSource structure, you can create your own implementation of the Groups and Aggregates structure. In general, there's no need to use any of the helper classes like Group or AggregateFunctionsGroup.
These classes are serialized to the particular format that the data source component expects. Any other class that has the same public properties will work just as well.

You also have the option to override the response format, replacing DataSourceResult entirely. The schema configuration defines how the response is processed on the client. You can choose to store the groups separately from the data and override schema.group, for example. The same is true for aggregates via the schema.aggregates setting. These settings can be accessed if you use a Custom DataSource configuration like the one in this demo.

Regards,
Tsvetina
Progress Telerik