Michael Friis' Blog

Shan McArthur put up a notice that the latest version (4.0.12) of the Microsoft CRM SDK includes Linq querying support. The CRM Team have a couple of blog posts describing the new features. I haven’t tested the new SDK, but I definitely recommend you try it out before using LinqtoCRM and I’ve put a notice to that effect on the LinqtoCRM front page.

It’s a little bit sad that LinqtoCRM probably won’t be used much anymore, but I also think it’s great that Microsoft is now providing what looks to be a solid Linq implementation for Dynamics CRM (especially considering the fact that we haven’t released new versions for more than a year).

Anyway, thanks to everyone who have contributed (esp. Mel Gerats and Petteri Räty) and to all the people who have used LinqtoCRM over the years! Now go get the new SDK and write some queries.

If you’re using Linq-to-SQL, doing group-by and selecting other columns than those in the grouping-key, performance might suffer. This is because there is no good translation of such queries to SQL and Linq-to-SQL has to resort to doing multiple subqueries. Matt Warren explains here. I experienced this firsthand when grouping a lot of geocoded events by latitude and longitude and selecting a few more columns (EventId and CategoryId in the example below):

from e in db.Events
group e by new { e.Lat, e.Lng } into g
select new
{
    g.Key.Lat,
    g.Key.Lng,
    es = g.Select(_ => new { _.EventId, _.CategoryId })
};

One possible solution is to fetch all events, to a ToList() and do the grouping in-memory.

var foo =
    from e in db.Events
    select new { e.Lat, e.Lng, e.EventId, e.CategoryId };

var bar = from e in foo.ToList()
            group e by new { e.Lat, e.Lng } into g
            select new
            {
                g.Key.Lat,
                g.Key.Lng,
                es = g.Select(_ => new { _.EventId, _.CategoryId })
            };

There are some pitfalls when retrieving CRM entities with LinqtoCRM and trying to update them through the CRM web service. The most intuitive (but wrong) approach would be this:

var res = from c in p.Linq()
		  select c;

foreach (var con in res)
{
	con.address1_line1 = "foo";
	service.Update(con);
}

This fails unfortunately. I think someone at Netcompany (my former employer) worked out why this was at some point, but I’ve forgotten.

Instead what you want to do it is new up new entities yourself while setting the relevant id attribute, and then updating the attributes you want to change:

var res = from c in p.Linq()
		  select new contact() { contactid = c.contactid };

foreach (var con in res)
{
	con.address1_line1 = "foo";
	service.Update(con);
}

A few days, a former collegue alerted me to xRM LINQ, a new commercial query provider for Microsoft CRM. I’ve downloaded the trial, of course, and it looks pretty good. xRM LINQ decided not to use usual web service classes and instead provide their own class generator/entity mapper (LinqtoCRM has one too, but only for generating many-to-many classes). This means you can’t mix and match Linq with traditional web service calls, and they had to implement their own create/update functionality. It’s a less gradual and more comprehensive approach than LinqtoCRM but it may give a smoother experience for the programmer. At any rate, I welcome xRM LINQ onto the CRM query provider stage and wish them the best of luck :-).

A less welcome addition is a company called Softpedia, a Romanian outfit. I won’t link to them, to avoid giving them any more Google Juice, but you can find them by googling LinqtoCRM. They seem to be screen-scraping CodePlex and similar sites for projects with permissive licenses and then put up copy-cat pages with downloads for these project on their own site. While not illegal, it’s not very useful for project owners or users either. They’ve been caught inflating their Wikipedia article and many user report trojans and similar on siteadvisor (to be fair, this seems to happen for other popular download sites too).

In other news, a new version of LinqtoCRM is out. It fixes some bugs that have surfaced over the last few months. I’ve also reorganised the wiki, hopefully making it easier for people to find what they’re looking for.

I’ve just recorded a web cast demontrating joins with LinqtoCRM. The piece de resistance is a join across a many-to-many relationship with the intermediary class generated by CRMMetal:

