DYNAMICS CRM RETRIEVE MULTIPLE AND QUERY EXPRESSION

Query Expression is a class used to build queries in Dynamics CRM. Let’s go through some examples of how to use this.

Here are some of our records in CRM:

If we want to return the Alexis Fry contact, we can use the RetrieveMultiple method to do this. It will retrieve all records where the contact name is Alexis Fry, in this case one record. Note if you know the GUID of the record then you can use the Retrieve method, which takes the entity name, guid and columnset as parameters.

To use RetrieveMultiple, first set up our connection to CRM. Add the following DLLs to follow this example:

• Microsoft.Crm.Sdk.Proxy
• Microsoft.Xrm.Sdk
• Microsoft.Xrm.Tooling.Connector

And the namespaces:

using Microsoft.Xrm.Tooling.Connector;
using Microsoft.Xrm.Sdk;
using Microsoft.Crm.Sdk.Messages;
using Microsoft.Xrm.Sdk.Query;

Then add code to connect to CRM:

var connectionString = @"AuthType=Office365;Url=https://yourcrm.crm.dynamics.com/;Username=you@username;Password=yourpassword";
CrmServiceClient conn = new CrmServiceClient(connectionString);

IOrganizationService _orgService;
_orgService = (IOrganizationService)conn.OrganizationWebProxyClient != null ? (IOrganizationService)conn.OrganizationWebProxyClient : (IOrganizationService)conn.OrganizationServiceProxy;

Next, as we are searching for our contact based on the first name and last name, we will add ConditionExpressions and attached these to our FilterExpression, then attached that to our QueryExpression:

ConditionExpression condition1 = new ConditionExpression();
condition1.AttributeName = "lastname";
condition1.Operator = ConditionOperator.Equal;
condition1.Values.Add("Fry");

ConditionExpression condition2 = new ConditionExpression();
condition2.AttributeName = "firstname";
condition2.Operator = ConditionOperator.Equal;
condition2.Values.Add("Alexis");

FilterExpression filter1 = new FilterExpression();
filter1.Conditions.Add(condition1);
filter1.Conditions.Add(condition2);

QueryExpression query = new QueryExpression("contact");
query.ColumnSet.AddColumns("firstname", "lastname");
query.Criteria.AddFilter(filter1);

Finally, we can execute this query using RetrieveMultiple, and then loop through the results of the EntityCollection that is returned:

EntityCollection result1 = _orgService.RetrieveMultiple(query);
foreach (var a in result1.Entities)
        {
             Console.WriteLine("Name: " + a.Attributes["firstname"] + " " + a.Attributes["lastname"]);
        }

In this example, the first, last name and Id are returned:

Another way to do this is to write a procedure that returns an entity collection given some criteria:

        private static EntityCollection GetEntityCollection(IOrganizationService service, string entityName, string attributeName, string attributeValue, ColumnSet cols)
        {
            QueryExpression query = new QueryExpression
            {
                EntityName = entityName,
                ColumnSet = cols,
                Criteria = new FilterExpression
                {
                    Conditions =
                    {
                        new ConditionExpression
                        {
                            AttributeName = attributeName,
                            Operator = ConditionOperator.Equal,
                            Values = { attributeValue }
                        }
                    }
                                    }
                                };
                    return service.RetrieveMultiple(query);
        }

We can then call the code passing in the required attribute:

var contact = GetEntityCollection(_orgService, "contact", "lastname", "Fry", new ColumnSet("contactid", "lastname"));
Guid contactid = (Guid)contact[0].Id;

Once we have the Id of our record we can use that in our code to create, retrieve and update other CRM records.