Odd paging behaviour using EntitySpaces and grouping

[Kieran]

I'm having problems getting my paging working under certain conditions. I'm using EntitySpaces 2009 to access my data and RadControls v 2010.1.309.35 for my front end. My front end features a number of filtering options that require table joins to implement, which results in duplication of records in the resulting data set. My aim was to use grouping to eliminate the duplicate records. For clarification, before adding a group by expression to my query (resulting in duplicate rows), the RadGrid's pager works correctly.

In order to work around SQL Server's design that renders the distinct command useless when used in conjunction with ROW_NUMBER(), I've been forced to add a group by command to my query to remove duplicate records from my result set. At a query level, I am simply grouping by the ID of the records in the result set so as to essentially only return one row per set of duplicates. Doing this breaks the paging in the RadGrid though.

I can see using SQL Server Profiler that EntitySpaces is sending through the right query and is returning the right results, and the count query is returning the expected number to tell the RadGrid about the number of items to expect, however the RadGrid (from a paging perspective) is acting as though there is only one record and not showing the paging controls. I tried setting the PagerStyle's AlwaysVisible to true, and it shows 1 page and says '1 items in 1 pages' in the lower right, despite the fact that it's actually showing the correct number of records for that page. I am able to alter the CurrentPageIndex in the code and it functions as expected (other than not showing any pages in the pager).

Up until this point, I'd assumed it might be a problem with the data source/EntitySpaces, however if I add a GroupByExpression to the RadGrid using the ID of the records, the pager is visible and functions correctly. This means that the RadGrid is receiving the right data, given that the query never changed, but for some reason is not calculating correct paging information.

Just for clarification, from a user interface perspective I do not want grouping functionality. It is only implemented in the back end query in order to filter out duplicate results before those results are passed to the interface for rendering. I have attached screenshots showing the results of my standard grid with the filtered duplicates (behind the scenes group-by), then again with AlwaysVisible set to true, then another showing the RadGrid after adding a GroupByExpression to it. Note that in all of the above, every single piece of code, all of the related queries, and the count query that dictates the total row count for the RadGrid are all exactly the same. All that changes is markup declaring a GroupByExpression.

I don't understand why when presented with the exact same data, the paging is breaking unless I add a GroupByExpression.

Anybody have any ideas?

[Kieran]

Nobody have any ideas why I might be experiencing this?

[Kieran]

I've found some more info on this after replicating the issue again. This time I'm seeing an actual discrepancy in my count query.
Count query results without group by:
1. 21801

Count query results with group by:
1. 3380
2. 3380
3. 507
4. 3887
5. 3380
6. 3887
7. 3380

The RadGrid without a GroupByExpression will use the result from the first set in the count query. So in this case, the RadGrid will claim to have 3380 items when it should really have 7. When I add the GroupByExpression to the RadGrid, it behaves as normal with 7 grouping items and all the right number of children.

[Nikolay Rusev]

Hello Kieran,

I am afraid that I do not completely understand the behavior that you are experiencing. However it seems to me(especially from the screens) that your data layer returns only page size items. Then RadGrid performs grouping. The pager displays only one page as the count of returned items is same or less than PageSize. If this is the case you can use custom paging(set AllowCustomPaging) and you will need to provide RadGrid with all items count(VirtualItemCount) in order to form its pager control properly.

Bellow link demonstrates how to achieve above:
http://demos.telerik.com/aspnet-ajax/grid/examples/programming/custompaging/defaultcs.aspx

Let me know if I am missing something obvious.

Regards,
Nikolay
the Telerik team