Michael Friis' Blog

About


LinqtoCRM obsoleted

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.

LinqtoCRM and updating entities

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);
}

LinqtoCRM competitor and new version

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.

Querying relationships with LinqtoCRM

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.

LinqtoCRM 0.3.0 released

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.

CRMMetal and LINQtoCRM

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.

LinqtoCRM 0.2.5 released

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

Microsoft CRM 3.0 32 bit to 4.0 64 bit

Last week I helped a client move from CRM 3.0 32 bit to CRM 4.0 64 bit. Note that 3.0 never came in a 64 bit version so the 32->64 bit upgrade has to happen on the 4.0 side. The procedure described below assumes the existence of a 32 bit staging server to keep the production environment running during the upgrade but it is not strictly necessary.

  1. Backup/restore the 3.0 32 bit database to the staging server
  2. Install 3.0 on the staging server, instructing the installer to use an existing database. Point it at the database you just moved (I highly recommend consulting the redeployment tool at this stage, as getting the databases right is rather hit ‘n miss)
  3. Upgrade the staging server to 4.0 32 bit
  4. Install 4.0 64 bit on the new production server, don’t worry about creating a default organization, you can remove it later on
  5. Backup/restore the 4.0 32 bit database to the new 64 bit production server. Note that you can’t use detach-attach to move from 32 bit to 64 bit — attempting this will bork the database
  6. Use the 4.0 deployment manager “Import organization” feature and point it at the database you just moved
  7. Make the newly imported organization the default one
  8. Depending on the extend of the customizations to the 3.0 installation, some fiddling may be required to get everything running on 4.0

That’s it! And there wasn’t but a single import/export customizations step. The new “Import organization” feature in the 4.0 deployment manager is an absolute killer for this sort of mucking around — use it.

Links:

LinqtoCRM, now with demo!

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

New version of LinqtoCRM

I haven’t updated the project for a while, but Michael Höhne sent me some fixes that I’ve merged. I’ve also compiled the project with Visual Studio 2008 RTM and run it against CRM 4.0 RC0 (partnersource link). Get it here.

Michael H. has hinted that he might contribute more, and I expect to pour some time into the project too.

Older Posts