Pogo69's Blog

December 2, 2012

Adventures in Multi-Threaded CRM 2011 Client Development

Filed under: .NET, C#, CRM — pogo69 [Pat Janes] @ 12:52

Overview

I’ve recently completed development of a process to auto-magically batch import data on a weekly basis for a client.

I have no control over the format in which the data will be received; the client (or their technical representatives) have decided that I will receive the entire set of Active Associations (data representing a link between a Contact and Account) each import.  I will not, however, receive any indication of Inactive (that is, deactivated) associations; as such, the import process will “clean up” prior to each import, deleting all active associations and updating each affected Contact (setting a custom Status attribute to Inactive).

I will discuss the import process in another blog posting; the focus of this post is the “clean up”; specifically the development process of deleting and updating approximately 100,000 records (each) as efficiently as possible.

Early Bound Classes, Large Record Sets and Memory Consumption

The initial iteration of my clean up program, simply queried for a set of all Contacts that were currently set to CustomStatus == Active.  It then looped through each record and set them, one by one, to Inactive.  In pseudo-code:

var contactIds =
  (
    from c in xrm.ContactSet
    where c.new_status == 1
    select c.Id
  ).ToList();

foreach (Guid contactId in contactIds)
{
  Contact c = new Contact
  {
    Id = contactId,
    new_status = 2
  };

  xrm.Update(c);
}

This caused the console program to consume huge amounts of memory to the point that the program stopped working.  I thought it may have to do with the ServiceContext caching objects, so I turned caching off completely:

xrm.MergeOption = MergeOption.NoTracking;

It had no effect (on memory); so I started “batching” my updates.

Enter the ThreadPool

I decided to use System.Threading.ThreadPool to operate simultaneously on batches of records.  Initially, my code selected 1,000 records at a time:

var contactIds =
  (
    from c in xrm.ContactSet
    where c.new_status == 1
    select c.Id
  ).Take(1000).ToList();

foreach (Guid contactId in contactIds)
{
  ThreadPool.QueueUserWorkItem(id =>
  {
    Contact c = new Contact
    {
      Id = id,
      new_status = 2
    };

    xrm.Update(c);
  }, contactId);
}

This would run for a little while, before erroring.  Trawling through the CRM Trace Log files indicated that I was encountering Timeout errors.  WCF Services have a throttling mechanism that allows only a certain number of simultaneous connections.  So I throttled my client code:

ThreadPool.SetMaxThreads(10, 10);

The first parameter allows only 10 concurrently running threads in the ThreadPool.  All other requests must wait for a spare thread to become available, effectively throttling my multi-threaded client code.

But it still errored; this time, I started generating SQL Deadlock errors.  It was intermittent and random.  It appears that I still had threads doing multiple updates when the next batch of candidate Contacts was being selected.  So I had to separate the select and update operations by “waiting” for each batch of updates to complete before going back and selecting the next batch.

In order to wait for a batch of threads, I needed to set up an array of WaitObjects.  The maximum number of objects that can be waited on simultaneously is 64, so that became my batch size:

class Program
{
  #region Local Members
  private static int BATCH_SIZE = 64;
  #endregion

  #region Thread Worker Classes
  private class ContactUpdater
  {
    XrmServiceContext _xrm;
    Guid _contactId;
    ManualResetEvent _doneEvent;

    public ContactUpdater(XrmServiceContext xrm, Guid contactId, ManualResetEvent doneEvent)
    {
      this._contactId = contactId;
      this._doneEvent = doneEvent;
      this._xrm = xrm;
    }
    public void UpdateContact(Object o)
    {
      // de-activate contact
      Contact contact = new Contact
      {
        Id = this._contactId,
        new_Status = (int)Contactnew_Status.Inactive
      };

      // update
      this._xrm.Update(contact);

      // signal that we're done
      this._doneEvent.Set();
    }
  }
  #endregion

