Dynamics CRM Linq Query Provider.

Caution:
The enhanced Linq query provider will not work in most cases with CRM online (and any sandboxed plugin assembly). The query provider uses .Net reflection and so has limited functionality when working within the sandboxed environment.

Linq provides an easy and intuitive way to query your CRM Organization for data. However, there have been some limitations in the past when working with the Linq query provider in the CRM SDK. Many of those limitations have been addresses in a Linq query provider in the SS.Crm.Linq assembly. This query provider includes the same functionality as the CRM SDK linq query provider, but also has several improvements including:

Using the SS.Crm.Linq DLL:

The SS.Crm.Linq.dll is automatically added to any plugin projects created by XrmToolkit. If you already have a plugin assembly project that you need to use the Linq query provider in, you can navigate to the following location and choose the correct reference based on the version of the CRM SDK that you are using: "C:\ProgramData\Simpler Software\XrmToolkit\CRM References\"

  • 5.0 - CRM 2011
  • 6.0 - CRM 2013
  • 7.0 - CRM 2015
  • 8.0 - CRM 2016/D365

Specify Columns to Return

Any columns explicitly specified by passing a 'ColumnSet' to the method below will be returned. In addition, columns specified in the return object will also be included. For the query below the following columns will be returned:

  • createdon
  • modifiedon
  • name
// Select the "createdon" and "modifiedon" columns in addition to the "AccountName"
var query = from a in Linq.CrmQueryFactory.Queryable<Account>(_service, new ColumnSet("createdon", "modifiedon"))
                    where a.AccountName == "Account 1"
                    orderby a.AccountName ascending
                    select new ProxyClasses.Account() { AccountName = a.AccountName };

var results = query.ToList();

"In" or "Not In" Queries

You can now query for items based on lists. The following examples demonstate this functionality:

var accountNames = new List<string>()
    {
        "Account 1",
        "Account 2",
        "Account 3"
    };

// "In" query
var query = from a in Linq.CrmQueryFactory.Queryable<Account>(_service)
            where accountNames.Contains(a.AccountName)
            orderby a.AccountName ascending
            select a;

// "Not In" query
query = from a in Linq.CrmQueryFactory.Queryable<Account>(_service)
    where !accountNames.Contains(a.AccountName)
    orderby a.AccountName ascending
    select a;

IsNullOrEmtpy Queries

// Find accounts where the "AccountName" is null or empty
var query = from a in Linq.CrmQueryFactory.Queryable<Account>(_service)
                    where string.IsNullOrEmpty(a.AccountName)
                    select a;

var results = query.ToList();

// Find accounts where the "AccountName" has a value
var query = from a in Linq.CrmQueryFactory.Queryable<Account>(_service)
                    where !string.IsNullOrEmpty(a.AccountName)
                    orderby a.AccountName ascending
                    select a;

var results = query.ToList();

Aggregate Queries

You can always process aggregate queries on the client side, but that requires querying and returning all the records in question. The enhanced query provider runs a FetchXML query behind the scenes which allows you to perform the aggregate operation on the server. This is much quicker and only the aggregate value is returned. The following are examples of how to perform this:

// Determine the sum
var summation = (from a in Linq.CrmQueryFactory.Queryable<Account>(_service)
                where a.AccountName.Contains("ABC Company")
                select a.CreditLimitMoney.Value).Sum();

// Determine the average
var average = (from a in Linq.CrmQueryFactory.Queryable<Account>(_service)
                where a.AccountName.Contains("ABC Company")
                select a.CreditLimitMoney.Value).Average();

// Determine the min
var min = (from a in Linq.CrmQueryFactory.Queryable<Account>(_service)
                where a.AccountName.Contains("ABC Company")
                select a.CreditLimitMoney.Value).Min();

// Determine the max
var max = (from a in Linq.CrmQueryFactory.Queryable<Account>(_service)
                where a.AccountName.Contains("ABC Company")
                select a.CreditLimitMoney.Value).Max();

// Determine the count
var count = (from a in Linq.CrmQueryFactory.Queryable<Account>(_service)
                where a.AccountName.Contains("ABC Company")
                select a.CreditLimitMoney.Value).Count();

// Determine the count of distinct values
var result = (from a in Linq.CrmQueryFactory.Queryable<Account>(_service)
                where a.AccountName.Contains("ABC Company")
                select a.AccountName).Distinct().Count();

// Determine aggregates using grouping
var groups = (from a in Linq.CrmQueryFactory.Queryable<Account>(_service)
                where a.AccountName.Contains("ABC Company") && a.CreditLimitMoney.Value > 5000
                group a.CreditLimitMoney.Value by a.AccountName into g
                orderby g.Key ascending
                select new
                {
                    AccountName = g.Key,
                    CreditLimitSum = g.Sum(),
                    CreditLimitAverage = g.Average(),
                    CreditLimitMin = g.Min(),
                    CreditLimitMax = g.Max()
                }).ToList();

Left Outer Join Queries

Have you ever needed to query based on something that didn't exist? For example: which contacts do NOT have an activity linked to them (where the 'Regarding' object is set to the contact)? Below is an example of how this can be done. The key is in the join and the "where" clause specifying that the "Regarding.Id == null":

// Query the contacts that do not have an activity associated to them
var query = from c in Linq.CrmQueryFactory.Queryable<Contact>(_service)
            join t in Linq.CrmQueryFactory.Queryable<Task>(_service) on c.ContactId equals t.Regarding.Id
            where c.LastName.Contains("Doe")
            where t.Regarding.Id == null
            orderby c.LastName
            select new
            {
                contact_first_name = c.FirstName,
                contact_middle_name = c.MiddleName,
                contact_last_name = c.LastName
            }.ToList();

Query by ID

When working with late bound entities, if you need to retrieve an object by its Id, then you first need to know what the logical name of the Id is, ie for Accounts it is "accountid". The enhanced query provider allows you to simply query the "Id" property of the "Entity" instead of first knowing what the logical name is:

var entity = (from a in CrmQueryFactory.Queryable(_service, "account")
                    where a.Id == first.Id
                    select a).FirstOrDefault();