Fetching data from the CRM database

As we discussed in an earlier topic, we have two direct methods available to get data from CRM: Retrieve and RetrieveMultiple. The Retrieve method only fetches a single record based on the primary key passed to it, but RetrieveMultiple can fetch one or more records based on the query passed to it. To write a query for the RetrieveMultiple method we can use the following different options:

  • QueryByAttribute
  • QueryExpression
  • FetchXML

QueryByAttribute

QueryByAttribute is the simplest way to build your query to fetch data. But it does not provide options to define complex criteria to get data from the CRM. For example, the QueryByAttribute class can't be used to get data based on a conditional operator such as: fetch account where annual revenue > 50000, or something similar. It only checks for the equality operator and fetches data where the specified attribute/attributes match with the specified value/values. While using QueryByAttribute, we can use the following properties:

Name

Description

Example

EntityName

Entity name, which data we want to fetch

 

QueryByAttribute query = new QueryByAttribute ("EntityName");

Use the entity's logical name in the case of late bound or use the entity schema name in the case of early bound

ColumnSet

List the column that we want to retrieve

 

query.ColumnSet.AddColumns("Firstfield1", "secondfield"…);

or

query.ColumnSet=new ColumnSet(new string[]{"fieldname"});

Attributes

Conditional attribute

 

query.Attributes.AddRange("conditionfield");

Values

Conditional attribute value

 

query.Values.AddRange("Valuetocheck");

Orders

Sorting result set in Ascending/order

 

query.OrderType = OrderType.Descending;

PageInfo

Used to set number of pages and the number of records per page that we want to return from the query

 

query.PageInfo = new PagingInfo();
query.PageInfo.Count = 10;
query.PageInfo.PageNumber = 1;

Note

For more details on paging, you can refer to: https://msdn.microsoft.com/en-us/library/gg327917.aspx.

The following is an example of fetching account records based on the country India:

using (OrganizationService crmService = new OrganizationService("OrganizationService"))
  {
    QueryByAttribute queryByAttribute = new QueryByAttribute("account");
    queryByAttribute.ColumnSet = new ColumnSet("name", "address1_country");
    queryByAttribute.Attributes.AddRange("address1_country");
    queryByAttribute.Values.AddRange("US");
    EntityCollection retrieved = crmService.RetrieveMultiple(queryByAttribute);
    Console.WriteLine("Number of Clients from US is {0}", retrieved.Entities.Count);
  }

Tip

The query will only return non-null columns from the list of the columns defined under the column set property.

QueryExpression

The QueryExpression class is used to retrieve multiple data based on complex queries. It gives us the flexibility to define different options to filter our query. The following are the properties that we can use while building our QueryExpression:

Name

Description

Example

EntityName

Used to set the source entity that we want to query

 

QueryExpression query = new QueryExpression("EntityName");

//use entity logical name in the case of late bound or use entity schema name in the case of early bound

ColumnSet

Used to define the set of attributes that we want to fetch

 

query.ColumnSet.AddColumns("Firstfield1", "secondfield");

or

query.ColumnSet=new ColumnSet(new string[]{"fieldname"});

Criteria

Used to define our conditions

 

query.Criteria.AddCondition("Fieldname", ConditionOperator.Equal, "value");

Distinct

Used to work with duplicate records

 

Query.Distinct=true;

LinKEntities

Used to work with related entities

 

query.LinkEntities.Add(new LinkEntity("primary entity", "relatedentity", "lookupfieldonprimaryentity", "primarykeyofrelatedentity", JoinOperator.Inner));

Order

Setting ordering options

 

query.OrderType = OrderType.Descending;

PageInfo

Used to set the number of the pages and the number of records

 

query.PageInfo = new PagingInfo();
query.PageInfo.Count = 10;query.PageInfo.PageNumber = 1;

Now let's say we want to get all the accounts from CRM created in the last 30 minutes. The following sample code does this:

QueryExpression query = new QueryExpression
  {
    EntityName = "account",
    ColumnSet = new ColumnSet(new string[] { "name", "accountnumber" }),
    Criteria =
      {
        Conditions = 
              {
            new ConditionExpression 
              {
                AttributeName = "createdon",
                Operator = ConditionOperator.OnOrAfter,
                Values={DateTime.UtcNow.AddMinutes(-30)}
              },
              }
      }
  };
EntityCollection _Result = crmService.RetrieveMultiple(query);
  foreach (Entity Acc in _Result.Entities)
    {
      Console.WriteLine(Acc["name"].ToString());
    }
  }

If we want to fetch data using multiple conditions, we can use FilterExpression to combine multiple conditions, as follows:

