How do you use grouping WITHOUT using an IQueryable?

[Kendall Bennett]

I have been scratching my head trying to figure out how to decipher the grouping descriptors for a custom AJAX grid binding, so that I can build a grouped SQL query to produce the data in a format that the Telerik grid is expecting when grouping is enabled. The MVC sample that shows how to use a custom grid binding is useless, because it assumes your data is exposed via an IQueryable interface and it applies the grouping using LINQ. This won't work in my case, as we are not using a LINQ provider.

When I simply add a grouping statement to my SQL code, it groups the values together but it does NOT do what the Telerik grid is expecting, because it seems as though it needs it in some other format (like that returned by LINQ). All I get when I enable the grouping is a grid with the correct number of columns, but with nothing in them.

So it seems I need to be returning the data for the grouped results in some other format? What should the format be?

Ideally we would want to simply return the grouped data back to the grid, and then when someone tries to expand a particular group it would then do another AJAX query to the server to get the actual line items for all the items that are grouped together so they can be displayed.

Any suggestions or sample code on how this works?

[Rosen]

Hello Kendall Bennett,

In order grid component to consume the result of a custom grouping, this result should be IEnumerable<AggregateFunctionsGroup>. Therefore, you should project the query result in such a collection.

As to your question about loading the results when a group is expanded. I'm afraid that this is currently not supported.

Regards,
Rosen
the Telerik team

[Kendall Bennett]

It is not clear to me at all how the paging works in the situation where you are going to be enabling grouping? On the surface this grouping feature sounds pretty neat, but I think in practice it may end up being a terrible idea and difficult to implement. As I see it, if I group by a particular field I might end up with only a small number of results that drive the grouping, and under each of those groups we now have a TON of results that could be present. Which completely throws out the window the whole point of having a paged grid.

As I understand how it should be implemented, I need to do the initial grouping to determine the grouping keys, and then I need to project those results and run sub-queries for each group to that I can populate the items into the sub-groups for that grouped item? But there is no way to know how to page the results of this grouping?

For instance, if I have a grid that is allowing someone to display all the orders in our system, we might have say 20,000+ orders in our active database. Just say a customer service reps thinks it would be interesting to see all the orders grouped by shipping type (one of the columns in the grid). We probably have maybe 10 different shipping types, so all the items would end up displaying on the first page of the grid. BUT, due to the grouping each of those 10 or so shipping types would have the bulk of those 20,000 orders attached! Not only would this page be pretty unusable, but it would also be ridiculously slow and would bog down the servers if someone did attempt to do it (I am sure the help desk would get a call asking why the page got stuck). This would be be a whole lot worse if the grid is allowing the display of orders from the archives, where there might be well over half a million items.

So it seems using grouping in practice on a grid is probably not very useful at all except maybe in a few canned situations, where the data set is small?

At this point I think I will just turn off grouping in our grids and not use that 'feature'.

[Rosen]

Hello Kendall Bennett,

Generally speaking the paging should be applied before the grouping and this same paged data should be used in the grouping operation. In order to maintain the correct order data should be sorted on the same field on which will be grouped before it is paged. However, as custom binding is used instead of grid built-in functionality, you are in charge on building the result set and you should feel free to take different approach if it matches more closely to your requirements.

Greetings,
Rosen
the Telerik team

[Kendall Bennett]

I can see that I might be able to hack up something to not bring in silly amounts of data if I use a custom binding, but if I am using a regular binding and passing in a LINQ queryable from say OpenAccess ORM, it would blow up exactly as I mentioned above.

[Rosen]

Hi Kendall Bennett,

I'm afraid that I'm not sure what you mean by  "would blow up" as you did not mention such behavior to this point.

All the best,
Rosen
the Telerik team

[Kendall Bennett]

I thought that was pretty obvious? Specifically, if I have a grid that is paging through a lot of records (say customer orders, and we have a total of 100,000 of them), and one of the fields is the shipping type, we have a major problem. If the user decides to group on the shipping type, and say there are 10 different shipping types, ALL of the grouped data is going to end up in the grid without paging. Which means as soon as the user does this, the site blows up because the query is going to pull in ALL 100,000 records from the database. Not only is this going to generate a SQL query that will take a long time, it will eat up a TON of memory on the server and will produce a JSON packet (assuming the grid is using AJAX) that is absolutely massive.

The end result is that the user will perform the operation, and then call the help desk after about 5 minutes when the page has still not loaded.

That is what I mean by blowing up.

[Charley]

Kendall

I had some problems sending over large amounts of JSON through MVC before.  I had to increase the MaxJSONLength of the serializer.  You may also want to make sure your service can send large data through it as well.  I am not quite sure if this is a help but in case there you go.

[Rosen]

Hello Kendall,

As I have mentioned maybe you have not implement you custom grouping to be efficient enough.
Also if you using the built-in functionality you and using IQueryable implementation you should  make sure it is not being resolved before it is populated to the grid.

Regards,
Rosen
the Telerik team