Retrieve N to N Relationship via C#

N to N relationship is not so common as far as I know due to the complexity of the design, however there are some use cases where it is needed, example: N contact can have N hobby.

Below is the code of how to retrieve hobby by filtering contact GUID:

var query = new QueryExpression(Hobby.EntityName)
{
	ColumnSet = new ColumnSet(Hobby.PrimaryKey),
	LinkEntities =
	{
		new LinkEntity(Hobby.EntityName, app_hobby_contact.EntityName, Hobby.PrimaryKey, app_hobby_contact.app_hobbyid, JoinOperator.Inner)
		{
			LinkEntities =
			{
				new LinkEntity(app_hobby_contact.EntityName, Contact.EntityName, app_hobby_contact.contactid, Contact.PrimaryKey, JoinOperator.Inner)
				{
					LinkCriteria = new FilterExpression(LogicalOperator.And)
					{
						Conditions =
						{
							new ConditionExpression(Contact.PrimaryKey, ConditionOperator.Equal, contactId)
						},
					}
				},
			},
		},
	},
};

To further explain the above code, we use Hobby as our main entity and link to the relationship (app_hobby_contact), and after that we link again from our relationship to Contact and do a filter criteria on selected contact GUID.

It looks complicated but it is useful when you need retrieve some of the attributes here.

If you just need the hobby GUID, we may use below code:

var query = new QueryExpression(app_hobby_contact.EntityName)
{
	ColumnSet = new ColumnSet(app_hobby_contact.app_hobbyid),
	Criteria = new FilterExpression(LogicalOperator.And)
	{
		Conditions =
		{
			new ConditionExpression(app_hobby_contact.contactid, ConditionOperator.Equal, contactId)
		}
	}
};

This code is just retrieve from the relationship and do a filter on the contact GUID, and we are able to get the hobby GUID(s) that own by the contact easily without doing much linked entities and the code is easily to understand.

Leave a Reply

Your email address will not be published. Required fields are marked *