AXForum  
Вернуться   AXForum > Microsoft Dynamics CRM > Dynamics CRM: Blogs
All
Забыли пароль?
Зарегистрироваться Правила Справка Пользователи Сообщения за день Поиск Все разделы прочитаны

 
 
Опции темы Поиск в этой теме Опции просмотра
Старый 14.01.2013, 20:11   #1  
Blog bot is offline
Blog bot
Участник
 
25,459 / 846 (79) +++++++
Регистрация: 28.10.2006
crminthefield: Dynamics CRM 2011 SDK Query Limitations by API
Источник: http://blogs.msdn.com/b/crminthefiel...ns-by-api.aspx
==============

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

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





Источник: http://blogs.msdn.com/b/crminthefiel...ns-by-api.aspx
__________________
Расскажите о новых и интересных блогах по Microsoft Dynamics, напишите личное сообщение администратору.
 

Похожие темы
Тема Автор Раздел Ответов Посл. сообщение
crminthefield: Overview: Microsoft Dynamics CRM 4.0 Update Rollup 21 Blog bot Dynamics CRM: Blogs 0 11.02.2012 01:11
crminthefield: Podcast and Overview: Microsoft Dynamics CRM 2011 Update Rollup 4 Blog bot Dynamics CRM: Blogs 0 24.09.2011 01:16
dynamics-community.at: Fertige Microsoft Dynamics CRM 2011 Demos stehen zum Download zur Verfügung Blog bot Dynamics CRM: Blogs 0 21.06.2011 19:11
Microsoft Dynamics CRM Team Blog: Microsoft Dynamics CRM 2011 ~ Book Club Blog bot Dynamics CRM: Blogs 0 02.02.2011 00:16
Все о Microsoft Dynamics CRM: Как установить Microsoft Dynamics CRM 2011 Beta Blog bot Dynamics CRM: Blogs 0 31.10.2010 15:08
Опции темы Поиск в этой теме
Поиск в этой теме:

Расширенный поиск
Опции просмотра

Ваши права в разделе
Вы не можете создавать новые темы
Вы не можете отвечать в темах
Вы не можете прикреплять вложения
Вы не можете редактировать свои сообщения

BB коды Вкл.
Смайлы Вкл.
[IMG] код Вкл.
HTML код Выкл.
Быстрый переход

Рейтинг@Mail.ru
Часовой пояс GMT +3, время: 14:15.
Powered by vBulletin® v3.8.5. Перевод: zCarot
Контактная информация, Реклама.