The XrmDataContext utilizes an underlying query provider to translate LINQ queries from C# syntax into the query API that the CRM can read natively. Further details on the LINQ framework can be found here. The query provider abstracts much of the CRM SDK specific query API under a more familar C# syntax; however, the querying capability of the query provider is limited to the functionality provided by the CRM query service. This article tries to point out the areas where the standard LINQ to Objects capabilities may run into problems when applied to the CRM LINQ provider.

Sample Queries

To get an idea of what the typical CRM LINQ queries look like, here is a sampling of some common data retrieval scenarios.

var xrm = new XrmDataContext();

// a basic query to retrieve all contacts
var contactsWithSelect =
	from contact in xrm.Contacts
	select contact;

// which is just an extended form of
var contacts = xrm.Contacts;

// output the results
foreach (var contact in contacts)
{
	Console.WriteLine(contact.FullName);
}

The next sample adds a "where" clause filter and returns a single result.

// find a contact by username
var namedContact = (
	from contact in xrm.Contacts
	where contact.Username == "someuser"
	select contact).Single();

// equivelent query using an inline expression
var namedContact2 = xrm.Contacts.Where(c => c.Username == "someuser").Single();

Console.WriteLine(namedContact.Username);

A basic join can be done between two entities with a where clause added against either or both entities.

// find the contacts that have an associated parent customer
var contacts =
	from contact in xrm.Contacts
	join parentCustomer in xrm.Accounts
		on contact.ParentCustomerAccountID equals parentCustomer.AccountID
	where parentCustomer.AccountName == "someCustomer"
	select contact;

foreach (var contactWithParentCustomer in contacts)
{
	Console.WriteLine(contactWithParentCustomer.Username);
}

A many-to-many relationship requires a more complex query that involves joining a relationship entity between the two entities.

// find the marketing lists that a contact is subscribed to
var contactsMarketingLists =
from marketingList in xrm.MarketingLists
join mlm in xrm.MarketingListMemberRelationships
	on marketingList.ListID equals mlm.MarketingList
join contact in xrm.Contacts
	on mlm.Entity equals contact.ContactID
where contact.Email == "charlie.brown@peanuts.com"
select marketingList;

// equivelent results, but done in two separate CRM queries:
var lists = (from c in xrm.Contacts 
	     where c.Email == "charlie.brown@peanuts.com" 
	     select c).Single().MarketingLists;


Console.WriteLine(contactsMarketingLists.ToString());

Translating the LINQ Queries to FetchXML

Having an understanding of the mechanics behind the CRM SDK FetchXML language can be helpful especially in debugging query issues. To view the FetchXML equivalent of a particular CRM LINQ query, call the ToString() method on the query expression. If the LINQ query is in a form that cannot be translated into FetchXML, then the call to ToString() will throw an exception with the details on the translation error.

The previous sample ending with the call:

Console.WriteLine(contactsMarketingLists.ToString());
Outputs the following FetchXML:

<fetch distinct="false" mapping="logical">
	<entity name="list">
		<all-attributes/>
		<link-entity name="listmember" to="listid" from="listid" link-type="inner" alias="listmember0">
			<link-entity name="contact" to="entityid" from="contactid" link-type="inner" alias="contact0">
				<filter type="and">
					<condition attribute="emailaddress1" operator="eq" value="charlie.brown@peanuts.com" />
				</filter>
			</link-entity>
		</link-entity>
	</entity>
</fetch>
Since this translation involves a round-trip back to the CRM (through the CRM Service), it is best to avoid calling ToString() (on an expression) in production code. Also note, that this functionality is disabled when the debugger is attached for the same reason.

Parts of the Query

This section covers the clauses that are valid in a CRM LINQ query.

"from" and "join" Clauses

The first step in constructing a query is identifying the relevant entity types and the relationships between them. The XrmDataContext exposes IQueryable entity set properties to provide the entry point bindings against the CRM entities. These properties are used by the "from" and "join" statements of the LINQ query. A valid binding starts with a single "from" clause. Optionally, one or more "join" clauses can be appended to the chain an related to any previously bound entity. When joining multiple entities, the inner and outer key selectors of the "on" clause should specify primary key or foreign key (uniqueidentifier) properties of the entities. These are the properties with the "ID" suffix and are Guid types.

Multiple "from" clauses are not valid within a single CRM LINQ query so a query with select-many behaviour should be separated into individual queries. Consequently, all entities within the query have some sort of relationship.

Order of Entities

