Paging with the Xrm Extensions

Jun 7, 2010 at 5:55 PM

I am attempting to get server-side paging working in a standard ASP.Net grid utilizing a LinqDataSource. I ran a few tests and the Skip and Take LINQ features appear to work correctly with the XrmDataContext object, but Count seems to be implemented in a way that has very poor performance. When Count is called, all records are retrieved and then the count of that result set is utilized, rather than using reccommended method for retrieving CRM query counts using FetchXml aggregates. This performance issue is present when I set the AutoPage feature to true on a LinqDataSource object as well. The LinqDataSource first retrieves the entire data set (Count), then each time a page is accessed, it pulls the data for that page (Skip/Take). Will Count be implemented correctly in a future release? Is there an alternate way to page a grid with the XrmDataContext?

Thanks,

Jeff

Jun 7, 2010 at 6:15 PM
The LINQ query provider translates the queries to QueryExpression, and not FetchXml, and unfortunately, QueryExpression does not have the ability to handle aggregates such as count. There is also a technology misalignment with LINQ and CRM queries. CRM queries deal with pages and page size, where LINQ uses skip and take. You should be able to easily implement a paging model as long as your paging model flows on page boundaries. ie: you always take and skip exact intervals or multiple of it: skip 30 take 10 (30 is a multiple of page size 10 and would tell CRM to query page 4 data). As for how to implement paging in ASP.NET, you can use a standard DataPager control. There are examples of how to do this in Adxstudio's xRM Portals product that you can download from http://community.adxstudio.com/ Check out the customer support site and look at the discussion forum implementation - it uses a data pager. Shan McArthur www.shanmcarthur.net
Jun 7, 2010 at 7:35 PM
Edited Jun 7, 2010 at 8:28 PM

Shan,

Thanks for the quick response. If FetchXml is not an option, another possibility that could cut some of the overhead of retrieving the total row count is removing the ColumnSetBase from the QueryExpression before retrieving the result set for Count operations (which results in retrieval of the identity column only). This could significantly reduce the amount of time needed to retrieve the total record count for a query and would make it a bit more practical to utilize the out-of-the-box paging features of the GridView and LinqDataSource.

For example, there are 10,000 contacts in our development environment. If I call...

int recordCount = xrm.contacts.OrderBy(c => c.fullname).Select(c => c.contactid).Count();

...it takes about 6 seconds for the query to return with an accurate count of 10,000. However, if I call...

int recordCount2 = xrm.contacts.OrderBy(c => c.fullname).Count();

...it takes about 24 seconds to give me the same result of 10,000. 

The main objective that I have is to make data-binding simple. We would like to be able to drop a GridView control onto a page, tie it to a DataSource object (in this case LINQ), and not have to write a single line of code. This makes the page much simpler to maintain, understand, and keeps its implementation consistent with every other GridView that we use, regardless of data source. The LinqDataSource blindly calls the Count method for the underlying query when paging, so enhancing the performance here would address the issue we are experiencing without the need to write any custom code on these grids. I'm not that familiar with the implementation of a LINQ provider, so perhaps you have already considered this and dismissed it due some incompatibility. If not, could you consider utilizing it in a future release? 

I'll take a look at the implementation in the xRM Portals samples and see if that will fit our needs for the time being. 

Thanks, 

Jeff