Retrieve record by Using Many-To-Many Relation (Query Expression)

Problem:

              Need to retrieve related records by using many to many relations.

Solution:

              As in MS Dynamics CRM there are 1:N (one- to-many relation) , N:1 ( many- to- one relation) and there is N:N(many-to-many relation) so some time you need to get data by using one of the above relation. Using either 1: N or N: 1 relation you can easily get the record of the related entity but using N: N many-to-many relation can be little tricky.So I will show you in this post how to used one of the many-to-many relation to get the related entity details if you have such type of requirements.
As I am working on account entity in this series of posts, I have used account and marketing list many-to-many relation.I have added another data grid control for this post as I will show all related market list for the selected account entity. You can see the main screen in Image 1 , here the size of the marketing list data grid control will be less and I have just displayed the marketing list name, type(dynamic or static) and marketing list member type( the list is for account, contact or lead entity).


Note:For this post I have created separate view model for the marketing list data grid so that all the processing of the marketing list will be separated from the account data grid. You can find the new view model in the viewModel folder of the project with name “MarketingListViewModel” 
 

Image 1

Let us start with the code of how to retrieve using many-to-many relationship, the code is listed in the List 1, which you can see as below. Here you can see that first I have set the name of the entity which is used to get the record and as you can see I have set the name of the entity which is used in the many-to-many relationship (I have pick name of the entity from the many-to-many relation dialog "Relationship Entity Name", I have also upload the image of the dialog and highlighted the "Relationship Entity Name" you can see it in Image 2).Next thing is to add the condition to get the related record from the many-to-many entity; here I have passed the GUID of the account entity (entity which is selected by the user by clicking on the data grid row). So I will get all the records where account GUID will be matched( here I have set the attribute name to “entityid” which I have get by debugging the code by retrieving all the column with many-to-many entity name).

List 1

Next is the link entity which I have also discussed in my post "Retrieving Child Entity Columns Using Query Expression".here you can see that I have set the parent entity to the “Relationship entity name” as I will get ids of the list based on the entityid which is the id of the account records. So my parent entity will be the many-to-many entity name and then the parent entity attributes which will be mapped for the relationship is the id of the marketing list.After setting the parent entity name and the parent entity attribute next is to mention the child entity name and the child entity attribute, as I get to display the marketing list name and some of the information of the marketing list so my child entity will be marketing list as you can see that I have mention the LinkToEntityName and the LinkToAttributeName to marketing list name and marketing list id. Columns which I have retrieved for the linked entity are marketing list name, marketing list type (dynamic or static) and member type ( marketing list member type as marketing list is either for account , contact or lead entity).

Image 2

Add the link entity to the query expression and then send the request to retrieve the record is there is any record for the account GUID which passed.

Downloads
You can download the source code of the sample from here.
and working CRM 2011 solution from here.

Go to Home

All and any comments / bugs / suggestions are welcomed!