Dynamics 365 Linq Query Provider.

Note:
Starting with v7, the XrmToolkit LINQ query provider can now be deployed to all Dynamics365 instances, whether online or on-premise. To use the LINQ query provider in a Dynamics 365 instance, you will need to use ILMerge or IL-Repack to merge the XrmToolkit.Linq.dll into the .dll deployed to Dynamics 365. See the documentation around using ILMerge with XrmToolkit here.

Linq provides an easy and intuitive way to query your Dynamics 365 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 addressed in the XrmToolkit.Linq NuGet package. This query provider includes the same functionality as the CRM SDK linq query provider, but also has several improvements including:

Using the XrmToolkit.Linq DLL:

You can add the XrmToolkit.Linq.dll using NuGet. This package has a reference to the Microsoft.CrmSdk.CoreAssemblies package. Please add the correct version based on the version of your Dynamics 365 organization.

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
using XrmToolkit.Linq; // Imports the 'Queryable<T>' extension method

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

var results = query.ToList();
Note:
Please note that in order to ensure that these properties are populated correctly, you MUST use the constructor that accepts an 'Entity' and pass the variable to this constructor. You can see this in action here:
        select new ProxyClasses.Account(a)...

"In" or "Not In" Queries

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

using XrmToolkit.Linq; // Imports the 'Queryable' extension method

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

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

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

IsNullOrEmtpy Queries

using XrmToolkit.Linq; // Imports the 'Queryable' extension method

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

var results = query.ToList();

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

var results = query.ToList();

String Length Queries

using XrmToolkit.Linq; // Imports the 'Queryable' extension method

// Returns accounts where the length of the name is greater than or equal to 6 characters.
var accountsQuery = this.Service.Queryable<Account>()
    .Where(x => x.Name.Length >= 6)
    .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:

using XrmToolkit.Linq; // Imports the 'Queryable<T>' extension method

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

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

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

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

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

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

