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.