I recently found myself tediously constructing a QueryExpression based query to retrieve work items that satisfied a complicated set of criteria in our work item tracking system (Microsoft Dynamics CRM.) I had constructing the query in advanced find before attempting the QueryExpression, and so it occurred to me that it would be great to just reuse query I saved from advanced find.
I didn’t want to spend a lot time doing so, however, because the point of the exercise was to save time. I wanted the results of the query’s execution in strong type objects, again, to save time in my coding exercise. Luckily, it turned out to be very easy and so I thought I’d walk you through it.
In our implementation of CRM, I do not have wide access rights, so I worked with a user query (constructed by advanced find, with rights granted by default for lower privileged roles like mine) as opposed to instead of a saved query (prepared in the customization UI, requiring a higher level of privilege.)
Here’s how to do it:
First, navigate to the target entity’s grid, press the advanced find button, construct the target advanced find query using the advanced find UI, and save it.
Now you need to determine the id of the target query, which if you’re running low privileged like me, you’re going to need a trick to determine. It turns out there is an easy trick. Refresh CRM and go back to the entity gird. Select your new query from the “View:” dropdown. Now hit the advanced find button again. Having selected the view prior to navigating to the advanced find UI ensures that the query you just created will be passed in the URL to the advanced find dialog. You can’t see the URL, however, because the dialog is created without the browser controls. To get around this, press Ctrl + N while you have focus on the advanced find dialog. This will open the same URL in a new browser with the browser controls. Now you can see the URL, and you can find your user query id in the “QueryId” query string parameter. A packet sniffer, or tool to walk to IE DOM would also allow you to get the id of the target query, but my method above is much simpler.
Now that you have the user query id, you’re ready to call it from the SDK. The ExecuteByIdUserQueryRequest is almost what we want, but not quite. Advanced find queries are saved as fetch xml, and this API returns a response as a fetch result set analogous to if you would have called the Fetch method with the contents of the query. What we need is to execute the query as a QueryExpression so we can get a strong typed result set. So we’ll use the FetchXmlToQueryExpressionRequest to convert the query before executing it. In summary, we’ll need to retrieve the user query by its id, convert the query’s fetch xml to a query expression, and the execute the query expression. The final solution looks something like this (assuming you have instantiated the proxy as part of class construction and stored it in the member variable _service):
public BusinessEntity ExecuteUserQuery(Guid queryId)
userquery userQuery = (userquery)_service.Retrieve(
EntityName.userquery.ToString(), queryId, new AllColumns());
FetchXmlToQueryExpressionRequest request =
request.FetchXml = userQuery.fetchxml;
FetchXmlToQueryExpressionResponse response =
BusinessEntityCollection items =
That’s all there is to it. Note that this approach is not particularly performant, as there are three round trips made to CRM. Further, whenever calling RetrieveMultiple, the user should specify both a specific column set and paging information, as retrieving extremely large numbers of columns or rows at once is a bad practice. The first issue can be addressed by serializing the resultant query expression to a file or resource, then then deserializing it before calling RetrieveMultiple. The second issue is the result of trying to keep this example brief, please see: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/CrmSdk3_0/htm/v3d0paginginfoclass.asp for more information on using paging and http://msdn.microsoft.com/library/default.asp?url=/library/en-us/CrmSdk3_0/htm/v3d0columnsetbaseclass.asp for more information on using column sets.
I hope you find this useful,