Use LINQ to Retrieve Data from Microsoft Dynamics CRM

The XrmDataContext class contains an underlying query provider to translate LINQ queries from C# syntax into the query API that the Microsoft Dynamics CRM can read natively. This query provider abstracts much of the Microsoft Dynamics CRM SDK query APIs under a more familiar C# syntax; however, the querying capability of the query provider is limited to the functionality provided by Microsoft Dynamics CRM. There are some areas indicated where you may have problems with the standard LINQ to Objects capabilities when applied to the Microsoft Dynamics CRM LINQ provider. For more information about the LINQ query language, see LINQ. For more information about query features in Microsoft Dynamics CRM, see Building Queries.

The Advanced Developer Extensions LINQ query provider offers the following capabilities:

• Supports the Join, Where, OrderBy, and Select functions.
• Supports the Skip, Take, Distinct, Single, and First functions.
• Handles the other IEnumerable extensions.
• Supports Select to anonymous types, constructors, and initializers.
• Supports Where conditions with Contains, StartsWith, EndsWith, and Equals String functions.
• Allows you to specify attributes statically or dynamically.

To get an idea of what the typical Microsoft Dynamics CRM LINQ queries look like, here are some samples of common data retrieval scenarios.

Examples

The following example shows how to retrieve all contact records.

// Loop through all Microsoft Dynamics CRM contacts using the IQueryable interfaces
// on the XrmDataContext object.
System.Console.WriteLine("List all contacts in CRM");
System.Console.WriteLine("========================");
foreach (var c in crm.contacts)
{
  System.Console.WriteLine(c.fullname + " " + c.emailaddress1);
}

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

// Find a contact by user name.
var namedContact = (
   from contact in crm.cContacts
   where contact.Username emailaddress1 == " allison.brown@contoso.com someuser"
   select contact).Single();

// Here is the equivalent query using an inline expression.
var namedContact2 = crm.Contacts.Where(c => c.Username == "someuser").Single();
Console.WriteLine(namedContact.Username);var allisonBrown = 
  crm.contacts.FirstOrDefault(c => c.emailaddress1 == "allison.brown@contoso.com");

if (allisonBrown != null)
{
  System.Console.WriteLine(allisonBrown.fullname)
}

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

System.Console.WriteLine("List all contacts where Parent Customer = 'Contoso':");
var contosoContacts = from c in crm.contacts
                      join parentCustomer in crm.accounts
                      on c.parentcustomerid.Value equals parentCustomer.accountid
                      where parentCustomer.name == "Contoso"
                      select c;

foreach (var c in contosoContacts)
{
  System.Console.WriteLine(c.fullname + " " + c.emailaddress1);
}

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

// List the contacts in the Softball team marketing list.
System.Console.WriteLine("List all contacts in Softball Team:");

var members = from c in crm.contacts
              join mlm in crm.listmembers on c.contactid equals mlm.entityid
              join ml in crm.lists on mlm.listid equals ml.listid
              where ml.listname == "Softball Team"
              select c;

foreach (var c in members)
{
  System.Console.WriteLine(c.fullname + " " + c.emailaddress1);
}

When you access a date/time attribute value, you can use the formatted values collection to retrieve the value in the user's converted time zone.

var query = from contact in crm.contacts
                        where contact.fullname.Contains("Ken")
                        select new { contact.fullname, contact.contactid, contact.createdon };
            foreach (var c in query)
            {
                System.Console.WriteLine(c.fullname);
                System.Console.WriteLine(c.contactid);
                System.Console.WriteLine(c.FormattedValues["createdon"]);
            }