The "from" clause plays a special role in CRM LINQ queries due to limitations of the underlying query provider. The provider is only capable of projecting/returning entities of a single entity type (ie. the "select" clause cannot combine properties from multiple entities). The "from" clause specifies that single "root" entity that is designated for projection. As a result, the "orderby" and "select" clauses must refer back to this root entity. Keep this in mind when deciding how to join multiple entities.

// find the marketing lists that a contact (with a parent customer) is subscribed to
var someUsername = "someuser";

var contactsMarketingLists =
from marketingList in xrm.MarketingLists
// many-to-many relationship (MarketingList to Contact)
join mlm in xrm.MarketingListMemberRelationships
	on marketingList.ListID equals mlm.MarketingList
join contact in xrm.Contacts
	on mlm.Entity equals contact.ContactID
// many-to-one relationship (Contact to Account)
join parentCustomer in xrm.Accounts
	on contact.ParentCustomerAccountID equals parentCustomer.AccountID
where contact.Username == someUsername
select marketingList;

"where" Clause

In order to filter the set of results, where clauses can be added against one or more of the entities. An important limitation to note here is that each where clause may only contain conditions against an individual entity type (ie. a composite condition involving multiple entities is not valid). Instead, each entity should be filtered in separate where clauses.

Method Calls Within Conditions

Currently, where conditions support the standard set of boolean operators (==, !=, >, >=, <, <=, &&, ||) along with a limited set of method calls (Equals, Contains, StartsWith, EndsWith). The string based method calls (Contains, Starts/EndsWIth) translate into the FetchXML's "like" condition which is a case-insensitive operation. As a result, there is an implicit "StringComparison.InvariantCultureIgnoreCase" IEqualityComparer used with these method calls.

// need the website ID for the next query
var websiteID = (
	from webSite in xrm.Websites
	where webSite.Name == "My Website"
	select webSite.WebsiteID).Single();

// find the child webpages of the parent webpage with an associated sitemarker
var childrenOfNews =
	from child in xrm.WebPages
	join parent in xrm.WebPages
		on child.ParentPageID equals parent.WebPageID
	join siteMarker in xrm.SiteMarkers
		on parent.WebPageID equals siteMarker.PageID
	// filters on the SiteMarker entity
	where siteMarker.WebsiteID == websiteID && siteMarker.Name == "News"
	// filters on the WebPage entity
	where child.HiddenFromSitemap == null || child.HiddenFromSitemap == false || child.Name.Contains("text")
	select child;

"orderby" Clause

Query results can be sorted by a property of the entity specified in the "from" clause. Sorting on an entity specified by a "join" clause is not supported. This is related to the contstraint that a query may only project a single entity type.

var descendingMarketingLists =
	from marketingList in xrm.MarketingLists
	orderby marketingList.Name descending
	select marketingList;

"select" Clause

The main rule to keep in mind when mapping properties is that only properties of the entity specified in the "from" clause may be referenced. Otherwise, the CRM LINQ queries support a fairly rich set of mapping capabilities.

// single property
var marketingListNames =
	from marketingList in xrm.MarketingLists
	select marketingList.Name;

// instantiating object
var marketingListsAsStaticType =
	from marketingList in xrm.MarketingLists
	select new KeyValuePair<Guid, string>(marketingList.ListID, marketingList.Name);

// re-assigning entire entity
var marketingListsAsAlias =
	from marketingList in xrm.MarketingLists
	select new { List = marketingList };

// assigning to anonymous type
var marketingListsAsAnonymousType =
	from marketingList in xrm.MarketingLists
	select new { marketingList.Name, ID = marketingList.ListID };

// performing mapping calculations
var marketingListsWithCalculations =
	from marketingList in xrm.MarketingLists
	select new { marketingList.Name, Cost = marketingList.Cost + 100 };

Building More Complex Queries

This section mentions some general advice on how to approach more complex queries. It is best to start with a simplified version of the desired query which may include the basic joins and where clauses that will keep the resulting data manageable. Gradually build from this starter query. If unexpected results appear, it can be useful to examine the associated FetchXML for misplaced entities or invalid clauses.

If you cross the line where the query goes beyond the limitations of the query provider, try stepping back to the last working query and force evaluation by calling the ToList() method. This delegates query processing from the CRM query provider into LINQ to Objects at the point that ToList() is called. Once in the world of LINQ to Objects, the full capabilities of where clause expressions, aggregation, and merging multiple entity types is possible.

var totalCostAggregate = (
	from marketingList in xrm.MarketingLists
	select marketingList.Cost).ToList().Sum();