Retrieve associated records (N:N related) in CRM

Hi,

In CRM, many times we create N:N relationship between 2 entities. When ever we do this, CRM internally creates an intermediate entity with 3 fields.

1) Primary key field of newly created entity.
2) First entity Primary Key field
3) Second entity Primary Key field

In my example, I have two entities called Books and Authors. One book can be written by multiple authors and one author can write multiple books.

Here is the code to retrieve Books written by Author 'Gopinath'

string strFirstEntity = "books";

string strSecondEntity = "authors";
string strRelationshipEntityName = "books_authors";
QueryExpression query = new QueryExpression(strFirstEntity);
query.ColumnSet = new ColumnSet(true);
LinkEntity linkEntity1 = new LinkEntity(strFirstEntity, strRelationshipEntityName, "books", "booksid", JoinOperator.Inner);
LinkEntity linkEntity2 = new LinkEntity(strRelationshipEntityName, strSecondEntity, "authors", "authorsid", JoinOperator.Inner);
linkEntity1.LinkEntities.Add(linkEntity2);
query.LinkEntities.Add(linkEntity1);
// Add condition to match the Author name with "Gopinath"
linkEntity2.LinkCriteria = new FilterExpression();
linkEntity2.LinkCriteria.AddCondition(new ConditionExpression("name", ConditionOperator.Equal, "Gopinath"));
EntityCollection collRecords = iService.RetrieveMultiple(query);

Hope this helps.

--
Happy CRM'ing
Gopinath