Searching, Querying, Fetching Data in Dynamics 365

In Microsoft Dynamics 365 CRM, efficiently retrieving and manipulating data is critical for developers working on custom solutions, integrations, or extensions. Dynamics 365 offers multiple approaches for searching, querying, and fetching data, each with its own strengths, limitations, and specific use cases. Selecting the right method can greatly affect your solution's performance, maintainability, and scalability. 

 In this article, we will examine the following key data retrieval methods:  

• FetchXML, a query language using XML syntax that supports complex queries, aggregation, and joins between entities, explicitly designed for Dynamics 365.  
• QueryExpression, a programmatic and object-oriented way to define queries, offering flexibility and control over data retrieval operations.  
• QueryByAttribute, a simpler query approach designed to fetch records by specifying attribute-value pairs, ideal for straightforward queries without complex logic.  
• LINQ (Language Integrated Query), a powerful, readable approach that allows developers to use C# syntax to perform queries, providing an intuitive way to work with Dynamics data.  
• WebApi (OData v4), a RESTful API approach for querying data using web services, primarily utilized in integrations and client-side operations.  
• Direct SQL Access, a group of methods, such as Data Export Service or Azure Synapse Link, allowing direct querying of Dynamics data in SQL format, offering high-performance reads for analytics.  

In addition to the current best practices, we will also pay attention to the deprecated querying methods that are no longer recommended for use:  

• Organization Data Service (OData v2)  

It is an older RESTful API for querying Dynamics data that has been replaced by the Web API (OData v4) due to limited capabilities and security concerns.  

• SOAP Endpoint (2011 Organization Service)  

Once the primary service endpoint for server-side code, this SOAP-based approach has been deprecated in favor of the more modern Web API.  

• Filtered Views in SQL (on-premises only)  

While not officially deprecated, using SQL-filtered views directly from the Dynamics database is discouraged due to bypassing security and business logic layers.  

• ADFS Secured SOAP Services  

These are earlier versions of Dynamics CRM that used ADFS to secure SOAP-based services. This approach is now considered outdated with the transition to OAuth2.0 and the Web API.  

By comparing these approaches, including their advantages, limitations, and optimal use cases, this article will guide you in selecting the most effective tools for your specific Dynamics 365 CRM development needs while offering insights on transitioning away from deprecated methods.

FetchXML

FetchXML is a proprietary XML-based query language specifically designed for Microsoft Dynamics 365 CRM. It provides a flexible way to define queries, including complex filtering, sorting, aggregations, and joins between related entities. FetchXML is commonly used in custom workflows, plugins, and reports, particularly when integrating with SQL Server Reporting Services (SSRS).

Advantages

This query language supports complex joins, aggregations, subqueries, and detailed data manipulation, making it a powerful tool for server-side operations and report generation. It works in both online and on-premises environments, ensuring consistency in custom reports and facilitating smoother transitions between models. Moreover, FetchXML can be seamlessly integrated into custom workflows and plugins, allowing advanced querying within server-side logic.

Limitations

Nevertheless, the language possesses several limitations. FetchXML’s syntax can be less readable and more cumbersome to maintain compared to other methods like LINQ or QueryExpression. Besides, FetchXML may exhibit slower performance in complex scenarios involving large datasets, particularly when compared to SQL-based queries in on-premises environments. The XML format of the language lacks the development support and real-time validation found in more programmatic approaches, making debugging and error-checking more challenging.

The Best Ways of Usage

The ideal use cases of FetchXML are custom reports in online deployments, as it is the only supported approach for custom SSRS reports in Dynamics 365 online, and advanced data retrieval in plugins and workflows, where it is suitable for scenarios that require detailed data querying, filtering, and manipulation.

Availability

It is the only supported option for building custom SSRS reports due to the architectural constraints of the cloud environment, where direct SQL access is unavailable. All data retrieval for reports must be conducted through FetchXML. Both FetchXML and SQL-based queries are supported in SSRS reports.