using (OrganizationService crmService = new OrganizationService("OrganizationService"))
  {
     QueryExpression query = new QueryExpression
       {
         EntityName = "account",
         ColumnSet = new ColumnSet("name", "accountnumber"),
         Criteria =
           {
             FilterOperator = LogicalOperator.Or,
             Conditions = 
               {
                 new ConditionExpression 
                   {
                     AttributeName = "address1_city",
                     Operator = ConditionOperator.Equal,
                     Values={"Delhi"}
                    },
                  new ConditionExpression
                    {
                      AttributeName="accountnumber",
                      Operator=ConditionOperator.NotNull
                    }
             }
         };
       EntityCollection entityCollection = crmService.RetrieveMultiple(query);

In the preceding example we have combined two conditions using FilterExpression; we are comparing these two conditions based on the Or logical operator, so it will return data where either the account city is equal to Delhi or the account number does contain data.

The following is an example of using the link entity in query expressions; in this code we are fetching account data based on the primary contact ID:

Guid _Contactid = new Guid("E8954457-6005-E511-80F4-C4346BADC5F4");
//mention contact id which is used as primary contact in account
using(OrganizationService crmService=new OrganizationService("OrganizationService"))
  {
    QueryExpression _QueryLinkEntity = new QueryExpression();
    _QueryLinkEntity.EntityName = "account";
     QueryLinkEntity.ColumnSet = new ColumnSet(true);
     _QueryLinkEntity.LinkEntities.Add
        (
          new LinkEntity
            {
              LinkFromEntityName = "account",
              LinkToEntityName = "contact",
              LinkToAttributeName = "contactid",
              LinkFromAttributeName = "primarycontactid",
              LinkCriteria = new FilterExpression
               {
                 Conditions =
                   {
            new ConditionExpression("contactid",ConditionOperator.Equal,_Contactid)
                   }
                }
             }
         );
  EntityCollection _LinkResult = crmService.RetrieveMultiple(_QueryLinkEntity);
  foreach (Entity Acc in _LinkResult.Entities)
    {
      Console.WriteLine(Acc["name"].ToString());
    }
  }

FetchXML

Another way of writing our query for QueryExpression is to use the FetchXML language. We can write our FetchXML query string, which is based on the FetchXML schema. All views in CRM internally use FetchXML to store a query for the view data source. All the properties in the QueryExpression class can be used with FetchXML as well. We can write out FetchXML query manually or get it from Advanced Find. Please refer to an earlier chapter for how to get queries from Advanced Find.

Note

We can also use add-ons available in the marketplace to write FetchXML queries—for example: http://fxb.xrmtoolbox.com/.

The following is an example of a FetchXML query to fetch all accounts where the city is Delhi:

string fetchxml = @"<fetch version='1.0' output-format='xml-platform' mapping='logical' distinct='false' aggregate='true'>
<entity name='account'>
//<attribute name='address1_city' aggregate='COUNT' alias='Citycount' />
<filter type='and'>
<condition attribute='address1_city' operator='eq' value='Delhi' />
</filter>
</entity>
</fetch>";
using (OrganizationService crmService = new OrganizationService("OrganizationService"))
       {
         EntityCollection results = crmService.RetrieveMultiple(new FetchExpression(fetchxml));
         if (results.Entities.Count > 0)
           {
             Entity result = results.Entities[0];
             AliasedValue Total = (AliasedValue)result["Citycount"];
             Console.WriteLine("Total City Count=>"+Total.Value);
           }
        }

Note

Please refer to https://msdn.microsoft.com/en-us/library/gg328117.aspx for more query samples.

LINQ

We can also use the LINQ query to fetch data from the CRM. LINQ allows us to write our query in SQL-like syntax; we can query different types of data using LINQ. If you are new to LINQ refer to https://msdn.microsoft.com/en-us/library/bb397926.aspx to learn the basics of LINQ.

The following is the sample code:

using (OrganizationService crmService = new OrganizationService("OrganizationService"))
  {
    HIMBAPDev serviceContext = new HIMBAPDev(crmService);
    var accounts = (from a in serviceContext.AccountSet
    select new Account
    {  Name = a.Name });
    foreach (var account in accounts)
       {  Console.WriteLine(account.Name);}}

While generating early bound proxy classes, we used HIMBAPDev as the name of the service context, so in the preceding code we are passing an OrganizationService object to the service context and we can use that object to query CRM data.

Note

For more sample code for LINQ, please refer to: https://msdn.microsoft.com/en-us/library/gg328028.aspx.

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset
18.119.0.158