CRM Entity Relationship Many to Many (N:N)

There are two ways of getting the related entity details in many to many relationship in CRM 4.0

One way is to use RetrieveMultipleRequest function from the SDK:

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
public BusinessEntityCollection RetrieveEntityCollectionFromManyToMany(
    string linkTableEntityName,
    string filterEntityName, string filterEntityIdName, string filterAttribute, string filterValue,
    string returnedCollectionEntityName, string returnedCollectionEntityIdName, string[] returnedAttributes)
{
    // Selection against linked Filter Entity
    var con = new ConditionExpression
    {
        AttributeName = filterAttribute,
        Operator = ConditionOperator.Equal,
        Values = new[] { filterValue }
    };
 
    var filter = new FilterExpression
    {
        FilterOperator = LogicalOperator.And
    };
 
    filter.AddCondition(con);
 
    // the Entity that you are filtering with
    var filterLinkEntity = new LinkEntity
    {
        LinkToEntityName = filterEntityName,
        LinkFromAttributeName = filterEntityIdName,
        LinkToAttributeName = filterEntityIdName,
        LinkCriteria = filter
    };
 
    // The linktable Entity that CRM generates
    var linkTableEntity = new LinkEntity
    {
        LinkToEntityName = linkTableEntityName,
        LinkFromAttributeName = returnedCollectionEntityIdName,
        LinkToAttributeName = returnedCollectionEntityIdName
    };
 
    linkTableEntity.LinkEntities.Add(filterLinkEntity);
 
 
    // The Entity returning the results
    var expression = new QueryExpression
    {
        EntityName = returnedCollectionEntityName
    };
 
    // Set columns being returned
    if (returnedAttributes == null)
    {
        expression.ColumnSet = new AllColumns();
    }
    else
    {
        expression.ColumnSet = new ColumnSet(returnedAttributes);
    }
 
    expression.LinkEntities.Add(linkTableEntity);
 
    var request = new RetrieveMultipleRequest { Query = expression, ReturnDynamicEntities = true };
    var response = (RetrieveMultipleResponse)mService.Execute(request);
    return response.BusinessEntityCollection;
}


For example, if we have a Loan custom entity and we want to return sets of borrowers (contacts):

?
1
2
3
4
5
6
        CrmServiceWrapper wrapper = new CrmServiceWrapper(crmOrganizationName, crmServer, crmPort, crmUsername, crmUserPassword, crmUserDomain);
 
        BusinessEntityCollection collection = wrapper.RetrieveEntityCollectionFromManyToMany("new_loan_borrower", "new_loan", "new_loanid",
"new_name", "2508280", "contact", "contactid", null);
 
        return collection.BusinessEntities;


---
Second way is to use Fetch function to return the XML of the related entities:
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
CrmServiceWrapper wrapper = new CrmServiceWrapper(crmOrganizationName, crmServer, crmPort, crmUsername, crmUserPassword, crmUserDomain);
 
string linkFetch = @"<fetch mapping='logical'>
 <entity name='new_loan'>
  <filter type='and'>
   <condition attribute='new_name' operator='eq' value='2508280'/>
  </filter>
  <link-entity name='new_loan_borrower' from='new_loanid' to='new_loanid'>
   <attribute name='contactid'/>
   <link-entity name='contact' to='contactid'>
    <attribute name='new_armcontactid'/>
    <attribute name='fullname'/>
   </link-entity>
  </link-entity>
 </entity>
</fetch>";
 
string result = wrapper.FetchXML(linkFetch);
 
XmlDocument doc = new XmlDocument();
doc.LoadXml(result);


James Downey created a nice little program FetchXMLBuilder to help you build the fetch query.

---
Just for completion, below is the CRMServiceWrapper constructor that I use:

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
public CrmServiceWrapper(string organizationName, string serverName, string portNumber)
        {
            // Create authentication token for CrmService web service
            CrmAuthenticationToken token = new CrmAuthenticationToken();
            token.AuthenticationType = 0;
            token.OrganizationName = organizationName;
 
            mService = new CrmService();
            mService.Url = "http://" + serverName + ":" + portNumber + "/mscrmservices/2007/crmservice.asmx";
            mService.Credentials = CredentialCache.DefaultCredentials;
            mService.CrmAuthenticationTokenValue = token;
            mService.Timeout = 300 * 1000;
        }
 
