Retrieving related records in an N:N relationship using QueryExpression in CRM

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.

1 Hidden entity

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

2 Attributes

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
  • LinkFromAttributeNameng_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"]);
}
Advertisements

About Bernado

Based in Australia, I am a freelance SharePoint and Dynamics CRM developer. I love developing innovative solutions that address business and everyday problems. Feel free to contact me if you think I can help you with your SharePoint or CRM implementation.
This entry was posted in CRM. Bookmark the permalink.

8 Responses to Retrieving related records in an N:N relationship using QueryExpression in CRM

  1. Nitin Ramapure says:

    Thanks a lot! Really nice and simple code.

  2. Pingback: CRM early binding | krsnabhanushali

  3. Oleg says:

    Hi Bernado, how add new record to Retrieving related records in an N:N relationship using QueryExpression in CRM?

  4. oleg says:

    I have a relation between entities rop_appartament and Opportunity N:N.
    The instance apartament of the entity rop_appartament has property apartament.rop_rop_appartament_opportunity and the instance opportunity of of the entity Opportunity has property opportunity.rop_rop_appartament_opportunity
    The propertiy .rop_rop_appartament_opportunity – a relation between entities rop_appartament and Opportunity N:N.

    I want to add a link beetwen the instance of opportunity and the instance appartament and save it.

    My code:
    using (_orgService = new OrganizationService(connection))
    {
    var context = new XrmServiceContext(_orgService);
    var apartament = context.rop_appartamentSet
    .Where(i => i.rop_appartamentId == apartamentId)
    .FirstOrDefault();
    var opportunity = context.OpportunitySet
    .Where(i => i.OpportunityId == opportunityId)
    .FirstOrDefault();

    if (apartament != null && opportunity!=null)
    {

    //=================================
    // ??? Add link opportunity to apartament
    //opportunity.rop_rop_appartament_opportunity.Concat(new[] {apartament}); // Not work !!!
    //================================
    // Use the Context to save changes
    context.UpdateObject(opportunity);
    SaveChangesResultCollection results = context.SaveChanges();
    }

    }

  5. Oleg says:

    Hi Bernado ,
    I have a relation between entities rop_appartament and Opportunity N:N.
    The instance apartament of the entity rop_appartament has property apartament.rop_rop_appartament_opportunity and the instance opportunity of of the entity Opportunity has property opportunity.rop_rop_appartament_opportunity
    The propertiy .rop_rop_appartament_opportunity – a relation between entities rop_appartament and Opportunity N:N.

    I want to add a link beetwen the instance of opportunity and the instance appartament and save it.

    My code:
    using (_orgService = new OrganizationService(connection))
    {
    var context = new XrmServiceContext(_orgService);
    var apartament = context.rop_appartamentSet
    .Where(i => i.rop_appartamentId == apartamentId)
    .FirstOrDefault();
    var opportunity = context.OpportunitySet
    .Where(i => i.OpportunityId == opportunityId)
    .FirstOrDefault();

    if (apartament != null && opportunity!=null)
    {

    //=================================
    // ??? Add link opportunity to apartament
    //opportunity.rop_rop_appartament_opportunity.Concat(new[] {apartament}); // Not work !!!
    //================================
    // Use the Context to save changes
    context.UpdateObject(opportunity);
    SaveChangesResultCollection results = context.SaveChanges();
    }

    }

  6. Nishant Rana says:

    Reblogged this on Nishant Rana's Weblog and commented:
    Nice post on Retrieve from N:N relationship

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s