Dynamics CRM in the Field

Information from the Microsoft Dynamics CRM PFE team working in the field

Dynamics CRM 2011 SDK Query Limitations by API

Dynamics CRM 2011 SDK Query Limitations by API

Rate This
  • Comments 3

*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

FetchXML

QueryExpression

LINQ*

OData**

LEFT OUTER JOIN

X

GROUP BY

X

X

X

Aggregate Functions (COUNT, SUM, AVG, MAX, MIN)

X

X

X

Statistical Aggregates (STDEV, VAR, etc.)

X

X

X

X

FULL OUTER JOINs

X

X

X

X

RIGHT OUTER JOINs

X

X

X

X

Composite JOINs

X

X

X

X

Strictly Non-equi JOINs

X

X

X

X

NOT EXISTS/IN [sub-query]

X

X

X

X

Combining Multiple Result Sets (UNION, INTERSECT, EXCEPT)

X

X

X

X

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

Austin Jones

Microsoft Premier Field Engineer

  • Could you explain why the QueryExpression and Linq APIs where written so that they didn't implement the full FetchXML API?

    This seems completely counter-intuitive to me and smacks of Microsoft making yet another half-hearted attempt at creating developer APIs.

  • I agree with "Anyone still avoiding this approach based on experience with the CRM 4.0 SDK should reevaluate it for CRM 2011". I recently started to use FetchXML instead of query expressions and now I use it for every retrieve request.

  • @AdrianR, our team doesn't have direct visibility into exactly why QE & Linq are implemented as they are in CRM 2011, but given that many features compete for priority in major releases I know many different options were considered. Our PFE team is providing feedback to the CRM product R&D team to see if we can help shed light on feature priorities as well as explain how more parity between the various query mechanisms will help developers. On a positive note, all options returning the same type of result giving you choice depending on what your requirements are and allowing you to be flexible as your needs change.  Also, since FetchXML is used as a way to query CRM for CRMOnline reporting you'll find more features in fetch to accommodate both extending CRM as well as report writing - whereas Linq provider and QE are specific to extending. More to come on this topic in the future I'm sure.  If you're attending Convergence this year I would expect to see some of our R&D Team there and it can be a great way to share this feedback personally with that team.  

    Thanks!

    Sean

Page 1 of 1 (3 items)
Leave a Comment
  • Please add 5 and 5 and type the answer here:
  • Post