  static void Main(string[] args)
  {
    ThreadPool.SetMaxThreads(10, 10);

    using (XrmServiceContext xrm = new XrmServiceContext("Xrm"))
    {
      xrm.MergeOption = Microsoft.Xrm.Sdk.Client.MergeOption.NoTracking;

      // update ALL current active contacts
      while (true)
      {
        // retrieve batch
        List<Guid> contactIds =
          (
            from c in xrm.ContactSet
            where c.new_Status.GetValueOrDefault() == (int)Contactnew_Status.Active
            select c.Id
          ).Take(BATCH_SIZE).ToList();

        if (contactIds.Count == 0) { break; }

        ManualResetEvent[] doneEvents = new ManualResetEvent[contactIds.Count];

        // spawn processing threads
        for (int i = 0; i < contactIds.Count; i++)
        {
          ContactUpdater contactUpdater = new ContactUpdater(xrm, contactIds[i], (doneEvents[i] = new ManualResetEvent(false)));

          ThreadPool.QueueUserWorkItem(contactUpdater.UpdateContact);
        }

        // wait for all threads to complete before processing next batch
        WaitHandle.WaitAll(doneEvents);
      }
    }
  }
}

Conclusion

This provides a framework for the multi-threaded updating of a large number of CRM Entity Instances.  With a little bit of tinkering you could get it to fit most batch update situations.

You could probably also play with the ThreadPool maximum thread count; I haven’t, but 10 seemed like a conservatively good number.

Advertisements

March 28, 2012

CRM SSRS Reports, SQL CLR and External Data – A Case Study

Filed under: .NET, C#, CRM, Cutting Code, SQL Server, Transact SQL — pogo69 [Pat Janes] @ 14:21

I met some interesting requirements recently, wherein I needed to produce a CRM Report that joined standard CRM Data using the FilteredEntityName views with data provided by a 3rd party dll.

Given the complexities of the resultant solution, I thought it would make for an interesting Case Study.  Hopefully, others can learn something from my experiences and perhaps broaden their minds to the possibilities of CRM Integration.

Architectural Overview

A picture speaks a thousand words, so I’ll shutup for a moment and:

In summary:

  1. My SSRS Report (uploaded to CRM) renders data in a single table provided by;
  2. An SQL query that joins data retrieved from;
  3. A CRM Database FilteredEntity View and;
  4. A Table Valued Function wrapping an;
  5. SQL-CLR Table Valued Function that retrieves the required data from;
  6. A Web Service that calculates Fatigue ratings using;
  7. A 3rd-party 32-bit DLL

Now, if that seems a little bit convoluted, keep reading for the reasoning behind this spider-web of data manipulation and retrieval.

I will start backwards, as the final piece of the puzzle is actually the beginning of the story.

How (and Why) It All Works

32-bit DLLs and CRM 2011

In contrast to CRM 4.0 that preceded it, CRM 2011 is now available only as a 64-bit installation.  This means that if you ever have a need to access 32-bit DLLs (legacy code, 3rd-party functionality, pretty much every ODBC driver ever written) you are unable to do so unless you write a “thunking” layer; providing access to the 32-bit functionality in a separate memory space in order to maintain compatibility.

I chose to wrap my 32-bit 3rd-party DLL in a WCF Service.  I have exposed several endpoints, including a basicHttpBinding that allows interoperability with older SOAP based clients – this is necessary for interaction from an SQL-CLR object as the requisite assemblies are not available to create a more modern WCF client.

As touched upon briefly above, I have implemented the same sort of idea to interoperate with ODBC drivers for legacy database applications that provide no other interface.

P/Invoke – The Murky World of Managed/Unmanaged Interoperability

I will just briefly mention a tool called the ‘P/Invoke Interop Assistant’ that I found invaluable when building a C# wrapper for the above mentioned 32-bit DLL.  It was written in C and exposed only a legacy C based interface that cannot be easily consumed by managed code; which is where P/Invoke comes in.

