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:
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.
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:
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();
select new ProxyClasses.Account(a)...
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;
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();
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();
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();
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();
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();
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. Microsoft documentation here.
Supported operators are:
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();
Supported fiscal operations are:
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();
Supported time periods are:
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();
You can query and group results by time periods and return one or multiple aggregates
Supported periods for grouping:
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();
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();
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>
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();
Support for multi-select operators has been added as part of the v8 release. The following operators are supported:
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();
Dataverse supports several operators that can optimize your queries:
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();