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
// 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(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:

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
var query2 = 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 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:

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

Column Comparison

Ability to use column comparison. Microsoft documentation here.

// Compare the 'Actual Revenue' to the 'Budget Amount'
var opportunities = this.Service.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
// Retrieve a list of accounts that are owned by me or my reports.
var opportunities = this.Service.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
// Retrieve a list of opportunities that closed in the current fiscal period
var opportunities = this.Service.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
// Retrieve a list of opportunities that closed last week
var opportunities = this.Service.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
// 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 this.Service.Queryable<Opportunity>()
                join c in this.Service.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:

// 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 = this.Service.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:

// Get the FetchXml for the query
var minDate = new DateTime(2020, 1, 1);
var maxDate = new DateTime(2020, 3, 15);
var queryFetchXml = this.Service.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.

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