Retrieve & Retrieve Multiple (QueryExpression using linkEntity, FilterExpression)(Logical Operator AND and OR) Methods in Dynamics 365 CRM

1. Retrieve Method

• entityname: Logical name of the entity

• id: GUID of the record to retrieve.

• Columnset: Specifies which columns to retrieve.

Guid accountId = new Guid("D7C72460-3A9E-4D7A-8B6D-ACD6B4693E01");

Entity account = service.Retrieve("account", accountId, new ColumnSet("name", "telephone1"));

string accountName = account.Contains("name") && account["name"] != null ?
                        account.GetAttributeValue<string>("name") : string.Empty;
string phone = account.Contains("telephone1") && account["telephone1"] != null ?
                    account.GetAttributeValue<string>("telephone1") : string.Empty;

2. Retrieve Multiple Method

1. Query Expression using ConditionalExpression (AND operator for all conditions)

   Query Expression is used to build complex queries to retrieve multiple records. By default, Query Expression with Conditional Expression works with AND operator.

string fullname = string.Empty;
string emailaddress1 = string.Empty;

QueryExpression query = new QueryExpression("contact");
query.ColumnSet = new ColumnSet("fullname", "emailaddress1");
query.Criteria.AddCondition(new ConditionExpression("lastname", ConditionOperator.Equal, "Smith"));
query.Orders.Add(new OrderExpression("lastname", OrderType.Ascending));

EntityCollection contacts = service.RetrieveMultiple(query);

2. Query Expression using FetchExpression (FetchXML query)

string lastName = "Smith";
int statecode = 0;

string fetchXml = $@"
    <fetch>
        <entity name='contact'>
            <attribute name='fullname'/>
            <attribute name='emailaddress1'/>
            <filter>
                <condition attribute='lastname' operator='eq' value='{lastName}'/>
                <condition attribute='statecode' operator='eq' value={statecode}/>
            </filter>
            <order attribute='fullname' descending='false'/>
        </entity>
    </fetch>";

FetchExpression fetchExpression = new FetchExpression(fetchXml);

EntityCollection contacts = service.RetrieveMultiple(fetchExpression);

3. QueryExpression using FilterExpression (both AND and OR operators)

QueryExpression query = new QueryExpression("contact");
query.ColumnSet = new ColumnSet("fullname", "emailaddress1");

// Outer filter using AND (default behavior)
FilterExpression outerFilter = new FilterExpression(LogicalOperator.And);

// Inner filter using OR
FilterExpression innerFilter = new FilterExpression(LogicalOperator.Or);
innerFilter.AddCondition(new ConditionExpression("lastname", ConditionOperator.Equal, "Smith"));
innerFilter.AddCondition(new ConditionExpression("firstname", ConditionOperator.Equal, "John")); 

// Adding inner filter (OR) to the outer filter (AND)
outerFilter.AddFilter(innerFilter);

// Additional AND conditions can be added
outerFilter.AddCondition(new ConditionExpression("statecode", ConditionOperator.Equal, 0)); 

// Apply the filter to the query
query.Criteria.AddFilter(outerFilter);

// Order by lastname in ascending order
query.Orders.Add(new OrderExpression("lastname", OrderType.Ascending));

EntityCollection contacts = service.RetrieveMultiple(query);

4. QueryExpression using LinkEntity and ConditionalExpression (both AND and OR operators)

// Define the Quote QueryExpression with proper data types
QueryExpression quoteQuery = new QueryExpression("quote");
quoteQuery ColumnSet = new ColumnSet("quoteName");

// Adding conditions to the "quote" entity (main entity)
quoteQuery.Criteria.AddCondition(new ConditionExpression("opportunityid", ConditionOperator.Equal, opportunityId));
quoteQuery.Criteria.AddCondition(new ConditionExpression("statecode", ConditionOperator.Equal, false));

// Adding a LinkEntity to another entity ("entity2") with conditions
LinkEntity link = quoteQuery.AddLink("entity2", "entity2id", "quoteid", JoinOperator.Inner);

// Specify which columns to retrieve from the linked entity
link.Columns = new ColumnSet("name", "number");
link.EntityAlias = "cont";

// Create the filter with OR conditions for the linked entity
FilterExpression filter = new FilterExpression(LogicalOperator.Or);

// Example array of conditions (replace arrayOfExps with your actual conditions)
ConditionExpression[] arrayOfExps = new ConditionExpression[]
{
    new ConditionExpression("status", ConditionOperator.Equal, "Active"),
    new ConditionExpression("type", ConditionOperator.Equal, "Proposal")
};

// Adding the conditions to the filter
filter.Conditions.AddRange(arrayOfExps);

// Applying the filter to the link criteria
link.LinkCriteria.AddFilter(filter);

// Optionally, add other conditions or orders for the main entity (quote)
quoteQuery.Orders.Add(new OrderExpression("name", OrderType.Ascending));

// Retrieve the results
EntityCollection result = service.RetrieveMultiple(quoteQuery);
[]
// Output results
if (result?.Entities?.Count > 0)
{
    foreach (Entity entity in result.Entities)
    {
        // Access quote entity values
        string quoteName = entity.Contains("quoteName") && entity["quoteName"] != null
                    ? entity.GetAttributeValue<string>("quoteName").Value : string.Empty;

        string linkedStatus = entity.Contains("cont.name") && entity["cont.name"] != null
                    ? entity.GetAttributeValue<AliasedValue>("cont.name")?.Value.ToString() : string.Empty;
    }
}

5. QueryByAttribute

   QueryByAttribute is a simplified alternative to QueryExpression when filtering based on a single or few attributes

string fullname = string.Empty;
string emailaddress1 = string.Empty;

QueryByAttribute query = new QueryByAttribute("contact");
query.ColumnSet = new ColumnSet("fullname", "emailaddress1");
query.Attributes = new List<string> { "lastname" };
query.Values = new List<object> { "Smith" };

EntityCollection contacts = service.RetrieveMultiple(query);

---

Differences Between QueryExpression & QueryByAttribute

---

EntityCollection Null handling

if (contacts?.Entities?.Count > 0)
{
    foreach (var contact in contacts.Entities)
    {
        fullname = contact.Contains("fullname") && contact["fullname"] != null ?
                        contact.GetAttributeValue<string>("fullname") : string.Empty;
        emailaddress1 = contact.Contains("emailaddress1") && contact["emailaddress1"] != null ?
                            contact.GetAttributeValue<string>("emailaddress1") : string.Empty;
    }
}