One of the best ways of creating FetchXml query is to use soap logger. It is way better to use stunware’s FetchXml designer if creating query is your objective. It is available in the crm sdk folder in the following folder <SDK folder>\sdk\samplecode\cs\client\soaplogger. when the solution is opened look for the a comments which looks like bellow,
//Add the code you want to test here: //You must use the SoapLoggerOrganizationService 'slos' proxy rather than the IOrganizationService proxy you would normally use.
Add your code underneath the lines compile and run the program. if you are running the soap logger solution for the first time then the system will take you through the registration process. once the registration process
it will create output.txt in the bin directory which will have the soap action and fetchxml command with it. here's one example that i have created which gets some account contact information from crm,
//Add the code you want to test here: // You must use the SoapLoggerOrganizationService 'slos' proxy rather than the IOrganizationService proxy you would normally use.
QueryExpression qe = new QueryExpression();
qe.EntityName = "account";
qe.ColumnSet = new ColumnSet();
qe.ColumnSet.Columns.Add("name");
qe.Criteria.AddCondition("accountid", ConditionOperator.Equal, "19C5970E-ABBC-E211-B7AD-3C4A92DBD85C");
qe.LinkEntities.Add(new LinkEntity("account", "contact", "primarycontactid", "contactid", JoinOperator.Inner));
qe.LinkEntities[0].Columns.AddColumns("firstname", "lastname");
qe.LinkEntities[0].EntityAlias = "primarycontact";
slos.RetrieveMultiple(qe);
And here's output of the query in the file. HTTP REQUEST
--------------------------------------------------
POST https://serveraddress/XRMServices/2011/Organization.svc/web
Content-Type: text/xml; charset=utf-8
SOAPAction: http://schemas.microsoft.com/xrm/2011/Contracts/Services/IOrganizationService/RetrieveMultiple
<s:Envelope xmlns:s="http://schemas.xmlsoap.org/soap/envelope/">
<s:Body>
<RetrieveMultiple xmlns="http://schemas.microsoft.com/xrm/2011/Contracts/Services" xmlns:i="http://www.w3.org/2001/XMLSchema-instance">
<query i:type="a:QueryExpression" xmlns:a="http://schemas.microsoft.com/xrm/2011/Contracts">
<a:ColumnSet>
<a:AllColumns>false</a:AllColumns>
<a:Columns xmlns:b="http://schemas.microsoft.com/2003/10/Serialization/Arrays">
<b:string>name</b:string>
</a:Columns>
</a:ColumnSet>
<a:Criteria>
<a:Conditions>
<a:ConditionExpression>
<a:AttributeName>accountid</a:AttributeName>
<a:Operator>Equal</a:Operator>
<a:Values xmlns:b="http://schemas.microsoft.com/2003/10/Serialization/Arrays">
<b:anyType i:type="c:string" xmlns:c="http://www.w3.org/2001/XMLSchema">19C5970E-ABBC-E211-B7AD-3C4A92DBD85C</b:anyType>
</a:Values>
</a:ConditionExpression>
</a:Conditions>
<a:FilterOperator>And</a:FilterOperator>
<a:Filters />
</a:Criteria>
<a:Distinct>false</a:Distinct>
<a:EntityName>account</a:EntityName>
<a:LinkEntities>
<a:LinkEntity>
<a:Columns>
<a:AllColumns>false</a:AllColumns>
<a:Columns xmlns:b="http://schemas.microsoft.com/2003/10/Serialization/Arrays">
<b:string>firstname</b:string>
<b:string>lastname</b:string>
</a:Columns>
</a:Columns>
<a:EntityAlias>primarycontact</a:EntityAlias>
<a:JoinOperator>Inner</a:JoinOperator>
<a:LinkCriteria>
<a:Conditions />
<a:FilterOperator>And</a:FilterOperator>
<a:Filters />
</a:LinkCriteria>
<a:LinkEntities />
<a:LinkFromAttributeName>primarycontactid</a:LinkFromAttributeName>
<a:LinkFromEntityName>account</a:LinkFromEntityName>
<a:LinkToAttributeName>contactid</a:LinkToAttributeName>
<a:LinkToEntityName>contact</a:LinkToEntityName>
</a:LinkEntity>
</a:LinkEntities>
<a:Orders />
<a:PageInfo>
<a:Count>0</a:Count>
<a:PageNumber>0</a:PageNumber>
<a:PagingCookie i:nil="true" />
<a:ReturnTotalRecordCount>false</a:ReturnTotalRecordCount>
</a:PageInfo>
<a:NoLock>false</a:NoLock>
</query>
</RetrieveMultiple>
</s:Body>
</s:Envelope>
--------------------------------------------------
HTTP RESPONSE
--------------------------------------------------
<s:Envelope xmlns:s="http://schemas.xmlsoap.org/soap/envelope/">
<s:Body>
<RetrieveMultipleResponse xmlns="http://schemas.microsoft.com/xrm/2011/Contracts/Services" xmlns:i="http://www.w3.org/2001/XMLSchema-instance">
<RetrieveMultipleResult xmlns:a="http://schemas.microsoft.com/xrm/2011/Contracts">
<a:Entities>
<a:Entity>
<a:Attributes xmlns:b="http://schemas.datacontract.org/2004/07/System.Collections.Generic">
<a:KeyValuePairOfstringanyType>
<b:key>name</b:key>
<b:value i:type="c:string" xmlns:c="http://www.w3.org/2001/XMLSchema">Adventure Works (sample)</b:value>
</a:KeyValuePairOfstringanyType>
<a:KeyValuePairOfstringanyType>
<b:key>accountid</b:key>
<b:value i:type="c:guid" xmlns:c="http://schemas.microsoft.com/2003/10/Serialization/">19c5970e-abbc-e211-b7ad-3c4a92dbd85c</b:value>
</a:KeyValuePairOfstringanyType>
<a:KeyValuePairOfstringanyType>
<b:key>primarycontact.firstname</b:key>
<b:value i:type="a:AliasedValue">
<a:AttributeLogicalName>firstname</a:AttributeLogicalName>
<a:EntityLogicalName>contact</a:EntityLogicalName>
<a:Value i:type="c:string" xmlns:c="http://www.w3.org/2001/XMLSchema">Nancy</a:Value>
</b:value>
</a:KeyValuePairOfstringanyType>
<a:KeyValuePairOfstringanyType>
<b:key>primarycontact.lastname</b:key>
<b:value i:type="a:AliasedValue">
<a:AttributeLogicalName>lastname</a:AttributeLogicalName>
<a:EntityLogicalName>contact</a:EntityLogicalName>
<a:Value i:type="c:string" xmlns:c="http://www.w3.org/2001/XMLSchema">Anderson (sample)</a:Value>
</b:value>
</a:KeyValuePairOfstringanyType>
</a:Attributes>
<a:EntityState i:nil="true" />
<a:FormattedValues xmlns:b="http://schemas.datacontract.org/2004/07/System.Collections.Generic" />
<a:Id>19c5970e-abbc-e211-b7ad-3c4a92dbd85c</a:Id>
<a:LogicalName>account</a:LogicalName>
<a:RelatedEntities xmlns:b="http://schemas.datacontract.org/2004/07/System.Collections.Generic" />
</a:Entity>
</a:Entities>
<a:EntityName>account</a:EntityName>
<a:MinActiveRowVersion>-1</a:MinActiveRowVersion>
<a:MoreRecords>false</a:MoreRecords>
<a:PagingCookie><cookie page="1"><accountid last="{19C5970E-ABBC-E211-B7AD-3C4A92DBD85C}" first="{19C5970E-ABBC-E211-B7AD-3C4A92DBD85C}" /></cookie></a:PagingCookie>
<a:TotalRecordCount>-1</a:TotalRecordCount>
<a:TotalRecordCountLimitExceeded>false</a:TotalRecordCountLimitExceeded>
</RetrieveMultipleResult>
</RetrieveMultipleResponse>
</s:Body>
</s:Envelope>
--------------------------------------------------
As i have mentioned before there are tools available online which can do all the above and some more. CRM itself has some sdk message that converts fetch xml to query expression and vise versa. One example of it can be found here.
No comments:
Post a Comment