However, while SQL-based reports provide direct data access and potentially better performance, FetchXML is recommended if a future migration to the cloud is anticipated, ensuring compatibility across deployment models.

QueryExpression

QueryExpression is a programmatic, object-oriented approach to querying data in Microsoft Dynamics 365 CRM. It allows developers to build queries using code, defining filters, conditions, joins, sorting, and grouping through a structured syntax.

It is widely used in server-side code, such as plugins, custom workflows, and integrations, due to its flexibility and the strong type of support it provides.

Advantages

QueryExpression makes it readable, maintainable, and easier to construct dynamically compared to XML-based queries like FetchXML. It offers compile-time checking and integration with Visual Studio, reducing errors and improving developer productivity. QueryExpression also provides extensive capabilities for building complex queries, including advanced filtering, joining multiple entities, and sorting data in various ways. In addition, it is well-suited for use within server-side codes, such as plugins and custom workflows, where queries need to be built dynamically or modified based on runtime conditions.

Limits

However, it also has some limitations. While QueryExpression supplies a structured and flexible approach, it can be less performant than direct SQL access available in on-premises environments, particularly for very complex queries or large datasets. The syntax can become lengthy and challenging to manage for queries involving complex joins or nested logic compared to other methods like LINQ. Finally, unlike FetchXML, QueryExpression cannot be directly used in SSRS reports, which limits its applicability for reporting scenarios.

The Best Ways of Usage

QueryExpression is perfect for use in plugins, custom workflows, and any server-side code where query flexibility and strong type-checking are beneficial. It also works well in both Online and on-premises deployments, making it a robust choice for solutions that need to run across different Dynamics 365 environments. It is really suitable for scenarios where query parameters need to be modified at runtime, such as user-driven filtering or complex business logic.

Availability

In contrast to the on-premises version, QueryExpression's online version is fully supported, providing consistent querying capabilities regardless of the deployment model. This makes it an excellent choice for developers who need cross-compatibility between cloud and on-premises versions of Dynamics 365.

QueryByAttribute

QueryByAttribute is a straightforward and simplified method for querying data in Dynamics 365 CRM. It allows developers to search for records by specifying attribute-value pairs without needing complex filters or joins. This touch is especially helpful when you require to execute simple queries, such as finding records based on a few specific field values.

Advantages

It is one of the easiest ways to query data, with a simple setup that only requires specifying the entity, attributes, and values to filter by. This makes it quick to implement for straightforward queries. The approach is intuitive, with a clear syntax that is easy to read, understand, and maintain, reducing the complexity often associated with more sophisticated querying methods. Furthermore, it can be very efficient for basic filtering, as it does not involve the overhead of parsing complex query structures.

Limits

On the other hand, QueryByAttribute lacks support for advanced filtering, joins, aggregations, and complex conditions. It is not suitable for scenarios requiring detailed data manipulation or querying multiple related entities. Besides, differently from QueryExpression or FetchXML, QueryByAttribute does not inherently support sorting, limiting its use when order-specific data retrieval is necessary.

The Best Ways of Usage

QueryByAttribute is best used when a straightforward query with a few attribute conditions is needed, such as retrieving records based on unique identifiers or basic attribute matches. QueryByAttribute is also perfect for rapid prototyping or scenarios where quick data access is required without building complex query structures. Moreover, it works well in scenarios where querying data from a single entity is sufficient, and no relationships need to be traversed.

Availability

It is completely supported in Dynamics 365 online and on-premises environments, making it a reliable option regardless of the deployment model.

LINQ (Language Integrated Query)

Language Integrated Query provides compile-time checking and full IntelliSense support in Visual Studio, helping developers catch errors early and speeding up the development process. In addition, LINQ allows developers to use familiar C# constructs like lambda expressions, making it easy to build dynamic queries based on runtime conditions or user inputs. Plus, since LINQ is translated to QueryExpression, many optimizations are handled automatically, allowing developers to focus on writing clear and concise code. It has some limitations, though.  

WebApi (OData v4)  

