Retrieving data from N:N relationship

(0) Share

Report

Posted on by adam90

Hello,

I created a relationship of N:N between two entities - Student and Course.

On the Student form I have a sub-grid that lists all of the courses the student takes, and on the Course form I have a sub-grid that lists all of its students. I do not use any Lookup fields in my forms.

I'd like to retrieve all of the courses a student has.

Is it possible without the use of a Lookup?

I've tried using LINQ but got confused.

I'm currently trying to use QueryExpression.

I'd appreciate help.

Thank you.

*This post is locked for comments

I have the same question (0)

All responses (9)

Answers (0)

Suggested answer

a33ik 84,331 Most Valuable Professional on at

Like (1)

Report

Hello,

To make your life easier I would recommend to start from FetchXml and FetchExpression. Build FetchXml using Advanced Find, download fetch query and use it with FetchExpression. Check following post - docs.microsoft.com/.../use-fetchxml-construct-query

Was this reply helpful? Yes No
Suggested answer

Charles Abi Khirs 3,569 on at

Like (0)

Report

Hello,

There is a similar thread in this link: community.dynamics.com/.../278747

Was this reply helpful? Yes No
Suggested answer

Kokulan 18,054 on at

Like (1)

Report

Hi

Like Andrew suggested building this query using advance find is much easier as you can test and see the results of your query and once you are satisfied with your query in advanced find you can still do this using query expression as shown below

Download the Fetch from your advance find query, please see the example below, a simple query to get active accounts

Fetch looks like this

<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false">

<entity name="account">

<attribute name="name" />

<attribute name="primarycontactid" />

<attribute name="telephone1" />

<attribute name="accountid" />

<order attribute="name" descending="false" />

<filter type="and">

<condition attribute="statecode" operator="eq" value="0" />

</filter>

</entity>

</fetch>

In XrmToolBox, FetchXML Builder, i can paste this and get the query expression as shown below

Please remember to select the Query Expression view to get the results

Hope this helps

Was this reply helpful? Yes No
adam90 30 on at

Like (0)

Report
Thanks for the help.

I'm still having trouble retrieving certain fields from the XML query I constructed.

As I mentioned above , I have two entities with N:N relationship - Student and Course.

In addition the Student entity has a Lookup field to Faculty entity.

In the XML I'd like to query all of the Active Student records - for every record I'd like to retrieve the Student's name, faculty and the Courses the student is taking. For every Course the Student is taking I'd like to retrieve its name and points.

I constructed the following XML -

<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="true"> <entity name="new_student"> <attribute name="new_studentid" /> <attribute name="new_name" /> <attribute name="createdon" /> <order attribute="new_name" descending="false" /> <filter type="and"> <condition attribute="new_faculty" operator="not-null" /> <condition attribute="statecode" operator="eq" value="0" /> </filter> <link-entity name="new_student_course" from="new_studentid" to="new_studentid" visible="false" intersect="true"> <link-entity name="new_course" from="new_courseid" to="new_courseid" alias="aa"> <filter type="and"> <condition attribute="new_name" operator="not-null" /> <condition attribute="new_points" operator="not-null" /> </filter> </link-entity> </link-entity> </entity> </fetch>

I then used the following code -

var fetchRes = service.RetrieveMultiple(new FetchExpression(XMLQuery)); foreach (new_student s in fetchRes.Entities) { var name = s.Attributes["new_name"].ToString(); var faculty = s.GetAttributeValue<EntityReference>("new_faculty"); Console.WriteLine("StudentName : " + name + ", Faculty : " + faculty.Name ); var courses = s.GetAttributeValue<EntityReference>("new_course"); foreach (new_course c in s.) { } }

I was able to get the "new_name" of the Student, but "new_faculty" returns null.
I'd appreciate help.

Was this reply helpful? Yes No
Suggested answer

Charles Abi Khirs 3,569 on at

Like (0)

Report

You are not fetching the faculty lookup in your fetchxml query, add the attribute new_facultyid to your list of returned attribute.

Was this reply helpful? Yes No
adam90 30 on at

Like (0)

Report
Isn't the following line supposed to fetch the faculty ?

<condition attribute="new_faculty" operator="not-null" />

I tried adding the new_facultyid to the attribute list , but I'm getting a FaultException that reads

'new_student' entity doesn't contain attribute with Name = 'new_facultyid'

Is it not because the faculty is a Lookup field and not an attribute?

Was this reply helpful? Yes No
Suggested answer

Charles Abi Khirs 3,569 on at

Like (0)

Report

Isn't the following line supposed to fetch the faculty ? NO

I tried adding the new_facultyid to the attribute list , but I'm getting a FaultException that reads : You should add <attribute name="new_faculty" />

Is it not because the faculty is a Lookup field and not an attribute? A lookup field IS an attribute

Was this reply helpful? Yes No
adam90 30 on at

Like (0)

Report
I see.

If I understand correctly, when the Advanced Find generates the XML file , it does not include Lookup fields as attributes. I'd still have to make minor adjustments to it. Is that correct?

I did the following and now it works -

foreach (new_student s in fetchRes.Entities) { var studentName = s.GetAttributeValue<string>("new_name"); EntityReference facultyRef = s.GetAttributeValue<EntityReference>("new_faculty"); var faculty = service.Retrieve(facultyRef.LogicalName, facultyRef.Id, new ColumnSet("new_name")); var facultyName = faculty.GetAttributeValue<string>("new_name"); Console.WriteLine("StudentName : " + studentName + ", Faculty : " + facultyName ); /* var courses = s.GetAttributeValue<EntityReference>("new_course"); foreach (new_course c in ) { }*/ }

Was this reply helpful? Yes No
Suggested answer

Charles Abi Khirs 3,569 on at

Like (0)

Report

In Fact, The Advanced Find will all the fields that are listed to be displayed in the result view ("Edit Columns" option).

And as per you did, you have to retrieve the faculty attributes (ColumnSet) in order to have the needed info.

Was this reply helpful? Yes No