When learning to work with Microsoft Dynamics CRM, developer need to get used to fetchXML syntax, query expressions, CRM’s end-points etc. On the other side, majority of .NET developers are very familiar with LINQ since most likely they have met with various LINQ providers throughout their career. That is why Dynamics CRM SDK includes LINQ provider which can be used to query CRM data. Using LINQ for querying data should feel more natural for .NET developers than to use fetchXML or query expressions. Thus, this blog post will present how to build queries against Microsoft Dynamics CRM data using LINQ and it is intended for readers already familiar with LINQ.

LINQ provider is implemented by CrmOrganizationServiceContext class from Microsoft.XRM.Client namespace. There are several ways of creating object of this class, but we will create it by passing organizationService to its constructor. If you are not familiar with the set of required assemblies or with process of setting up organizationService, please refer to this blog post: Working with organization data outside the Dynamics CRM – Part 1. Also, make sure that the assembly “System.Data.Services” is properly referenced.

CrmOrganizationServiceContext context = new CrmOrganizationServiceContext(_orgService);

This context can be used for building queries. Let’s look at very simple example of it:

var accountQuery = from account in context.CreateQuery("account")
                   where account["name"] == "Ajdin's Company"
                   select account;

foreach (Entity account in accountQuery)
{
    Console.WriteLine(account["emailaddress1"]);
}

This part of code will query against CRM data to find all accounts which have the name “Ajdin’s Company” and then it will iterate through every entity and output e-mail address of each of them. You can notice that the query is fairly simple and it looks like standard LINQ expression. Specifying CRM entity/table is done by context’s method “CreateQuery” and by passing entity’s name to it. “Where” clause is created in a way that on the left side of expression is entity’s attribute, specified in a way used when working with late bound types. accountQuery variable implements iQuerable interface, meaning that it can be iterated through in order to access each entity returned as result of query. Entity object can be used in the same ways as if it is retrieved by other means (fetchXML or query expression): updating, deleting or reading its attributes.

By executing previous query, it will return all fields of records which satisfy presented condition under “where” clause. As you probably already know, that has terrible impact on performances and it is strictly recommended to avoid retrieving all fields/columns. One should rather select specific fields of interests and retrieve only those. As you know, selecting of columns is written under “select” clause. In the case of CRM, object of anonymous type should be selected instead of Entity object and properties of that object should be set to CRM fields of specific interest. We will refine previous query to limit the set of fields only to name and e-mail address of retrieved accounts. We will get the set of objects of anonymous type as a result, each of them having to attributes: name and email.

var accountQuery = from account in context.CreateQuery("account")
                   where account["name"] == "Ajdin's Company"
                   select new {
                               Name = account["name"],
                               Email = account["emailaddress1"]
                      };

foreach (var account in accountQuery)
{
     Console.WriteLine(account.Email);
}

We often want to join several tables in the query which is far better idea than to retrieve all records from two tables by two distinct queries and filter and join them by code. LINQ allows construction of queries which would retrieve data from several related entities. It requires usage of “join” clause in which we specify related entity and “on” clause in which we specify entities’ fields on which the relationship is created. “On” clause needs to be formatted in the form: “fieldOfFirstEntity equals fieldOfSecondEntity”. It is important to use word “equals” here. If “==” sign was used, it would cause an error. The result of query is again anonymous object in which we need to specify which fields of both entities that we want. This is again done by “select” clause. We will upgrade previous example, so that it retrieves primary contact of account as well. We will take all fields of that contact, just for a purpose of presentation, even though it is never good practice.

var accountQuery = from account in context.CreateQuery("account")
                   join contact in context.CreateQuery("contact")
                   on account["primarycontactid"] equals contact["contactid"]
                   where account["name"] ==   "Ajdin's Company"
                    select new {
                           Name = account["name"],
                           Email = account["emailaddress1"],
                           Manager = contact
                      };

foreach (var account in accountQuery)
{
    Console.WriteLine(account.Name + " with e-mail address: " + account.Email + " is run by: " + account.Manager["fullname"]);
}

 

