Working with Saved Queries and FetchXML in Dynamics 365: A Guide to Converting and Manipulating Queries

In Dynamics 365, managing saved views and custom queries plays a central role in retrieving data efficiently. To support this, Dynamics 365 provides the SavedQuery and UserQuery tables, designed to store system views and user-specific saved queries, respectively. Understanding these tables and how to work with FetchXML and QueryExpression conversions can significantly enhance query manipulation capabilities within the system. In this post, we’ll dive into what these tables do, how FetchXML and QueryExpression interplay, and the techniques to convert and save queries effectively.

1. Understanding SavedQuery and UserQuery Tables

• SavedQuery Table: This table holds system views related to a specific table (or “entity type”) within Dynamics 365. Each entry in the SavedQuery table represents a predefined view designed for organizational use.
• UserQuery Table: In contrast, the UserQuery table is reserved for user-defined saved queries, offering individual users a personalized way to retrieve data and refine views based on their preferences.

Both tables are essential for users and system administrators, as they streamline data access across the organization and provide tailored data retrieval options at the user level.

2. Why Use FetchXML and QueryExpression?

FetchXML and QueryExpression are the two core query languages for accessing data in Dynamics 365:

• FetchXML: A proprietary XML-based query language, FetchXML is powerful for building complex queries, offering capabilities like joins, filtering, grouping, and ordering. It’s ideal for custom reports, workflows, and views.
• QueryExpression: This object-oriented query language provides a more structured way to work with Dynamics 365 data. QueryExpression is often used for manipulating data within the code due to its compatibility with the object model, making it easier to filter, sort, and manage query results programmatically.

3. Converting FetchXML to QueryExpression and Vice Versa

FetchXML is commonly stored as a string in SavedQuery or UserQuery entries. However, manipulating FetchXML strings directly is challenging, particularly for developers who prefer an object-oriented approach. By converting FetchXML to QueryExpression, you gain the flexibility of working within the Dynamics 365 object model. Here’s why and how to convert:

• Convert FetchXML to QueryExpression: Converting a FetchXML string into a QueryExpression enables direct access to each element of the query, making it easier to modify filters, attributes, and sorting options without editing the XML directly. This is especially useful in plugins, custom workflows, and complex business logic.
• Convert QueryExpression to FetchXML: After modifying the query using QueryExpression, you can convert it back to FetchXML, allowing you to store it in the SavedQuery or UserQuery table as an XML string. This saves the query in a form that Dynamics 365 can natively use in views and dashboards.

4. Practical Use Cases for Conversion and Manipulation

• Enhanced Query Customization: By converting queries to QueryExpression, you can dynamically add filters or change sorting parameters based on user input or specific conditions, tailoring data retrieval for particular scenarios.
• Automating Complex Queries: Using QueryExpression simplifies automating query generation and customization in code. You can, for example, create dynamic views or reports that adjust based on the context.
• Saving and Reusing Customized Queries: After making the necessary modifications, you can convert your QueryExpression back to FetchXML and save it in the UserQuery or SavedQuery tables. This approach enables you to maintain consistency and reuse customized queries within the Dynamics environment.

5. How to Convert FetchXML to QueryExpression

To work with the FetchXML-to-QueryExpression conversion, you can use Dynamics 365 SDK methods such as ConvertFetchXmlToQueryExpression. Here’s a simple example:

// Convert FetchXML string to QueryExpression
string fetchXml = "<fetch mapping='logical'><entity name='account'><attribute name='name'/></entity></fetch>";
QueryExpression queryExpression = service.ConvertFetchXmlToQueryExpression(fetchXml);

After conversion, you have access to the full object model, making it easy to add conditions, change the attributes being selected, or sort the results programmatically.

6. Saving the Query Back as FetchXML

Once the QueryExpression is modified, converting it back to FetchXML is straightforward:

// Convert QueryExpression back to FetchXML
string updatedFetchXml = service.ConvertQueryExpressionToFetchXml(queryExpression);

Now, updatedFetchXml can be stored in the SavedQuery or UserQuery tables, preserving all the changes made through the QueryExpression model.

7. Common Questions

Q: What happens to saved queries when a user is removed?

• Saved user queries in the UserQuery table are associated with a specific user. If a user is removed, any user-specific queries are disabled but may still reside in the table.

Q: Can I retrieve deleted saved queries?

• Deleted queries are generally unrecoverable unless backed up. System views in SavedQuery are typically safeguarded by the system, but user queries in UserQuery may be lost if deleted by the user.

Q: Will changing a saved query impact existing views?

• Any changes made to SavedQuery entries directly affect the system views they represent. UserQuery entries affect only the querying user’s views.

Q: What permissions are required to modify system views?

• Only users with system administration privileges or specific permissions to modify system-level data can make changes to the SavedQuery table.

By leveraging the flexibility of FetchXML and QueryExpression conversions, you can enhance your Dynamics 365 environment’s querying capabilities. This approach empowers administrators and developers to optimize data retrieval, customize views, and streamline the entire process of managing saved queries in Dynamics 365.