How to retrieve record using c# for N to N relationship

(0) Share

Report

Posted on by Community Member

Hello,

I've been looking at examples on the different websites/post but I still don't understand how to retrieve a record in N to N relationship.

Say I have Entity A and B they have N to N relationship.

There are two records in Entity A that is linked to a record Entity B.

How can I retrieve those 2 records from the record in Entity B using C#?

Some example code are really appreciated!

Thank you!

*This post is locked for comments

I have the same question (0)

All responses (4)

Answers (2)

Sort by

Verified answer

Joshua Wright 410 on at

Like (0)

Report
Copy link

Link copied!
Advanced find is your friend. N:N relationships actually create a hidden table that can be seen if you build an advanced find query and download the fetch xml. This can also be seen from the N:N relationship, but I find the advanced find to be a much quick way to set up the FetchXml query for C# use. From there standard serviceProxy.RetrieveMultiple(new FetchExpression(strFetch)) call will return the records(EntityCollection) you are looking for. Below I have included a screenshot of an advanced find between our Security Access and Location entities (N:N relatioship)

Downloaded Fetch

<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="true"> <entity name="new_buildingaccess"> <attribute name="new_name" /> <attribute name="createdon" /> <attribute name="new_buildingaccessid" /> <order attribute="new_name" descending="false" /> <link-entity name="new_location_securityaccess" from="new_buildingaccessid" to="new_buildingaccessid" visible="false" intersect="true"> <link-entity name="new_location" from="new_locationid" to="new_locationid" alias="ad"> <filter type="and"> <condition attribute="new_locationid" operator="eq" uiname="" uitype="new_location" value="" /> </filter> </link-entity> </link-entity> </entity> </fetch>

Was this reply helpful? Yes No
Suggested answer

Aric Levin - MVP 30,190 Moderator on at

Like (0)

Report
Copy link

Link copied!

Hi Heimmu,

Just to add to Joshua's response, the following is the way to use the fetch in C# code:

string fetchXml =

String.Format(@'<fetch version='1.0' output-format='xml-platform' mapping='logical' distinct='true'>
<entity name='new_buildingaccess'>

<attribute name='new_name' />

<attribute name='createdon' />

<attribute name='new_buildingaccessid' />

<order attribute='new_name' descending='false' />

<link-entity name='new_location_securityaccess' from='new_buildingaccessid' to='new_buildingaccessid' visible='false' intersect='true'>

<link-entity name='new_location' from='new_locationid' to='new_locationid' alias='ad'>

<filter type='and'>

<condition attribute='new_locationid' operator='eq' uiname='' uitype='new_location' value='{0}' />

</filter>

</link-entity>

</link-entity>

</entity>

</fetch>', locationId.ToString());

EntityCollection results = new EntityCollection();

FetchExpression fe = new FetchExpression(fetchXml);

try

{

EntityCollection results = service.RetrieveMultiple(fe);

if (results.Entities.Count > 0)

{

foreach (Entity result in results.Entities)

{

// Your Code here

}

}

}

catch (FaultException<OrganizationServiceFault> ex)

{

throw new InvalidPluginExecutionException('An error occurred in the RetrieveMultiple function', ex);

}

Hope this helps...

Was this reply helpful? Yes No
Verified answer

Hemant Kumar Sahu 1,829 on at

Like (0)

Report
Copy link

Link copied!

Hi Heimmu,

+ to above answers,

When you create N:N, it creates a table, the table will have the GuidFirstEntity and GuidSecondEntity as columns. So you can query on easily.

You can query on the third entity(relationship table) by guid of Entity B, You may use QueryExpression/RetriveMultiple/FetchXml.

Thanks

Hemant

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report
Copy link

Link copied!

Thank you so much all!!! I really appreciate the help!!

Was this reply helpful? Yes No