OData (Open Data Protocol) is a RESTful API standard used for accessing and querying data in Dynamics 365 CRM. With OData, developers can interact with Dynamics data using standard HTTP requests, allowing CRUD (Create, Read, Update, Delete) operations via web services.  

The current implementation in Dynamics 365 uses OData v4, also known as the Web API, which provides robust querying capabilities, including filtering, sorting, and navigating relationships between entities. OData uses standard HTTP methods (GET, POST, PUT, DELETE) and JSON or XML for data exchange, making it easily accessible for web applications, mobile apps, and integrations with other systems.  

Advantages 

It offers advanced querying options such as filtering, sorting, pagination, and navigating relationships, allowing developers to perform sophisticated data retrieval operations. 

OData supports modern authentication mechanisms, including OAuth 2.0, ensuring secure access to data, which is particularly important for cloud environments. In addition, its RESTful nature makes it highly suitable for integration with external systems, enabling seamless data access across different platforms.  

Limits 

It has a few limitations, though. Being flexible, OData queries can be less performant than other server-side approaches, especially when dealing with large datasets or complex queries involving multiple relationships. Although powerful for integrations and client-side applications, OData is generally less commonly used within server-side Dynamics CRM code like plugins and workflows, where other approaches like QueryExpression or FetchXML are preferred. Finally, the syntax for advanced filtering and joining can become intricate, and performance tuning may require additional effort compared to other methods.  

The Best Ways of Usage 

It is perfect for scenarios requiring access to Dynamics 365 data from external applications, including web, mobile, and cloud services. It is frequently used in custom web portals where data needs to be fetched dynamically based on user actions. Also, it is great for client-side data fetching within Dynamics forms or custom controls, allowing dynamic interactions without requiring full-page reloads.  

Availability 

OData is totally supported in online and on-premises environments, with consistent functionality across both models. However, it is worth noting that the Web API is especially vital for online environments, as direct SQL access is unavailable, making OData one of the primary ways to interact with data externally.  

Direct SQL Access  

Direct SQL Access allows querying Dynamics 365 CRM data directly from the SQL database. This approach is traditionally available only for on-premises deployments, where developers can access the CRM database using SQL queries, typically through filtered views. Direct SQL Access offers the most control over data retrieval, allowing complex queries, aggregations, and optimizations that are impossible with other Dynamics querying methods.  

Availability 

SQL affords direct and highly optimized access to data, making it the fastest way to perform complex data retrieval, especially for aggregations and large dataset operations. Additionally, SQL supports intricate queries, including complex joins, subqueries, and window functions that are unavailable in other Dynamics querying methods. Plus, the ability to use SQL queries makes integration with Business Intelligence (BI) tools like Power BI, SSRS, and third-party reporting systems straightforward, especially for on-premises deployments.  

Limits

However, its use has a range of limitations. Direct SQL access is restricted to on-premises environments and not available for Dynamics 365 online, making it unsuitable for cloud-based Dynamics 365 instances. SQL queries bypass the built-in security model of Dynamics, potentially exposing sensitive data or violating business rules if not carefully managed. Direct access to the database schema means that SQL-based solutions can break with Dynamics updates, as Microsoft may change the underlying data structure without notice.  

The Best Ways of Usage 

SQL is perfect for advanced reporting needs that require complex calculations or aggregations beyond what FetchXML can provide. It is commonly used in data export scenarios where large amounts of data need to be extracted for ETL (Extract, Transform, Load) operations or data warehousing. It is also suited for older, on-premises systems that have not transitioned to cloud-based or API-based data access approaches.  

It allows the export of Dynamics 365 data to Azure SQL Database, making it quarriable using SQL tools, thus providing an indirect way to access data via SQL in online environments. In addition, it enables near real-time replication of Dynamics 365 data to Azure Synapse Analytics, offering a scalable way to perform advanced analytics and reporting using SQL queries.  

Availability 

Direct SQL access is unavailable in online environments due to security, scalability, and architectural limitations. Instead, data must be accessed through supported APIs like OData or FetchXML. SQL queries, including those using filtered views, are supported in Dynamics 365 on-premises versions, giving CRM data the ability to be accessed directly from the database. Although Microsoft discourages this approach in favor of APIs to maintain compatibility with future updates.

