MS CRM 4.0 Many to Many Query helper

Rather than trying to explain how to query a many to many relationship, I thought it might be easier for many people to take a working generic method that retrieves a collection of entities from a Many to Many relationship and modify it as needed.

When a many to many relationship is created CRM creates a link table. Default naming conventions could give you a name like this

new_new_entity1_new_entity2

which is a link table between new_entity1 and new_entity2

The three entities involved are the following:

• linkTableEntity - new_new_entity1_new_entity2
• filterEntity – new_entity1
• put your conditions against this
• returnedEntity – new_entity2
• specify your return attributes against this

The arguments to call this method are the following:

• service = ICRMService
  
• linkTableEntityName= "new_new_entity1_new_entity2"
  
• filterEntityName = "new_entity1"
• filterEntityIdName = "new_entity1id"
• filterAttribute = "new_name"
• filterValue = "my entity’s name"
  
• returnedCollectionEntityName = "new_entity2"
• returnedCollectionIdName = "new_entity2id"
• returnedAttibutes = null  //null returns all columns
  or returnedAttributes = new[]{"new_entity2id","new_name"}

/// <summary>
/// Generic Method to retrieve a collection of all DynamicEntities related to another Entity in a Many to Many relationship
/// 
/// 
/// </summary>
/// <param name="service"></param>
/// <param name="linkTableEntityName"></param>
/// <param name="filterEntityName"></param>
/// <param name="filterEntityIdName"></param>
/// <param name="filterValue"></param>
/// <param name="returnedCollectionEntityName"></param>
/// <param name="returnedCollectionEntityIdName"></param>
/// <param name="filterAttribute"></param>
/// <param name="returnedAttributes">if null, AllColumns() is used</param>
/// <returns></returns>
public static BusinessEntityCollection RetrieveEntityCollectionFromManyToMany(ICrmService service, 
    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)service.Execute(request);
    
    return response.BusinessEntityCollection;
}