This post explains how the QueryExpression class can be used in conjunction with IOrganizationService.RetrieveMultiple() in CRM 2013 to retrieve related records for an entity in an N:N relationship. This is something that I could not find much documentation on, and it is not very straightforward – at least for someone starting out in CRM dev.

Scenario

I have two custom entities that have an N:N relationship between them: Course (logical name ng_course) and Subject (logical name ng_subject). The relationship is named ng_course_subject. The query that I need to write is to retrieve all Subjects assigned to a given Course.

Understanding QueryExpression and LinkEntity

The QueryExpression class is where you define your query. You can learn more about this class here: http://msdn.microsoft.com/en-us/library/microsoft.xrm.sdk.query.queryexpression(v=crm.6).aspx.

The key property of this class that enables us to retrieve the related records is the LinkEntities property, which is a collection of LinkEntity objects. Essentially LinkEntity allows us to perform inner joins in our query.

The LinkEntity class contains 4 properties that specify the nature of the join – i.e. they specify the related records that we would like to retrieve. These properties are:

• LinkFromEntityName
• LinkFromAttributeName
• LinkToEntityName
• LinkToAttributeName

Knowing the right values to set for these 4 properties were the trickiest part, and for that we need to understand how CRM stores N:N relationship in the background.

Understanding how N:N relationships are stored

When you create an N:N relationship, CRM creates a hidden entity to store the associations. This is pretty much how you would also do it if you were working on a traditional database.

The name of this entity is the same name as the relationship, i.e. ng_course_subject in our scenario. The attributes of this entity includes the ID of the entities participating in the N:N relationship. If you install the Metadata Browser solution, you will be able to see this hidden entity and its attributes – as shown below.

…and its attributes – notice the 2 keys attributes ng_courseid and ng_subjectid.

Completing the code for QueryExpression and LinkEntity

Now that you understand how N:N relationships are stored, it should be easier to understand the values we need to provide to LinkEntity to retrieve our related records.

As I am needing to retrieve Subject records, the join that I need to perform is from ng_subject to the hidden entity, i.e. ng_course_subject. The join will be performed on the ID column of ng_subject (i.e. ng_subjectid) and the corresponding column in ng_course_subject (i.e. also ng_subjectid). We will use the remaining ID column in ng_course_subject (namely ng_courseid) to filter the related records down to only those for a given Course.

So the values that we need for LinkEntity are as follow:

• LinkFromEntityName: ng_subject
• LinkFromAttributeName: ng_subjectid
• LinkToEntityName: ng_course_subject
• LinkToAttributeName: ng_subjectid

Note that the names are logical names, not schema names.

To filter the related records down to only those for a given Course, we will use the LinkCriteria property of LinkEntity.

Below is the complete code to setup and perform this query.

//Credentials for invoking the service
var credentials = new ClientCredentials();
credentials.UserName.UserName = "mydomain\\user1";
credentials.UserName.Password = "password1";

var organisationUrl = new Uri("http://nguyen5:5555/NguyenOrg/XRMServices/2011/Organization.svc");
var service = new OrganizationServiceProxy(organisationUrl, null, credentials, null);

//This will be used to demonstrate filtering
var courseID = "7CF5E251-30C1-E311-84ED-00155DAB7703";

var query = new QueryExpression()
{
	//Set this to ng_subject as we need to retrieve Subject records
	EntityName = "ng_subject",

	//This will return us all columns for each Subject records
	ColumnSet = new ColumnSet(true),
};

//This is a shortcut method for adding LinkEntity objects to the query. The LinkFromEntityName is default to the
//EntityName of the parent query. Note that logical names are used, not schema names.
var link = query.AddLink("ng_course_subject", "ng_subjectid", "ng_subjectid");
link.LinkCriteria = new FilterExpression()
{
	//Add this to filter the Subject records for a specified Course
	Conditions =
	{
		new ConditionExpression("ng_courseid", ConditionOperator.Equal, courseID)
	}
};

//Invoke the service with our query
var subjectEntities = service.RetrieveMultiple(query);

//Loop through the result
for (var i = 0; i < subjectEntities.Entities.Count; i++)
{
	Console.WriteLine("Name: {0}, ID: {1}", subjectEntities[i]["ng_name"], subjectEntities[i]["ng_subjectid"]);
}