*UPDATE: In the table below, LINQ was incorrectly marked with no support for LEFT OUTER JOIN whereas the provider does support it as documented here. The LINQ column has been updated to correctly reflect support for this type of table join.
The Microsoft Dynamics CRM 2011 SDK provides a flexible array of query API's for retrieving data programmatically. Nevertheless, I continue to encounter misinformation and unfounded affinity for one query API over another, as well as those that favor circumventing the application tier altogether without sufficient justification. While limitations are inherent in each API and circumstances exist that may warrant venturing into unsupported territory, more often than not a data access requirement can be accomplished efficiently via one of the provided API's. After repeated discussions with customers and partners on this subject, I decided it would be beneficial to document a definitive list of specific limitations inherent in each query API to provide clarity on which approach to choose and when to consider alternatives.
While this post intends to focus on capabilities rather than comparing performance, I do have to briefly call out anyone who is making the "We are retrieving data via Filtered Views for performance reasons" argument. If by "performance" you really mean "efficiency" from the perspective of shaping your result sets, then there may be justification given the specific limitations this article intends to identify. Otherwise, this argument holds little merit based on the degree of de-normalization and data transformation pitfalls inherent in the Filtered Views. These characteristics of Filtered Views directly limit your ability to optimize query execution and thus actually present additional performance concerns. Consider too that Filtered Views do not benefit from the recent UR10 query optimizations nor are they available in all deployment scenarios.
That said, let's define each query API's boundaries. In the table below, I've listed common SQL query elements that have not been exposed with an equivalent construct in at least one query API. Any API's that do not implement an equivalent construct have been marked with an 'X' in their column for that particular row.
SQL Query Limitations by API
LEFT OUTER JOIN
Aggregate Functions (COUNT, SUM, AVG, MAX, MIN)
Statistical Aggregates (STDEV, VAR, etc.)
FULL OUTER JOINs
RIGHT OUTER JOINs
Strictly Non-equi JOINs
NOT EXISTS/IN [sub-query]
Combining Multiple Result Sets (UNION, INTERSECT, EXCEPT)
*The LINQ provider requires that the left side of each predicate condition must reference a CRM entity attribute.**OData requests allow you to expand up to 6 entity relationships per query in lieu of table joins. All OData results are limited to pages of 50 records/page.
First, it should be noted that the most capable query API is FetchXML. Anyone still avoiding this approach based on experience with the CRM 4.0 SDK should reevaluate it for CRM 2011. Long gone are the headaches of manually parsing XML results. Rather, FetchXML query requests now return the same EntityCollection that's returned via QueryExpression query. Similarly, anyone that previously avoided QueryExpression should consider that significantly expanded capabilities in the CRM 2011 SDK make it a viable alternative to FetchXML. As you can see in the table above, the only appreciable difference is that FetchXML offers grouping and aggregation support.
The LINQ provider, a new API in the CRM 2011 SDK, abstracts QueryExpression from the developer in favor of the more fluent and familiar LINQ syntax. While it does present a few additional limitations compared to its FetchXML and QueryExpression brethren, it offers a highly efficient and maintainable developer experience that deserves strong consideration.
I generally advise as follows: If unwilling to compromise on standardizing to a single API, then choose FetchXML, it being the most capable. Otherwise, take time to learn all API's and don't hesitate to incorporate a combination in your solution where appropriate.
In addition to the three SOAP-based query API's, CRM 2011 provides a RESTful endpoint that streamlines data retrieval in client-side extensions. This endpoint implements portions of the OData protocol to provide a standardized Uri request pattern. It also returns a serialized JSON response which minimizes response payload size and makes parsing results on the client much easier. For these reasons, I prefer to use this approach in client-side extensions wherever possible.
Finally, if your data access requirement dictates shaping the desired SQL data set in such a way that can't be accomplished in a single programmatic query, querying directly from the Filtered Views may ultimately be your best option.
So, there you have it. A single reference point to use when deciding between query API's or whether to use them at all. For additional perspective, check out a recent post by Sarah Champ contrasting FetchXML vs. Filtered Views in Custom SSRS Reports.
Microsoft Premier Field Engineer