var res = from u in p.Linq()
	join sr in p.Linq() on u.systemuserid.Value equals sr.systemuserid.Value
	join r in p.Linq() on sr.roleid.Value equals r.roleid.Value
        select new { u.fullname, r.name };

The equivalent example query in the CRM SDK is around forty lines, compared to four for LinqtoCRM. Watch the web cast here.

There’s a new version of LinqtoCRM out, get it here. Petteri Räty has rewritten the query-generation engine so that arbitrary selectors are supported. He has also squashed some nasty predicate-bugs and added more unit tests. I’ve implemented a CRMMetal tool that generates classes for many-to-many relationships and joins across these are now supported.

I’ve also recorded a new screencast for your amusement.

The code in this post is from a talk by Erik Meijer, I claim no credit.

Take a look at this screenshot:

Pretty wacky huh? Visual Studio has broken into debug mode even though the division is clearly wrapped in a try-catch. Take a guess at what’s going on before reading on.

Here’s the full code listing:

static void Main(string[] args)
{
    var xs = new[] { 1, 2, 3, 0 };
    IEnumerable q = null;

    try
    {
        q = from x in xs select 1 / x;
    }
    catch { }

    foreach (var z in q)
    {
        Console.WriteLine(z);
    }
}

The obvious expectation is that division-by-zero will be caught by the try-catch. Alas, the division does not happen in the scope of the try statement. Instead, due to the deferred execution paradigm of IEnumerable, it happens on a by-need basis in the for-loop. From there the exception can propogate unopposed to the top af the stack. Haskell, where lazy evaluation dominates, avoids exceptions (and other messy side effects) alltogether, partly for reasons demonstrated by this example.

UPDATE: As Wayne points out, fixing this is as easy as extending the try to cover the enumeration. The point of this post was mainly to poke fun at the Visual Studio debugger and to point out that lazy evaluation can bite you if you’re not careful.

As you may know, Microsoft introduced many-to-many relationships in version 4.0 of Dynamics CRM. Unfortunately, querying these relationships is not supported through the general web service entities as the intermediary entities are not exposed. Fetch XML works fine however (and don’t forget this great hack to generate FetchXML). The missing entities meant that LINQtoCRM didn’t support many-to-many relationships either, even though the underlying query-engine generates Fetch XML.

To remedy this deficiency, a simple tool is now bundled with experimental versions of LINQtoCRM. In the tradition of the other major query providers, it’s called “CRMMetal”. It works by asking the metadata web service for all the metadata, including relationships, and then filtering out the many-to-many ones:

RetrieveAllEntitiesRequest allEntitiesRequest = new RetrieveAllEntitiesRequest();
allEntitiesRequest.RetrieveAsIfPublished = false;
allEntitiesRequest.MetadataItems = MetadataItems.IncludeRelationships;

RetrieveAllEntitiesResponse allEntitiesResponse = 
	(RetrieveAllEntitiesResponse)service.Execute(allEntitiesRequest);

var mtom = allEntitiesResponse.CrmMetadata.OfType().
	SelectMany(e => e.ManyToManyRelationships, (e, d) =>
		new
		{
			intersectname = d.IntersectEntityName,
			schemaname = d.SchemaName.Replace("_association",""),
			ent1Name = d.Entity1LogicalName,
			ent2Name = d.Entity2LogicalName,
			ent1Att = d.Entity1IntersectAttribute,
			ent2Att = d.Entity2IntersectAttribute
		}
		).Distinct().OrderBy(s => s.schemaname);

It then uses the CodeDOM API to generate classes similar to the web service ones, although the property bodies are empty and there’s no XML serialization attributes. It seems CodeDOM has not been updated with recent .Net releases: You can’t generate automatic properties and the API doesn’t support declarative composition of code, a great shame I think. Other than that, generating the code is pretty straightforward:

CodeCompileUnit targetUnit = new CodeCompileUnit();
string nameSpace = "LinqtoCRMApplication.CRM";

CodeNamespace ns = new CodeNamespace(nameSpace);
ns.Imports.Add(new CodeNamespaceImport("System"));

// The stupid codedom API doesn't properly support declarative DOM building, fail.
CodeTypeDeclarationCollection classes = new CodeTypeDeclarationCollection(
	mtom.Select(_ =>
		new CodeTypeDeclaration()
		{
			//Name = _.name,
			Name = _.schemaname,
			IsClass = true,
			IsPartial = true,
		}
	)
	.ToArray());

foreach (CodeTypeDeclaration c in classes)
{
	int count = mtom.Where(_ => _.schemaname == c.Name).Count();
	if (count > 1)
	{
		Console.WriteLine("Ignoring {0} due to duplicality", c.Name);
		continue; // bad one, multiple with same name
	}
	
	c.Members.AddRange(new CodeTypeMember[]
		{
			new CodeMemberProperty() 
			{ 
				Name = mtom.Single(_ => _.schemaname == c.Name).ent1Att,
				Type = new CodeTypeReference(nameSpace + ".Key"),
				HasGet = true,
				HasSet = true,
				Attributes = MemberAttributes.Public,
			},
			new CodeMemberProperty() 
			{ 
				Name = mtom.Single(_ => _.schemaname == c.Name).ent2Att,
				Type = new CodeTypeReference(nameSpace + ".Key"),
				HasGet = true,
				HasSet = true,
				Attributes = MemberAttributes.Public,
				
			}
		}
	);

	// goddam codedom doesn't support automatic properties, 
	// have to add something to getters. fail.
	foreach (CodeMemberProperty p in c.Members)
	{
		// just have it return null
		p.GetStatements.Add(
			new CodeMethodReturnStatement(
				new CodePrimitiveExpression(null)));
	}
}

ns.Types.AddRange(classes);
targetUnit.Namespaces.Add(ns);

CodeDomProvider provider = CodeDomProvider.CreateProvider("CSharp");
CodeGeneratorOptions options = new CodeGeneratorOptions();
options.BracingStyle = "C";
using (StreamWriter sourceWriter = new StreamWriter("ManyToManyClasses.cs"))
{
	provider.GenerateCodeFromCompileUnit(
		targetUnit, sourceWriter, options);
}

Go get the code and give it a whirl. LINQtoCRM still has a few large wharts, the ugliest probably being the very limited selectors permitted. I’ll try to lift the selector implementation from LINQtoSharePoint soon.

A little over a week ago, I got a wonderful email from Mel Gerats. He’s implemented a bunch of new features for LinqtoCRM, including Count, Skip/Take, Contains, EndsWith, StartsWith. There’s also support for chained queries and for returning CRM entities as well as anonymous types.

Mel also found a way to decouple LinqtoCRM from the web service so that it can be compiled as a separate assembly. This comes at the cost of having to define types and relations in your own code however. I rather liked the lightweight approach of querying against the web service entities and will still work with the current release. Going forward, generating the necessary types from the metadata service with a CRMMetal tool (similar to the SQLMetal tool that LinqtoSQL employs) might be the right thing to do — especially because the intermediary entities used in N:N (many-to-many) relationships are not exposed in the web service. The other major thing that needs doing is a proper projection implementation permitting all kinds of expressions in the select part of queries.

Enough babbling, head on over to the CodePlex site for code and samples of use: http://www.codeplex.com/LinqtoCRM

Version 0.2.3 is now up and I’ve made a “getting started” video for your amusement. There are now two downloads. The first one is the entire solution for you to hack on, the second is the minimal amount of code you need to write Linq queries in you own CRM project. Get it here, demo after the jump.

The demo can be found here: http://www.itu.dk/~friism/files/linqtocrm800.swf

UPDATE: New screencast here