public CrmServiceWrapper(string organizationName, string serverName, string portNumber, string username, string password, string domain)
        {
            NetworkCredential credential = new NetworkCredential(username, password, domain);
 
            RetrieveCrmTicketResponse ticketResponse = RetrieveCrmTicket(organizationName, serverName, portNumber, username, password, domain);
 
            // Create authentication token for CrmService web service
            CrmAuthenticationToken token = new CrmAuthenticationToken();
            token.AuthenticationType = 0;
            token.OrganizationName = organizationName;
            token.CrmTicket = ticketResponse.CrmTicket;
 
            mService = new CrmService();
            mService.Url = "http://" + serverName + ":" + portNumber + "/mscrmservices/2007/crmservice.asmx";
            mService.Credentials = credential;
            mService.CrmAuthenticationTokenValue = token;
            mService.Timeout = 300 * 1000;
        }
 
        private RetrieveCrmTicketResponse RetrieveCrmTicket(string organizationName, string serverName, string portNumber, string username, string password, string domain)
        {
            NetworkCredential credential = new NetworkCredential(username, password, domain);
 
            // Configure CrmDiscoveryService Web service proxy
            dService = new CrmDiscoveryService();
            dService.Url = "http://" + serverName + ":" + portNumber + "/mscrmservices/2007/AD/CrmDiscoveryService.asmx";
            dService.Credentials = credential;
 
            // Retrieve a list of available organisation
            RetrieveOrganizationsRequest orgRequest = new RetrieveOrganizationsRequest();
            orgRequest.UserId = domain + "\\" + username;
            orgRequest.Password = password;
            RetrieveOrganizationsResponse orgResponse = (RetrieveOrganizationsResponse)dService.Execute(orgRequest);
 
            // Find the target organization
            OrganizationDetail orgInfo = null;
            foreach (OrganizationDetail orgDetail in orgResponse.OrganizationDetails)
            {
                if (orgDetail.OrganizationName.Equals(organizationName))
                {
                    orgInfo = orgDetail;
                    break;
                }
            }
 
            // Check if the organization was not found
            if (orgInfo == null)
                throw new Exception("The specified organization was not found.");
 
            // Retrieve a CrmTicket from the CrmDiscoveryService web service
            RetrieveCrmTicketRequest ticketRequest = new RetrieveCrmTicketRequest();
            ticketRequest.OrganizationName = organizationName;
            ticketRequest.UserId = domain + @"\" + username;
            ticketRequest.Password = password;
            RetrieveCrmTicketResponse ticketResponse = (RetrieveCrmTicketResponse)dService.Execute(ticketRequest);
 
            return ticketResponse;
        }


Hope this helps ^_^

Comments

  1. gmajor334November 1, 2012 at 5:15 PM

    hy Andreas,
    i'm trying to insert a record in entity with many to many relationship. how do i do that??

    i'm using crmservice.create method.
    any ideas??

    thanks

    ReplyDelete
    Replies
    1. AndreasNovember 6, 2012 at 10:49 AM

      Hi, you should use AssociateRequest to establish the relationship. Example is in this blog:

      http://chaitanyaprasadtk.blogspot.com.au/2012/01/dealing-with-nn-relationship-in-crm.html

      Delete
  2. AnonymousMarch 6, 2014 at 4:14 PM

    Thank you !! :)

    ReplyDelete

Post a Comment

Popular posts from this blog

SharePoint 2013 anonymous access add attachments to list item

Image
Hi, Sometimes you want to enable anonymous access to be able to add a list item but at the same time attaching some documents. After good few hours playing around trying to make this work, turns out that we can achieve this functionality. The trick is to use RunWithElevatedPrivileges method for the file upload. We also want the file upload functionality to be available on the NewForm.aspx as it wouldn't make sense to put it in other forms as any user would be able to upload files to other records. The result will be something like this: When it is saved, the user is redirected to the display form: The close button then will take the user back to the homepage (or any page you desire) The steps: 1. Create an ascx control to host your file upload control in your project.  Put the below script in the ascx as well (credit to this post ): The code behind: 2. Create custom form template. You have to put the ascx in the CONTROLTEMPLATES folder of the ...
Read more

CRM Plugin - Parent and Child Pipeline

This concept tends to confuse people often - and I was no exception. I came across this problem when trying to create custom auto-numbering for one of my projects. Creating auto-number for Opportunity is easy. This is because the user creates opportunity straight from the home screen - thus the plugin runs on parent pipeline. However, it's a different story if you are dealing with Quote entity. Quote can be created in 2 ways, through the home screen OR the opportunity screen. If you create it from the latter, it will run on the child pipeline and you will find that your plugin will not work. Registering it as a child pipeline is easy, but CRM makes it harder since you can't use ICRMService. Instead, you have to create CRMService manually by using this piece of code: Not only that, with the child plugin you can't use Query expression to fetch other entities in your code unless you register it as ASYNCHRONOUS plugin. Mark Kovalcson in his blog found out that: A C...
Read more