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
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
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.