In my inaugural post, I made brief mention of the newly introduced capabilities of the LINQ provider included as part of the most recent revision of the CRM 4.0 SDK – the Advanced Developer Extensions. In this post, I’ll go into more detail about the features available and how it can increase developer productivity by reducing lines of code and increasing the testability and maintainability of the code.
The article wouldn’t be complete, however, without mentioning some of the current downsides; you can find the caveats later in the post.
NB: I started writing this post last year before CRM 2011 had been released but got distracted (not in the least bit unusual for me). Most of what I’ve written is still applicable to the CRM 2011 LINQ provider as the underlying query mechanism (Query Expressions) is the same.
LINQ
One of the most exciting new features of the .NET framework is LINQ (Microsoft Language Integrated Query). Using an applicable provider, LINQ provides SQL-like access to an underlying object model, via some compiler magic and a collection of extension methods (global static methods that take a “this” pointer as their first parameter).
LINQ allows strongly typed queries and results to an arbitrary underlying data model. The Advanced Developer Extensions makes available a LINQ provider to access CRM entities and attributes via the xRM LINQ provider.
Advanced Developer Extensions
CrmSvcUtil.exe
In order to be able to write LINQ queries against the CRM, you must first generate the following:
- DataContext class – allows a “connection” to the CRM and provides access to both collections of CRM entities and CRUD methods
- Data Transfer Objects – a mapping between a CRM entity instance and a .NET object with which your source code can interact
If you wish to keep the model consistent and in sync, the CrmSvcUtil.exe utility must be re-run each time you make metadata changes to the corresponding CRM installation.
xRM LINQ Queries
So, let’s look at some simple queries:
DataContext xrm = new DataContext("Xrm.ConnectionString");
Guid contactId = new Guid("{12341234-1234-1234-123412341234}");
List<incident> myCases =
(
from cs in xrm.incidents
join c in xrm.contacts on cs.customerid.Value equals c.contactid
where c.contactid == contactId
select cs
).ToList();
This simple “join” query returns all Case entities assigned to the Contact referenced by the Guid “contactId”. To write the equivalent query in QueryExpression syntax would consume many more lines of code, and largely obfuscate the intention of the query.
With LINQ, the simplified syntax allows us to see:
- Selection criteria
- Source entities
- Link criteria
- Output attributes
- Ordering
and more, all in one simple statement.
Returning Custom Classes
The previous query returns entire CRM entities (via the corresponding Data Transfer Object); it is also possible to return only a selection of the entity’s attributes, thus:
var myCases =
(
from cs in xrm.incidents
join c in xrm.contacts on cs.customerid.Value equals c.contactid
where c.contactid == contactId
select new
{
cs.incidentid,
cs.title
}
);
The keyword “var” refers to an anonymous type; anonymous types allow the dynamic creation of an object, without having to explicitly declare the type of the resultant object. Anonymous types may only contain read-only properties; if you wish to subsequently manipulate the content of the returned results, you must declare and instantiate instances of explicitly declared types.
The following provides an (almost real world, albeit abbreviated) example:
protected class AppointmentDataItem
{
// appointment attributes
public Guid AppointmentId { get; set; }
public DateTime ScheduledStart { get; set; }
public DateTime ScheduledEnd { get; set; }
public DateTime Booked { get; set; }
// case (job) attributes
public string JobTicketNumber { get; set; }
public Guid JobId { get; set; }
// contact (customer) attributes
public string CustomerName { get; set; }
public string CustomerTelephone { get; set; }
public string CustomerAddress { get; set; }
public string CustomerCity { get; set; }
public string CustomerState { get; set; }
public string CustomerPostcode { get; set; }
public string CalendarDisplayTitle { get; set; }
}
...
List<AppointmentDataItem> appointments =
(
from app in xrm.appointments
join cs in xrm.incidents on app.regardingobjectid.Value equals cs.incidentid
where cs.msa_partnercontactid.Value == contactId
select new AppointmentDataItem
{
AppointmentId = app.activityid,
ScheduledStart = activity.scheduledstart.GetValueOrDefault().ToLocalTime(),
ScheduledEnd = activity.scheduledend.GetValueOrDefault().ToLocalTime(),
Booked = activity.createdon.GetValueOrDefault().ToLocalTime()
}
).ToList();
...
foreach (AppointmentDataItem appointment in appointments)
{
// get the Case object to which the Appointment is attached
var job =
(
from cs in xrm.incidents
join app in xrm.appointments on cs.incidentid equals app.regardingobjectid.Value
where app.activityid == appointment.AppointmentId
select cs
).Single();
appointment.JobId = job.incidentid;
appointment.JobTicketNumber = job.ticketnumber;
// get the Customer Contact to whom the Case is attached
var customer =
(
from cust in xrm.contacts
join cs in xrm.incidents on cust.contactid equals cs.customerid.Value
join app in xrm.appointments on cs.incidentid equals app.regardingobjectid.Value
where app.activityid == appointment.AppointmentId
select cust
).Single();
appointment.CustomerName = customer.fullname;
appointment.CustomerAddress = customer.address1_line1;
appointment.CustomerCity = customer.address1_city;
appointment.CustomerState = customer.address1_stateorprovince;
appointment.CustomerPostcode = customer.address1_postalcode;
appointment.CustomerTelephone = customer.telephone2;
appointment.CalendarDisplayTitle = customer.fullname + " " + FormatTelephone(customer.telephone2) + "<br />" + FormatAddress(activity);
}
So, we:
- Declare our new custom type
- Query for, and return a List of instances of our new type
- Update the properties of our object instances at will
Pretty cool; and we’re still “talking” to CRM in terms of entities and attributes using a relatively natural (to those of us with SQL skills anyway) language.
Restrictions (some of those Caveats I mentioned)
There are limitations to the queries that can be written using the xRM LINQ provider. These restrictions are due to the underlying query mechanism to which the LINQ provider translates its queries – Query Expression.
One Entity’s Attributes per Query – The impact of Query Expression
Using Query Expressions, we can only return attribute values for a single entity at a time. A corollary of this is that an xRM LINQ Query can only return attribute values for a single entity at a time. For instance, the following is not valid and will error:
var contact =
(
from c in xrm.contacts
join a in xrm.accounts on c.parentcustomerid.Value equals a.accountid
where c.emailaddress1 != null
select new
{
ContactName = c.fullname,
AccountName = a.name
}
);
While the code will compile and run (it *is* valid C# and valid LINQ syntax), it will generate the following error at run-time as soon as you attempt to iterate the resultant collection:
The ‘select’ and ‘orderBy’ calls may only reference a single common entity type.
If you wish to retrieve more than one entity’s attributes in a single query, you are forced to “project” the CRM Entity’s Data Transfer Object(s) onto a local object collection via the ToList() method or similar. Unfortunately, this brings up another caveat.
Projection of Data Transfer Objects onto Local Object collections
The following revised query (NB: the ToList() projection in the ‘from’ clause):
var contact =
(
from c in xrm.contacts.ToList()
join a in xrm.accounts on c.parentcustomerid.Value equals a.accountid
where c.emailaddress1 != null
select new
{
ContactName = c.fullname,
AccountName = a.name
}
);
will no longer error as the ToList() projection turns our Contact entity Data Transfer Objects into a local object collection.
What this means however, is that the LINQ provider will return ALL CRM Contact entities, BEFORE it applies either your predicates (where clause) or attribute selection.
In other words, each and every attribute of each and every entity instance is returned, and subsequently whittled down in memory by the remainder of your LINQ query. This can be very expensive in terms of speed and bandwidth depending on the number and size of the entities in your query.
If you find this to be an issue, you will need to revise your code to use a mechanism such as that described above, wherein you create custom classes to retrieve the relevant data one entity type at a time.
Restricted Predicates – The impact of Condition Expressions
A Condition Expression takes the following form:
ConditionExpression condition = new ConditionExpression("<logicalattributename>", ConditionOperator, object);
This means that the predicates in your CRM LINQ queries must follow a similar format:
where <entityinstance>.<attribute> == "<value>"
- The left-hand side of the predicate MUST be an entity attribute
- The right-hand side of the predicate MUST be a variable or literal
This prevents us from comparing attribute values between entities or even from comparing attribute values from the same entity instance. For instance, the following is NOT valid:
var contacts = ( from c in xrm.contacts where c.modifiedon.Value > c.createdon.Value select c ).ToList();
Of course, you can “solve” this problem by projecting onto a local object collection as per above, but you risk the same performance issues previously described.
Many to Many Queries
I’m sure you can find examples somewhere in the SDK, but I figured I’d make mention of many-to-many queries, as they require some “unusual” syntax, once again due to their relationship to the underlying Query Expressions.
In a recent project my data model included a many-to-many relationship between the Category entity and the Module entity. To query for each Module attached to a particular Category, my query looks like the following:
List<Module> desiredModules =
(
from mod in xrm.ame_modules.ToList()
from mod_cat in mod.ame_ame_category_ame_module
join cat in xrm.ame_categoryame_categories on mod_cat.ame_categoryid equals cat.ame_categoryid
where cat.ame_categoryid == desiredCategoryId
where mod.statuscode.Value == 1
select new Module
{
CategoryId = cat.ame_categoryid,
ModuleId = mod.ame_moduleid,
ModuleNo = mod.ame_name,
ModuleTitle = mod.ame_title
}
).ToList();
The first thing you will probably notice is that I used ToList() to project the Modules onto a local object collection. This is so that I could populate my custom Module class with the Category to which it was attached. In this case, I know that the number of Categories/Modules/Subjects in the system will never be prohibitively large and there are only 2-3 custom attributes on each, so I can live with the overhead.
The “unusual” thing about the query is that we have 2 “from” clauses – this won’t make any sense to SQL aficionados, but it is necessary due to Query Expression and the way that many-to-many relationships are represented via the CRM SDK – the Moniker.
More Cool Stuff
Custom Comparators (this is more C# than CRM, but it’ll get you thinking)
In the same project described above, I need to build lists of Modules and Subjects that a user requires to complete a selected qualification (Category).
First, the user selects their desired Category, from which a list of required Modules and Subjects is constructed:
// obtain desired modules/subjects
List<Module> desiredModules =
(
from mod in xrm.ame_modules.ToList()
from mod_cat in mod.ame_ame_category_ame_module
join cat in xrm.ame_categoryame_categories on mod_cat.ame_categoryid equals cat.ame_categoryid
where cat.ame_categoryid == desiredCategoryId
where mod.statuscode.Value == 1
select new Module
{
CategoryId = cat.ame_categoryid,
ModuleId = mod.ame_moduleid,
ModuleNo = mod.ame_name,
ModuleTitle = mod.ame_title
}
).ToList();
List<Subject> desiredSubjects =
(
from sub in xrm.ame_subjects.ToList()
from mod_sub in sub.ame_ame_module_ame_subject
join mod in desiredModules on mod_sub.ame_moduleid equals mod.ModuleId
where sub.statuscode.Value == 1
select new Subject
{
ModuleId = mod.ModuleId,
SubjectId = sub.ame_subjectid,
SubjectNo = sub.ame_name,
SubjectTitle = sub.ame_title
}
).ToList();
Next, the user selects each Category that they currently hold, from which a list of currently held Modules and Subjects is constructed:
// obtain currently held modules/subjects
List<Module> heldModules =
(
from mod in xrm.ame_modules.ToList()
from mod_cat in mod.ame_ame_category_ame_module
join held in heldCategories on mod_cat.ame_categoryid equals held
where mod.statuscode.Value == 1
select new Module
{
CategoryId = held,
ModuleId = mod.ame_moduleid,
ModuleNo = mod.ame_name,
ModuleTitle = mod.ame_title
}
).ToList();
List<Subject> heldSubjects =
(
from sub in xrm.ame_subjects.ToList()
from mod_sub in sub.ame_ame_module_ame_subject
join mod in heldModules on mod_sub.ame_moduleid equals mod.ModuleId
where sub.statuscode.Value == 1
select new Subject
{
ModuleId = mod.ModuleId,
SubjectId = sub.ame_subjectid,
SubjectNo = sub.ame_name,
SubjectTitle = sub.ame_title
}
).ToList();
Next, I needed to calculate the difference. That is, remove all Modules and Subjects currently held from the list of required Modules and Subjects.
I started with the following:
// calculate difference List<Module> requiredModules = desiredModules.Except(heldModules).ToList(); List<Subject> requiredSubjects = desiredSubjects.Except(heldSubjects).ToList();
Unfortunately, it doesn’t work correctly. As the relationship between each entity is many-to-many and I am storing the parent identifier in my local Module and Subject classes, the “held” Modules/Subjects never matched the “required” Modules/Subjects – for the Modules, the CategoryId didn’t match and for the Subjects, the ModuleId didn’t match:
internal class Module
{
internal Guid CategoryId { get; set; }
internal Guid ModuleId { get; set; }
internal string ModuleNo { get; set; }
internal string ModuleTitle { get; set; }
}
internal class Subject
{
internal Guid ModuleId { get; set; }
internal Guid SubjectId { get; set; }
internal string SubjectNo { get; set; }
internal string SubjectTitle { get; set; }
}
Enter, the Custom Comparator. A custom comparator can be used to provide rules for comparison for classes that don’t meet the default property-wise comparison criteria. Your custom comparator must inherit from IEqualityComparer. For simplicity I’ll show only the Module comparator:
class ModuleComparer : IEqualityComparer<Module>
{
public bool Equals(Module x, Module y)
{
//Check whether the compared objects reference the same data.
if (Object.ReferenceEquals(x, y)) return true;
//Check whether any of the compared objects is null.
if (Object.ReferenceEquals(x, null) || Object.ReferenceEquals(y, null)) return false;
//Check whether the Modules' properties are equal.
return x.ModuleId == y.ModuleId;
}
// If Equals() returns true for a pair of objects
// then GetHashCode() must return the same value for these objects.
public int GetHashCode(Module module)
{
//Check whether the object is null
if (Object.ReferenceEquals(module, null)) return 0;
return module.ModuleId.GetHashCode();
}
}
So, my basis for comparison is only the ModuleId:
//Check whether the Modules' properties are equal. return x.ModuleId == y.ModuleId;
and I use the ModuleId’s hash code as the Module’s hash code:
return module.ModuleId.GetHashCode();
So, I can now alter my code to the following:
// calculate difference List<Module> requiredModules = desiredModules.Except(heldModules, new ModuleComparer()).ToList(); List<Subject> requiredSubjects = desiredSubjects.Except(heldSubjects, new SubjectComparer()).ToList();
and it returns the correct results.
Pogo69's Blog 