Up to now, we have shown how to filter records based on one condition. LINQ allows filter criteria to consist of multiple conditions. They need to be separated by OR (“||”) or AND (“&&”) operator. In each condition attribute (on the left side) and value (on the right side) can be separated by various operators such as “==”, “!=”, “>”, “<”, etc. The only limitation on building of multiple conditions under “where clause” is on conditions whose fields come from different entities (if several entities are present in the query). Therefore, under “where” clause, there can’t be conditions addressing account’s attributes and contact’s fields, in our example. Instead of that, these conditions need to be separated into two “where” clauses, one of them for conditions addressing account’s fields and the other one for conditions addressing contact’s fields. Between those two set of conditions lies implicit operator “AND”. Unfortunately, that operator cannot be changed to “OR”. So, we can’t construct the filter criteria like “account’s field == x OR contact’s field == y”, but rather only “account’s field == x OR contact’s field == y”. Let’s see how to grab all accounts which have headquarter in Seattle, except Ajdin’s Company and whose primary contact is not Ajdin Kahrovic.

var accountQuery = from account in context.CreateQuery("account")
                   join contact in context.CreateQuery("contact")
                   on account["primarycontactid"] equals contact["contactid"]
                   where account["address1_city"] == "Seattle" && account["name"] !=   "Ajdin's Company"
                   where contact["fullname"] != "Ajdin Kahrovic"
                    select new {
                           Name = account["name"],
                           Email = account["emailaddress1"],
                           Manager = contact
                      };

foreach (var account in accountQuery)
{
    Console.WriteLine(account.Name + " with e-mail address: " + account.Email + " is run by: " + account.Manager["fullname"]);
}

 

LINQ provider for Microsoft Dynamics CRM does not allow “aggregates” and “group by”. The query that is constructed is translated into standard query format used at CRM’s end-points. Therefore, the limitations of LINQ provider are limitations of CRM’s end-points. LINQ syntax itself would allow “aggregates” and “group by” itself, but when the request would be sent to CRM end-point, it would return an error. However, if this functions are really needed, you can retrieve raw data from CRM and build whatever query you want on result set. That query, built on result set rather than on CRM data directly, could include “aggregates” and “group by”. Contrary to those, LINQ for CRM support ordering by “order by” clause. LINQ for CRM also supports lazy load, meaning that it allows you to specify number of records to take and number of records to skip. It is done by take() and skip() functions of query. “Take(n)” function call means to retrieve first n records. “Skip(m).Take(n)” means to skip first m records and then to retrieve following n records. Let’s see the example of upgraded query by previously described ways.

var accountQuery = (from account in context.CreateQuery("account")
                   join contact in context.CreateQuery("contact")
                   on account["primarycontactid"] equals contact["contactid"]
                   where account["address1_city"] == "Seattle" && account["name"] !=   "Ajdin's Company"
                   where contact["fullname"] != "Ajdin Kahrovic"
                   orderby account["name"], account["emailaddress1"]
                    select new {
                           Name = account["name"],
                           Email = account["emailaddress1"],
                           Manager = contact
                      }).Skip(5).Take(3);

foreach (var account in accountQuery)
{
    Console.WriteLine(account.Name + " with e-mail address: " + account.Email + " is run by: " + account.Manager["fullname"]);
}

 

In the example above, we upgraded the query so that it sorts records first by account’s name and then by e-mail address. This query will skip first 5 records and take following 3 records.

In this blog post, we explained and demonstrated how to construct queries by LINQ form Microsoft Dynamics CRM. We explored variety of queries – from simplest ones containing only entity specification to more complex containing specification of fields, records, ordering and containing records from multiple entities. We used late bound types in this example. There are several differences between queries based on early bound types and late bound types. Those kind of differences might be a topic of some of following blog posts. I hope that this blog post was useful for all those familiar with .NET and LINQ, eager to master programmability of Microsoft Dynamics CRM.