Introduction to QueryExpression
I walked through the fundamentals of using a QueryExpression in a previous post titled Microsoft.Xrm.Sdk.Query.QueryExpression in Depth and want to take it a little further now. I had mentioned that there are some pretty heated debates on which methodology (FetchXml vs QueryExpression) one should use and why. I’ll say in advance that most of the true reasons people get so passionate has more to do with personal preference than anything else. That’s not to say there aren’t valid points, but most of the discussions I hear have a lot more to do with personal preference than solid facts.
With that said, I’d like to introduce two messages that more or less negate the very basis of much of these debates.
QueryExpressionToFetchXmlRequest | QueryExpressionToFetchXmlResponse
The first, following the Request/Response pattern used throughout the CRM API, are the QueryExpressionToFetchXmlRequest and the QueryExpressionToFetchXmlResponse messages. To illustrate how this works, we’ll use the last QueryExpression we used which included two LinkEntity items:
QueryExpression query = new QueryExpression(MarketingListConstants.MarketingListEntityName);
// Avoid using true, only done here for illustration
// call out the columns you want to use query.ColumnSet = new ColumnSet(true); query.Criteria.AddFilter(PrimaryFilter); LinkEntity BuildingLink = new LinkEntity(); BuildingLink.LinkFromEntityName = "contact"; //use constants instead BuildingLink.LinkToEntityName = "building"; BuildingLink.LinkFromAttributeName = "contactid"; BuildingLink.LinkToEntityName = "buildingownerid"; BuildingLink.JoinOperator = JoinOperator.Inner; ConditionExpression SquareFeetCondition = new ConditionExpression(); SquareFeetCondition.AttributeName = "squarefeet"; SquareFeetCondition.Operator = ConditionOperator.GreaterEqual; SquareFeetCondition.Values.Add(10000); BuildingLink.LinkCriteria.AddCondition(SquareFeetCondition); query.LinkEntities.Add(BuildingLink); LinkEntity OfficeLink = new LinkEntity(); OfficeLink.LinkFromEntityName = "building"; OfficeLink.LinkFromAttributeName = "buildingid"; OfficeLink.LinkToEntityName = "office"; OfficeLink.LinkToAttributeName = "buildingid"; OfficeLink.JoinOperator = JoinOperator.Inner; ConditionExpression SecuritySystemCondition = new ConditionExpression(); SecuritySystemCondition.AttributeName = "securitysystem"; SecuritySystemCondition.Operator = ConditionOperator.Equal; SecuritySystemCondition.Values.Add(true); OfficeLink.LinkCriteria.AddCondition(SecuritySystemCondition); BuildingLink.LinkEntities.Add(OfficeLink); QueryExpressionToFetchXmlRequest expression = new QueryExpressionToFetchXmlRequest(); expression.Query = query; QueryExpressionToFetchXmlResponse QueryId = (QueryExpressionToFetchXmlResponse)OrgServiceInstance.Execute(expression); Console.WriteLine(QueryId.FetchXml);
What this code is doing is building a QueryExpression, then converting it to FetchXml using the QueryExpressionToFetchXmlRequest message. You can get the actual FetchXml by referencing the FetchXml property of the QueryExpressionToFetchXmlResponse once it’s been executed.
One really nice feature of Dynamics CRM 2011 is that you can easily download the FetchXml used to create any given Advanced Find query for instance as shown below:
Once you have the Advanced Find Query set up as you’d like it, you can select the Download FetchXml button and it’ll download an xml file for you containing the FetchXml. While this isn’t the exact same query that was created above with the QueryExpression and LinkEntity ‘s (I don’t have those entities set up on this particular instance i’m using right now), it would work the same way. Once you extract the file information you’d get the following:-
<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false"> <entity name="contact"> <attribute name="fullname" /> <attribute name="telephone1" /> <attribute name="contactid" /> <order attribute="fullname" descending="false" /> <filter type="and"> <condition attribute="address1_city" operator="eq" value="Duncan" /> <condition attribute="emailaddress1" operator="eq" value="firstname.lastname@example.org" /> </filter> </entity> </fetch>
-To convert it into a QueryExpression, you’d just need to create a FetchXmlToQueryExpressionRequest, set the FetchXml property to the FetchXml you want to convert (which is a string – the one thing you’ll need to do is either escape the double quotes, or convert them to single quotes which is very easy to do with Find/Replace) and call the Execute method of the OrganizationService. To actually get the QueryExpression you’ll need the FetchXmlToQueryExpressionResponse so you can reference its Query property. Using the FetchXml shown above the code to accomplish this is shown below:
FetchXmlToQueryExpressionRequest ContactFetchQuery = new FetchXmlToQueryExpressionRequest(); fetch.FetchXml = "<fetch version='1.0' output-format='xml-platform' mapping='logical' distinct='false'> <entity name='contact'> <attribute name='fullname' /> <attribute name='telephone1' /> <attribute name='contactid' /> <order attribute='fullname' descending='false' /> <filter type='and'> <condition attribute='address1_city' operator='eq' value='Duncan' /> <condition attribute='emailaddress1' operator='eq' email@example.com' /> </filter> </entity> </fetch>"; FetchXmlToQueryExpressionResponse FetchResponse = OrgServiceInstance.Execute(fetch) as FetchXmlToQueryExpressionResponse;
Now you’d just need to reference the Query property and you’d have a fully functional QueryExpression. This may not seem all that earth shattering, but it’s immensely helpful when you know how to build a given query in one format but need it in another and are having trouble. It’s also very useful to do as you’re learning to build both types of queries since this is what the CRM API is actually using to do the conversions.
Leave any questions in the Comments section, or write me directly: firstname.lastname@example.org – /b/ill
KeyWords: Microsoft.Xrm.Sdk.Entity, Crm 2011, Dynamics CRM 2011, RelatedEntities, LinkEntity, LinkEntities, CrmConnection, OrganizationService, FilterExpression, QueryExpression, Criteria, ,ColumnSet, FetchXmlToQueryExpressionResponse , FetchXmlToQueryExpressionRequest , QueryExpressionToFetchXmlRequest , QueryExpressionToFetchXmlResponse, FetchXml, Dynamics4, Dynamics4.com, William Ryan , Bill Ryan