Dynamics CRM Retrieve Multiple and Query Expression

1 Comment

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.

THANKS FOR READING. BEFORE YOU LEAVE, I NEED YOUR HELP.
 

I AM SPENDING MORE TIME THESE DAYS CREATING YOUTUBE VIDEOS TO HELP PEOPLE LEARN THE MICROSOFT POWER PLATFORM.

IF YOU WOULD LIKE TO SEE HOW I BUILD APPS, OR FIND SOMETHING USEFUL READING MY BLOG, I WOULD REALLY APPRECIATE YOU SUBSCRIBING TO MY YOUTUBE CHANNEL.

THANK YOU, AND LET'S KEEP LEARNING TOGETHER.

CARL

https://www.youtube.com/carldesouza

 

ABOUT CARL DE SOUZA

Carl de Souza is a developer and architect focusing on Microsoft Dynamics 365, Power BI, Azure, and AI.

carldesouza.comLinkedIn Twitter | YouTube

 

One Response to Dynamics CRM Retrieve Multiple and Query Expression

  1. Hi Carl,

    I need to build a tool like CRM’s Advance Filter to build dynamic query from another UI.

    I did partially using QueryExpression class, but I need some more info like.. Is it possible or not?

    If u have any code snippet please share with me.

    Thanks,
    Ravikumar Anbarasu.

Leave a Reply

Your email address will not be published. Required fields are marked *