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;
}
}
