Microsoft Dynamics CRM Querying Data - QueryByAttribute

Same like QueryExpression, QueryByAttribute is also derived from QueryBase class and is used to fetch CRM data. 

When to use QueryByAttribute?

Let me explain QueryByAttribute logic with the help of SQL Query.

1. When your query is quite simple e.g (Select Name, Age, Salary from Account where Age=20 and Salary = 5000).

2. When you want to get data for single entity say "account", "contact" etc (Inner/Outer Joins not supported) at a time.

3. Does not support OR condition, it only supports AND Condition. e.g. If you want to achieve (Select Name, Age, Salary from Account where Age=20 OR Salary = 5000) which is not possible through QueryByAttribute.

4. Only Equal Operator is supported e.g (Select Name, Age, Salary from Account where Age>20 AND Salary = 5000), Age>20 condition is not be supported in QueryByAttribute.

5. It must have one AttributeValue specified e.g (Select Name, Age, Salary from Account) will throw exception, since there is no where clause specified.

6. Supports Pagination (both backward and forward) and Ordering

Sample Code
Suppose we have a requirement where we have to fetch firstname and lastname of accounts where it belongs to city Redmond and firstname should be in descending order. The same requirement will be written in SQL Query given below.

Select name, accountnumber, address_city from account where address1_country="U.S." order by name desc
The same query logic in QueryByAttribute.

Example: Without Pagination

QueryByAttribute myQuery = new QueryByAttribute("account");

myQuery.ColumnSet = new ColumnSet("name", "accountnumber", "address1_city");

myQuery.AddAttributeValue("address1_country", "U.S.");

myQuery.AddOrder("name", OrderType.Descending);

EntityCollection resultset= orgproxy.RetrieveMultiple(myQuery);

  foreach (Entity acct in resultset.Entities)

      {

Console.WriteLine("AccountName:{0} \t AccountNumber: {1}\t City: {2}", acct.GetAttributeValue<string>("name"), acct.GetAttributeValue<string>("accountnumber"), acct.GetAttributeValue<string>("address1_city"));

       }

Example: Pagination

QueryByAttribute myQuery = new QueryByAttribute("account");

myQuery.ColumnSet = new ColumnSet("name", "accountnumber", "address1_city");

myQuery.AddAttributeValue("address1_country", "U.S.");

myQuery.AddOrder("name", OrderType.Descending);

//Pagination logic

myQuery.PageInfo = new PagingInfo();

myQuery.PageInfo.Count = 3;//Display 3 Records in a page

myQuery.PageInfo.PageNumber = 1;//Starts from page 1           

myQuery.PageInfo.PagingCookie = null;

        

    while (true)

          {

                // Retrieve the page.

                EntityCollection resultset = orgproxy.RetrieveMultiple(myQuery);

                if (resultset.Entities != null)

                {

                    // Retrieve all records from the result set.

                    foreach (Entity acct in resultset.Entities)

                    {

                        Console.WriteLine("AccountName:{0} \t AccountNumber: {1}\t City: {2}", acct.GetAttributeValue<string>("name"), acct.GetAttributeValue<string>("accountnumber"), acct.GetAttributeValue<string>("address1_city"));

                    }

                }            

                // Check for more records, if it returns true.

                if (resultset.MoreRecords)

                {

                    Console.WriteLine("\n****************\nPage number {0}\n****************", myQuery.PageInfo.PageNumber);

                 

                    // Increment the page number to retrieve the next page.

                    myQuery.PageInfo.PageNumber++;

                    // Set the paging cookie to the paging cookie returned from current results.

                    myQuery.PageInfo.PagingCookie = resultset.PagingCookie;

                }

                else

                {

                    // If no more records are in the result nodes, exit the loop.

                    break;

                }

            }