// Determine aggregates using grouping
var groups = (from a in organizationService.Queryable<Account>()
                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":

using XrmToolkit.Linq; // Imports the 'Queryable<T>' extension method

// Query the contacts that do not have an activity associated to them
var query = from c in organizationService.Queryable<Contact>()
            join t in organizationService.Queryable<Task>() 
                on c.ContactId equals t.Regarding.Id into ct
            from t in ct.DefaultIfEmpty()
            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:

using XrmToolkit.Linq; // Imports the 'Queryable<T>' extension method

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

Column Comparison

Ability to use column comparison. Microsoft documentation here.

using XrmToolkit.Linq; // Imports the 'Queryable<T>' extension method

// Compare the 'Actual Revenue' to the 'Budget Amount'
var opportunities = organizationService.Queryable<Opportunity>()
    .Where(x => x.ActualRevenue > x.BudgetAmount)
    .ToList();

Query Hierarchical Data

Query hierarchical data. Microsoft documentation here.

Supported operators are:

  • Above
  • AboveOrEqual
  • Under
  • UnderOrEqual
  • NotUnder
  • OwnedByMeOrMyReports
  • OwnedByMeOrMyReportsAndTeams
using XrmToolkit.Linq; // Imports the 'Queryable<T>' extension method

// Retrieve a list of accounts that are owned by me or my reports.
var opportunities = organizationService.Queryable<Account>()
    .Where(x => x.Owner.OwnedByMeOrMyReports())
    .ToList();

Query By Fiscal Periods

Supported fiscal operations are:

  • LastFiscalYear
  • ThisFiscalYear
  • NextFiscalYear
  • LastFiscalPeriod
  • ThisFiscalPeriod
  • NextFiscalPeriod
  • InFiscalYear
  • InFiscalPeriod
  • InFiscalPeriodAndYear
  • InOrBeforeFiscalPeriodAndYear
  • InOrAfterFiscalPeriodAndYear
  • LastXFiscalPeriods
  • LastXFiscalYears
  • NextXFiscalPeriods
  • NextXFiscalYears
using XrmToolkit.Linq; // Imports the 'Queryable<T>' extension method

// Retrieve a list of opportunities that closed in the current fiscal period
var opportunities = organizationService.Queryable<Opportunity>()
    .Where(x => x.ActualCloseDate.Value.ThisFiscalPeriod())
    .ToList();

Query Dates By Time Periods

Supported time periods are:

  • LastYear
  • ThisYear
  • NextYear
  • LastMonth
  • ThisMonth
  • NextMonth
  • LastWeek
  • ThisWeek
  • NextWeek
  • Last7Days
  • Next7Days
  • Yesterday
  • Today
  • Tomorrow
  • OnOrBefore
  • OnOrAfter
  • NotOn
  • LastXDays
  • LastXHours
  • LastXMonths
  • LastXWeeks
  • LastXYears
  • NextXDays
  • NextXHours
  • NextXMonths
  • NextXWeeks
  • NextXYears
  • OlderThanXDays
  • OlderThanXHours
  • OlderThanXMinutes
  • OlderThanXMonths
  • OlderThanXWeeks
  • OlderThanXYears
using XrmToolkit.Linq; // Imports the 'Queryable<T>' extension method

// Retrieve a list of opportunities that closed last week
var opportunities = organizationService.Queryable<Opportunity>()
    .Where(x => x.ActualCloseDate.Value.LastWeek())
    .ToList();

Group By Time Periods (fiscal or regular)

You can query and group results by time periods and return one or multiple aggregates

Supported periods for grouping:

  • Year
  • Quarter
  • Month
  • Week
  • Day
  • FiscalPeriod
  • FiscalYear
using XrmToolkit.Linq; // Imports the 'Queryable<T>' extension method

// Retrieve a list of opportunities where the contacts first name is 'Jane', status reason == 'Won', group by the 'Fiscal Period' 
// and return the 'FiscalPeriods', 'Count', Sum('ActualRevenue'), Average('ActualRevenue'), Sum('EstimatedRevenue')
var results = (from o in organizationService.Queryable<Opportunity>()
                join c in organizationService.Queryable<Contact>() on o.Contact.Id equals c.ContactId
                where c.FirstName.Contains("Jane")
                where o.StatusReason == Opportunity_StatusReason.Won
                group o by o.ActualCloseDate.Value.ThisFiscalPeriod() into g
                orderby g.Key ascending
                select new
                {
                    FiscalPeriod = g.Key,
                    Count = g.Count(),
                    TotalRevenue = g.Sum(x => x.ActualRevenue),
                    AverageRevenue = g.Average(x => x.ActualRevenue),
                    TotalEstimatedRevenue = g.Sum(x => x.EstRevenue),
                }).ToList();

Between Operator

Use the 'Between' operator for numeric, money, or datetime columns:

using XrmToolkit.Linq; // Imports the 'Queryable<T>' extension method

// Retrieve a list of opportunities that closed between 2 dates
var minDate = new DateTime(2020, 1, 1);
var maxDate = new DateTime(2020, 3, 15);
var opportunities = organizationService.Queryable<Opportunity>()
    .Where(x => x.ActualCloseDate.Between(minDate, maxDate))
    .ToList();

Generate FetchXml for your LINQ query

See what FetchXml is being executed for a query by using the 'ToFetchXml' extension method:

using XrmToolkit.Linq; // Imports the 'Queryable<T>' extension method

// Get the FetchXml for the query
var minDate = new DateTime(2020, 1, 1);
var maxDate = new DateTime(2020, 3, 15);
var queryFetchXml = organizationService.Queryable<Opportunity>()
    .Where(x => x.ActualCloseDate.Between(minDate, maxDate))
    .ToFetchXml();

FetchXml:

<fetch mapping="logical">
  <entity name="opportunity">
    <attribute name="actualclosedate" />
    <filter type="and">
      <condition attribute="actualclosedate" operator="between">
        <value>1/1/2020 12:00:00 AM</value>
        <value>3/15/2020 12:00:00 AM</value>
      </condition>
    </filter>
  </entity>
</fetch>

Column Count

Supports the 'ColumnCount' operator with/without the use of the 'Distinct' modifier. Microsoft documentation here.

using XrmToolkit.Linq; // Imports the 'Queryable<T>' extension method

// Retrieve a count of the distinct states being used in the contacts address.
var contacts = organizationService.Queryable<Contact>()
    .Select(x => x.Address1_State_Province)
    .Distinct()
    .CountColumn();

Multi-select Choice Operators

Support for multi-select operators has been added as part of the v8 release. The following operators are supported:

  • ContainsValues
  • Contains
  • Equals

using XrmToolkit.Linq; // Imports the 'Queryable<T>' extension method

// Returns accounts that have both `Value1' and 'Value4' specified as values.
// Results may also have other values specified.
var accountsQuery = this.Service.Queryable<Account>()
    .Where(x => x.MultiSelectOptionset.ContainsValues(EnumValues.Value1, EnumValues.Value4))
    .ToList();

// Returns accounts that have `Value3' specified as one of the values.
// Results may also have other values specified.
var accountsQuery = this.Service.Queryable<Account>()
    .Where(x => x.MultiSelectOptionset.Contains(EnumValues.Value3))
    .ToList();

// Returns accounts that only have both `Value1' and 'Value4' specified and NOT any other values.
var accountsQuery = this.Service.Queryable<Account>()
    .Where(x => x.MultiSelectOptionset.Equals(EnumValues.Value1, EnumValues.Value4))
    .ToList();

Query Optimizations

Dataverse supports several operators that can optimize your queries:

  • No-lock
  • Late materialize
  • Query hints
For more information see the documentation here.

using XrmToolkit.Linq; // Imports the 'Queryable<T>' extension method

// Adds the 'No-lock' statement to the query.
var accountsQuery = this.Service.Queryable<Account>()
    .WithNoLock()
    .ToList();

// Adds the 'LateMaterialize' statement to the query.
var accountsQuery = this.Service.Queryable<Account>()
    .WithLateMaterialize()
    .ToList();

// Adds the SQL query hints to the SQL query.
var accountsQuery = this.Service.Queryable<Account>()
    .WithQueryHints(QueryHints.HashJoin, QueryHints.MergeJoin)
    .ToList();