By adding your legacy 32-bit DLL to your managed project and marking it as content, it will be copied to your output folder.  You must then create DllImport wrappers for any functions that you wish to access e.g. (names changed to protect the innocent:

[DllImport("xxxx.DLL", EntryPoint="_xxx_yyyy@nn")]
 internal static extern int xxx_yyyy(
 int p1,
 ref int p2,
 int p3,
 ref int p3,
 ref int p4,
 ref int p5,
 ref double p6,
 ref double p7,
 int p8,
 ref int p9,
 ref double p10
);

I won’t go into the nightmare that was trying to figure out exactly what that signature should look like, nor the corresponding nightmare that was figuring out how to properly talk to it.  However, I can say that the P/Invoke Interop Assistant made life somewhat easier.

SQL-CLR Table-Valued Functions are awesome!

I got so excited about SQL-CLR when we first came in contact with each other.  In fact, I was all over a whole stack of new “toys” that became available with SQL Server 2005 (I will mention another, Common Table Expressions or CTEs a little later).

SQL-CLR allows you to write a managed assembly (.NET code) that can perform tasks not ordinarily available via Transact SQL and access that functionality via a Stored Procedure or Function wrapper.  My first attempt at such an assembly was to expose some simple .NET Regular Expression pattern matching/replacing functionality, which became invaluable (and surprisingly fast) for a data cleansing project on which I was working.

In this case, however, I wrote my first SQL-CLR Table Valued function; which does exactly what it says – returns data from a CLR managed assembly that is represented and consumed in Transact SQL as a Table.

So; what does an SQL-CLR Table Valued function look like?  I’m glad you asked.  I will let MSDN explain in details, but basically, you have to implement two methods:

  1. One method does that actual data retrieval/collection/however you obtain the data – this method must return the interface System.Collections.IEnumerable (or something that implements same) and will be marked with metadata describing various SQL related properties and the name of the method that will be used to “feed” the data back to SQL Server
  2. The second method does the feeding – taking a generic Object (which are individual elements of the datatype returned from the first method), ripping it apart, and populating out parameters for each column in the resultset.  This allows SQL Server to stream the data back to the query that calls your assembly
Example as follows:
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using System.Collections.Generic;
using System.Linq;
using System.Xml;
using Microsoft.SqlServer.Server;
using BMP.FAID.SqlClr.WebServiceWrapper;
public partial class CalculateFatigue
{
 [SqlFunction(FillRowMethodName = "FillRow", DataAccess = DataAccessKind.None, TableDefinition = "Start datetime, Fatigue float", IsDeterministic = true, IsPrecise = false)]
 public static System.Collections.IEnumerable InitMethod(SqlXml rosterPeriods, string faidServiceUrl)
 {
  return WebService.Process(rosterPeriods.Value, faidServiceUrl);
 }

 public static void FillRow(Object obj, out SqlDateTime start, out SqlDouble fatigue)
 {
  HourlyFatigue hourBlock = (HourlyFatigue)obj;
  start = hourBlock.Start;
  fatigue = hourBlock.Fatigue;
 };
}

NB: I implemented my Web Service Wrapper in another assembly so that I could unit test it outside of SQL Server.

As per the in-depth explanation of SQL-CLR Table Valued functions, I will leave the details of the deployment of these managed assemblies to your MSDN searching capabilities – in fact, I think I may have gone through the process in another blog posting.

The Transact SQL Query

Apologies for the verbosity, but there are several elements of the query that I would like to explain for those who are not familiar with the concepts within.

--declarations and setup (hard-coded for demonstrative purposes)
declare
 @pilotId uniqueidentifier = 'A4D1BAF3-BC41-E111-B9ED-00012E244939',
 @year int = 2012,
 @month int = 2;

declare
 @monthBegin datetime = (select cast(@year as nvarchar) + '-' + cast(@month as nvarchar) + '-01');
declare
 @monthEnd datetime = DATEADD(SECOND,-1,DATEADD(Month,1,@monthBegin));

declare
 @daysInMonth int = (select DAY(@monthEnd));

declare
 @faidServiceUrl nvarchar(255) = (select top 1 mrcrm_faidserviceurl from Filteredmrcrm_rosterandavailabilitysettings);

create table #HourBlock
(
 [Start] [datetime] NULL,
 [Fatigue] [float] NULL
);

-- find roster items
declare
 @rosterPeriods xml =
 (
  select
   rsi.mrcrm_Start, rsi.mrcrm_End, rt.mrcrm_RiskLevel
  from
   mrcrm_rosterscheduleitem rsi
  join
   mrcrm_rostertype rt on rt.mrcrm_rostertypeId = rsi.mrcrm_RosterTypeId
  where
   rsi.mrcrm_EmployeeId = @pilotId
  and
   rt.mrcrm_RiskLevel > 0
  and
   rsi.mrcrm_Start <= @monthEnd
  and
   rsi.mrcrm_End >= DATEADD(DAY, -14, @monthBegin)
  and
   rsi.statecode = 0
  order by
   rsi.mrcrm_Start
  for xml path('period'), root('roster')
 );

-- use faid service to calculate hourly fatigue
insert
 #HourBlock
select
 Start, Fatigue
from
 [BMP_Functions].[dbo].[InitMethod](@rosterPeriods, @faidServiceUrl);

;with
-- obtain list of all hours in the day
Hour_CTE (Hour)
as
(
 select 0

 union all

 select Hour + 1 from Hour_CTE where Hour < 23
),
-- obtain list of all days in the month
Day_CTE (Day)
as
(
 select @monthBegin

 union all

 select DATEADD(Day, 1, Day) from Day_CTE where Day < @monthEnd
)
-- join it all up together
select
 Day_CTE.Day,
 Hour_CTE.Hour,
 ISNULL(h.Fatigue, 0.0) as Fatigue
from
 Hour_CTE
cross join
 Day_CTE
left join
 #HourBlock h on DATEPART(Hour, h.Start) = Hour_CTE.Hour and Day(h.Start) = Day(Day_CTE.Day);

Again; apologies for the verbosity but it is as simple as I could make it while still demonstrating what I had hoped (yes, it is more complicated than that).  Now, to break it down.

Configuration Data

I store system-wide configuration data in a “Settings” entity instance in the CRM.  This allows me to set variables such as Web Service URLs centrally and access them from anywhere that has access to CRM Data.  CRM components access the Fatigue Web Service that I show here in exactly the same way via plugins etc.

declare
 @faidServiceUrl nvarchar(255) = (select top 1 mrcrm_faidserviceurl from Filteredmrcrm_rosterandavailabilitysettings);

Table Inputs into SQL-CLR Functions (are not allowed) – ‘FOR XML PATH’

In order to calculate Fatigue ratings, the 3rd-party component I was using requires a “table” of input data.  Unfortunately, although you can output a table of data from an SQL-CLR function, you cannot input such data.  So I decided to convert my input data into XML and feed it into my managed assembly function via an SQL XML datatype.

This is another of the “new for SQL Server 2005” Transact SQL enhancements that I was very excited about upon first use.  While the “FOR XML …” syntax was available prior to SQL Server 2005, the introduction of “FOR XML PATH…” made the structured formatting of XML data so much cleaner.

-- find roster items
declare
 @rosterPeriods xml =
 (
  select
   rsi.mrcrm_Start, rsi.mrcrm_End, rt.mrcrm_RiskLevel
  from
   mrcrm_rosterscheduleitem rsi
  join
   mrcrm_rostertype rt on rt.mrcrm_rostertypeId = rsi.mrcrm_RosterTypeId
  where
   rsi.mrcrm_EmployeeId = @pilotId
  and
   rt.mrcrm_RiskLevel > 0
  and
   rsi.mrcrm_Start <= @monthEnd
  and
   rsi.mrcrm_End >= DATEADD(DAY, -14, @monthBegin)
  and
   rsi.statecode = 0
  order by
   rsi.mrcrm_Start
  for xml path('period'), root('roster')
 );

This query outputs data in the following format:

<roster>
 <period>
  <mrcrm_Start>2012-02-06T12:00:00</mrcrm_Start>
  <mrcrm_End>2012-02-06T13:30:00</mrcrm_End>
  <mrcrm_RiskLevel>3</mrcrm_RiskLevel>
 </period>
</roster>

Such data can then be processed in C# using the usual System.Xml provided mechanisms.

Using the SQL-CLR Table Valued Function

Using the SQL-CLR Table Valued Function is as simple as referencing its “wrapper” user-defined function, passing in any required parameters (in this case, the XML data and web service URL) and selecting from the resultant table.

-- use faid service to calculate hourly fatigue
insert
 #HourBlock
select
 Start, Fatigue
from
 [BMP_Functions].[dbo].[InitMethod](@rosterPeriods, @faidServiceUrl);

Common Table Expressions or CTEs – Outputting the Data

Also newly introduced in SQL Server 2005, is the concept of Common Table Expressions.  CTEs provide the equivalent of an inline function, which can be very handy when building reports for Dynamics CRM given that user-defined elements such as custom stored procedures and user-defined functions are disallowed within the CRM database.

NB: in the case of my SQL-CLR function, I stored the managed assemblies and function in a separate database.

In addition to providing a mechanism for an “inline” function, CTEs allow the definition of a recursive function as per my Hour_CTE and Day_CTE below; in this case, I am building a list of:

  1. Each Hour of the Day (0-23)
  2. Each Day in the Month (1-however many days are in the current month)
Using the results of the CTEs and SQL-CLR function, the results are output via a select statement – CROSS JOINS are used to ensure that a value is produced for each combination of Day and Hour.
;with
-- obtain list of all hours in the day
Hour_CTE (Hour)
as
(
 select 0

 union all

 select Hour + 1 from Hour_CTE where Hour < 23
),
-- obtain list of all days in the month
Day_CTE (Day)
as
(
 select @monthBegin

 union all

 select DATEADD(Day, 1, Day) from Day_CTE where Day < @monthEnd
)
-- join it all up together
select
 Day_CTE.Day,
 Hour_CTE.Hour,
 ISNULL(h.Fatigue, 0.0) as Fatigue
from
 Hour_CTE
cross join
 Day_CTE
left join
 #HourBlock h on DATEPART(Hour, h.Start) = Hour_CTE.Hour and Day(h.Start) = Day(Day_CTE.Day);

SSRS and SQL-CLR Functions – A Difficulty

One final word on my experiences with this process.

There is a well-known issue with SSRS Reports, wherein the rendering of large quantities of data can cause excess memory consumption and the resultant failure of a Report to complete.  I encountered this issue, however the supposed cause of such issues did not apply to me – 25 columns and 31 rows of data (in the Production Report) does not constitute a large quantity of data.  Nor does the single, lone table rendering that data constitute a complex visual display.

While I cannot prove causality, I was able to overcome the memory consumption issue, by moving my table and query defined above into a Sub-Report.

My hypothesis is that there is something in my query that could not deal with another DataSet having been defined in the same report (I was using other DataSets to provide data for the Year, Month and Pilot parameters).  This hypothesis is supported by the subsequent removal of the additional DataSets and hard-coding the parameters, allowing the successful rendering of the Report.

I asked for assistance in the SSRS MSDN Support Forums, but was unable to elicit any useful information.  When time permits, I will follow up with this new discovery (I have only just stumbled upon the Sub-Report workaround) and see if it sparks any further ideas.

 

May 7, 2011

The Missing LINQ – CRM 4.0 SDK – Advanced Developer Extensions

Filed under: .NET, C#, CRM, Cutting Code — Tags: , , , — pogo69 [Pat Janes] @ 09:01

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();
resulting in the following run-time (because again, it *is* valid c# and valid LINQ) error:
Lambda Parameter not in scope

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.

March 31, 2011

Microsoft.Crm.Sdk.dll v4.0 vs v5.0 – CRM 4.0 SDK Library Redirection to CRM 2011

Filed under: .NET, C#, CRM, Cutting Code — Tags: , , , , — pogo69 [Pat Janes] @ 11:12

NB: This issue has been fixed in CRM 2011 Rollup 1.

We completed the migration of our internal CRM 4.0 system to CRM 2011 last week.  It all went reasonably smoothly with only what appear to be a few lingering javascript customisations that don’t play nicely with the new system.

We also run an externally available Customer Support Portal using the Customer Portal Accelerator from CodePlex.  This web application leverages the XRM libraries in the CRM 4.0 SDK to provide a reference implementation for Customer Support, upon which developers with the requisite skills can build additional functionality.

While it would be nice to move to the new CRM 2011 specific versions of the Portal Accelerators, I had to get the existing version up and running ASAP, so that our customers didn’t experience undue difficulties with the transition; let’s face it, as CRM Developers, who ever has time to work on their internal systems when there’s client work to do?  In order to get it all working on the new system, I had to change a few things.  In case anyone else needs to do the same, the following describes the process.

Web.config

CRM 2011 utilises the .NET framework v4.0.  Many of the Configuration Sections in a Web.config generated for a .NET 3.5 web application are no longer necessary in .NET 4.0 as they are specified in the Machine.config and are therefore inherited by each Web Application.

I made the following modifications to my Web.Release.config (Config Transform for Release Builds) to remove the offending sections when I compiled a Release build:

<configuration xmlns:xdt="<a href="http://schemas.microsoft.com/XML-Document-Transform">http://schemas.microsoft.com/XML-Document-Transform</a>">
  <configSections>
    <sectionGroup name="system.web.extensions" type="System.Web.Configuration.SystemWebExtensionsSectionGroup, System.Web.Extensions, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35">
      <sectionGroup name="scripting" type="System.Web.Configuration.ScriptingSectionGroup, System.Web.Extensions, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35">
        <section name="scriptResourceHandler" xdt:Locator="Match(name)" xdt:Transform="Remove"/>
        <sectionGroup name="webServices" type="System.Web.Configuration.ScriptingWebServicesSectionGroup, System.Web.Extensions, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35">
          <section name="jsonSerialization" xdt:Locator="Match(name)" xdt:Transform="Remove"/>
          <section name="profileService" xdt:Locator="Match(name)" xdt:Transform="Remove"/>
          <section name="authenticationService" xdt:Locator="Match(name)" xdt:Transform="Remove"/>
          <section name="roleService" xdt:Locator="Match(name)" xdt:Transform="Remove"/>
        </sectionGroup>
      </sectionGroup>
    </sectionGroup>
  </configSections>
...
</configuration>

CRM SDK Library – Redirect from v4.0 to v5.0

This was a tricky one.  It seems that the installation of CRM 2011 places a machine-wide redirection for the Microsoft.Crm.Sdk.dll from the v4.0 to the v5.0 version.

This means that any application running on the CRM Server that relies on the Microsoft.Crm.Sdk.dll assembly will load the v5.0 version even if the v4.0 version exists in the application’s /bin folder!  I verified this by enabling Fusion logging:

In order to be able to successfully load the v4.0 version of the assembly and execute your legacy v4.0 code, you have to override the <publisherpolicy /> set at the machine level, with the following addition to the application configuration file:

<configuration xmlns:xdt="http://schemas.microsoft.com/XML-Document-Transform">
  ...
  <runtime>
    ...
    <assemblyBinding appliesTo="v4.0.30319" xmlns="urn:schemas-microsoft-com:asm.v1">
      <dependentAssembly>
        <assemblyIdentity name="Microsoft.Crm.Sdk" publicKeyToken="31bf3856ad364e35"/>
        <publisherPolicy apply="no"/>
      </dependentAssembly>
    </assemblyBinding>
  </runtime>
  ...
</configuration>

Create a free website or blog at WordPress.com.