Breaking down the structure of the QueryExpression Class

Author: 
Josh Behl
Category: 
CRM Customization
Microsoft Dynamics CRM

So you are new to CRM development and you are just beginning to use Query Expressions.  When we learn something new it is a common tendency to relate to something we know.  While this can sometimes create inadvertent, mental roadblocks, it is nonetheless a common tendency.  As I was learning how to use the QueryExpression class, I found that what got it to finally “gel” for me was equating the components to a standard SQL query.

Let’s consider the following SQL query:

Select name from FilteredAccount where address1_city=’Fargo’ or address1_city=’Buxton’

As you can see, all we are doing is getting a list of account names that are from Fargo or Buxton. Simple enough, right?  Well, how does this equate to a QueryExpression?

Let’s review the components of using the QueryExpression class:

QueryExpression

Essentially, this is our query. There are various properties we set when we instantiate the class such as:

  •     Entity: Specifies which type of entity will be retrieved.
  •     ColumnSet: Specifies the set of attributes (columns) to retrieve.
  •     Criteria: Specifies complex conditional and logical filter

As we build the other components listed below, they aggregately become our QueryExpression.

ColumnSet

The ColumnSet identifies which fields we want returned when our query is actually ran.

ConditionExpression

The ConditionExpression allows us to filter the results of our query. For example, if I only want to return records that have a specific value, you would use the ConditionExpression to do it.

FilterExpression

The FilterExpression allows you to take one or more condition expression and tie them into the QueryExpression.

How do each of these relate to a SQL statement? Let’s take our previous SQL query and break it into the related components.

If you were to write this code in C#, it would look something like this:

 

//instantiate the QueryExpression

QueryExpression qeAccounts = new QueryExpression();

//Include a ColumnSet to our QueryExpression

qeAccounts.ColumnSet.AddColumns("name","address1_city");

//instantiate our first condition expression for Fargo

ConditionExpression  ceFargo = new ConditionExpression();

ceFargo.AttributeName="address1_city";

ceFargo.Operator =ConditionOperator.Equal;

ceFargo.Values.Add("Fargo");

//instantiate our second ConditionExpression for Buxton

ConditionExpression ceBuxton = new ConditionExpression();

ceBuxton .AttributeName="address1_city";

ceBuxton .Operator =ConditionOperator.Equal;

ceBuxton .Values.Add("Buxton");

//instantiate our instance of a FilterExpression

FilterExpression feFargo = new FilterExpression();

//because we want a list of account from either Fargo or Buxton, we'll need to add an "or"

feFargo.FilterOperator = LogicalOperator.Or;

//next, we'll associate our two ConditionExpression to our FilterExpression

feFargo.Conditions.Add(ceFargo);

feFargo.Conditions.Add(ceBuxton);

//identify which entity our query is actually going to query

qeAccounts.entity = "account";

//link our FilterExpression to the QueryExpression

qe.Criteria = fe;

To run this, you would simply pass the instance of the QueryExpression into the RetrieveMultiple() method and then consume the information from the returned EntityCollection.

/*the _service variable would have been created earlier in your code. It is used to provide programmatic access to the metadata and data for an organization*/

EntityCollection ecAccounts = _service.RetrieveMultiple(qeAccounts);

/*as you can see the RetrieveMultiple() method returns an EntityCollection.

We will loop through that EntityCollection, grabbing each entity instance (record) found

when our query ran*/

foreach (var a in ecAccounts.Entities)

{

//as we look at each record, simply display the name and city in the console

Console.WriteLine("Name: {0}", a.GetAttributeValue<string>("name"));

Console.WriteLine("City: {0}", a.GetAttributeValue<string>("address1_city"));

}

There are technically other ways to do this exact same query using LINQ, FilteredViews, or even FetchXML.  However, the purpose of this article was to introduce and explain this particular type of querying method. We’ll provide other introductory articles of each these other querying methods later.

 

Tags: 
Training
Training Techniques
Dynamics Training
User Training
How do I train
Dynamics CRM Training
Dynamics GP Training