Queries

With any custom code that you create, of course you'll need to programmatically query data from Microsoft CRM. Microsoft CRM offers three data retrieval mechanisms: QueryExpression, FetchXML, and filtered views.

QueryExpression Class

Microsoft CRM provides a powerful, typed QueryExpression class. You initialize QueryExpression like all other classes.

 QueryExpression query = new QueryExpression(); 

You then set the entity that you want to query plus any other query parameters required for your search. Table 9-3 lists the main fields (each with their own properties) that are available with this class. For more details on the QueryExpression fields, consult the SDK.

Table 9-3: QueryExpression Fields

Field	Description
ColumnSet	Property that contains an array of columns to return. Use AllColumns() to return all possible fields for an entity. If left null, only the primary key is returned.
Criteria	Contains the filters of your query.
Distinct	Determines whether duplicate records should be returned.
EntityName	Sets the name of the entity to search.
LinkEntities	Joins an entity to other entities.
Orders	Specifies the order of the results.
PageInfo	Sets the number of pages and number of records per page for the result set.

We will now create a sample query that you can execute against the sample database. This query starts by retrieving all Leads created this week. The results should be identical to the Leads Opened This Week view on the Leads grid. We have sorted this grid by the Name (fullname) column, as shown in Figure 9-6.

Figure 9-6: Leads Opened This Week

  // Standard CRM Service Setup  CrmService service = new CrmService();  service.Credentials = System.Net.CredentialCache.DefaultCredentials;  service.Url = "http://<crmserver>/mscrmservices/2006/crmservice.asmx";  try  {   QueryExpression query = new QueryExpression();   // Set the query to retrieve Lead records.   query.EntityName = EntityName.lead.ToString();   // Create a set of columns to return.   ColumnSet cols = new ColumnSet();   cols.Attributes = new string [] {"subject", "fullname", "createdon"};   // Create the ConditionExpression.   ConditionExpression condition = new ConditionExpression();   condition.AttributeName = "createdon";   condition.Operator = ConditionOperator.ThisWeek;   // Builds the filter based on the condition   FilterExpression filter = new FilterExpression();   filter.FilterOperator = LogicalOperator.And;   filter.Conditions = new ConditionExpression[] {condition};   OrderExpression order = new OrderExpression();   order.OrderType = OrderType.Ascending;   order.AttributeName = "fullname";   query.ColumnSet = cols;   query.Criteria = filter;   query.Orders = new OrderExpression[] {order};   // Retrieve the values from Microsoft CRM.   BusinessEntityCollection retrieved = service.RetrieveMultiple(query);   Response.Write("<table>");   for(int i=0; i<retrieved.BusinessEntities.Length; i++)   {    lead leadResult = (lead)retrieved.BusinessEntities[i];    Response.Write("<tr>");    Response.Write("<td style=\"font-family:tahoma;font- size:8pt;\">" + leadResult.fullname.ToString() + "</td>");    Response.Write("<td style=\"font-family:tahoma;font- size:8pt;\">" + leadResult.subject.ToString() + "</td>");    Response.Write("<td style=\"font-family:tahoma;font- size:8pt;\">" + leadResult.createdon.date.ToString() + "</td>");    Response.Write("</tr>");   }   Response.Write("</table>");  }  catch (System.Web.Services.Protocols.SoapException ex)  {   // Handle error.  } 

If you parsed the results in a Web page (shown in Figure 9-7), you would see the same data returned by the grid view.

Figure 9-7: Leads returned from QueryExpression

Tip 

You could also perform simple queries with the QueryByAttribute class. See the SDK for sample code.

FetchXML

If you used the Microsoft CRM 1.x SDK, you're probably familiar with FetchXML. Using the FetchXML syntax, you can create a string containing your query statement. You then pass that string using the common method Fetch.

We generally recommend that you use QueryExpression over FetchXML for better performance, in addition to the fact that the QueryExpression results will be strongly typed. However, the FetchXML option still exists to ease the upgrade path for users of earlier versions of Microsoft CRM.

The following code snippet shows an example of a FetchXML call that uses the Fetch method.

 CrmService service = new CrmService(); service.Credentials = System.Net.CredentialCache.DefaultCredentials; service.Url = "http://<crmserver>/mscrmservices/2006/crmservice.asmx"; // Retrieve the full name of any contact whose first name equals "Alan". string fetch = @"  <fetch mapping=""logical"">   <entity name=""contact"">    <attribute name=""fullname""/>     <filter>      <condition attribute=""firstname"" operator=""eq"" value=""Alan""/>     </filter>   </entity>  </fetch>"; try {  // Retrieve the results.  string result = service.Fetch(fetch); } catch (System.Web.Services.Protocols.SoapException ex) {  // Handle error. } 

The resulting string of this query when run against the Microsoft CRM sample database would be the similar to the following:

 <resultset morerecords='0' paging-cookie='$1$$contact$contactid$1$0$38${688676F1-19D7-46BB- 96AB-5D764BE0180B}$!$contact$contactid$1$0$38${}$'> <result><fullname>Alan Waxman</fullname></result><result><fullname>Alan Brewer</fullname> </result></resultset> 

Filtered Views

In addition to QueryExpression and FetchXML, you can also use SQL filtered views to retrieve data from Microsoft CRM. We introduced filtered views in Chapter 7, "Reporting and Analysis," so we won't review them in detail again. In the context of creating custom code that reads data from Microsoft CRM, you can connect to the filtered view tables in SQL Server directly instead of using the API.

Important 

Accessing data in filtered views is the only case in which your code should ever connect directly to SQL Server. For all other calls, you should use the methods provided in the Microsoft CRM API.

Remember that you must connect to SQL Server by using Windows authentication, not SQL Server authentication, because the views join the systemuser base table based on the domain credentials of the calling user. This requirement prevents you from using a filtered view from a callout or workflow assembly, because the calling context will be the server's system account. Therefore, for those types of assemblies, you should use QueryExpression or FetchXML to retrieve the data that you require.