Organization Data Service (OData v2)

Organization Data Service, based on OData v2, was an early RESTful endpoint for querying Dynamics CRM data. This service allows developers to interact with Dynamics data using standard web protocols and was particularly popular for its ease of use in client-side code and external integrations.  

Advantages 

It offers a straightforward way to query data using RESTful web requests, making it accessible for developers familiar with web technologies, and allows complete CRUD operations, making it versatile for various data manipulation needs.  

Limits 

It is less powerful than the current Web API, with limited support for complex queries, joins, and other advanced data retrieval functions. OData v2 may also cause performance bottlenecks and security vulnerabilities, which have been addressed in the newer Web API.  

Usage 

It is usually found in older Dynamics CRM implementations, particularly where quick integrations are needed. Besides, OData v2 was historically used in JavaScript and other client-side applications before the introduction of the Web API.  

Availability   

OData v2 is officially deprecated, with no support in newer Dynamics versions, making it unreliable for future-proof solutions. 

SOAP Endpoint (2011 Organization Service)  

SOAP Endpoint, also known as the 2011 Organization Service, was the primary service for querying and manipulating data in earlier versions of Dynamics CRM. This endpoint used SOAP (Simple Object Access Protocol) for communication, making it robust but more complex than modern RESTful APIs.  

Advantages 

SOAP Endpoint provides extensive access to Dynamics data, including metadata and service operations that were not available in other APIs. It is also well-supported in .NET environments, offering robust type checking and integration with Visual Studio. 

Limits 

Its XML-based format makes it more complex and less readable than JSON-based RESTful services. It is important to keep in mind that SOAP’s verbosity can lead to slower performance, especially in web and mobile applications.    

Usage 

The 2011 Organization Service is found in older server-side code, including custom workflows, plugins, and external integrations where extensive functionality was required. It is often used in on-premises deployments that have not been updated to leverage the Web API. 

Availability 

The SOAP Endpoint is now officially deprecated, with Microsoft recommending the Web API for all new development work.  

Filtered Views in SQL (On-premises Only)

Filtered Views provide read-only access to Dynamics 365 data directly from the SQL database, maintaining Dynamics' security model. These views allow querying data using standard SQL syntax, making them highly useful for reporting and data extraction in on-premises environments.  

Advantages 

Filtered Views in SQL grant powerful querying capabilities directly against the CRM database, ideal for complex data extraction and reporting needs. They also adhere to the Dynamics 365 security model, ensuring data access respects user roles and permissions.  

Limits 

However, direct SQL access bypasses some of the business logic and is vulnerable to changes in the database schema, making it risky for long-term solutions. Moreover, as Microsoft continues to push towards cloud and API-first approaches, relying on Filtered Views may limit the ability to upgrade or migrate to online environments.  

Usage 

The Filtered Views in SQL are frequently used for building SSRS reports and other BI tools that need direct access to data for complex queries. That is why they are commonly found in older on-premises installations where direct access was needed for performance reasons. They are not available online in Dynamics 365, as all data access must be done through supported APIs.  

Availability 

Now, the Filtered Views in SQL are fully supported for read-only queries, but their usage is discouraged in favor of APIs due to potential breaking changes with updates.  

Navigating various methods of querying data in Dynamics 365 CRM can be complex, with each approach offering unique advantages, limitations, and use cases. From modern, entirely supported techniques like FetchXML, QueryExpression, LINQ, and OData, to deprecated legacy methods such as the SOAP Endpoint, Organization Data Service, and Direct SQL Access, understanding these options helps developers choose the most suitable approach for their specific needs.  

Staying informed about the supported and deprecated methods of data querying in Dynamics 365 ensures that your CRM applications remain robust, secure, and scalable. By leveraging the appropriate tools and planning for the future, you can maximize the potential of Dynamics 365 and provide a seamless data experience for your users.