Understanding CRM LinkEntities

13Jul10

Anyone that has spent some time developing solutions for Microsoft CRM should be familiar at least with grabbing an entity instance or collection of instances from CRM using CrmService.Retrieve() or CrmSevice.RetrieveMultiple(). It is conceptually straightforward to pass CRM an ID and get the corresponding instance back. While a little more advanced, using FilterExpressions is still pretty easy to visualize, since we are dealing with properties of a single entity. Things start to get a little more complex if we’d like to involve more than a single entity in our selection criteria however.

In CRM, in order to perform a query that takes more than a single entity into consideration we need to use a LinkEntity. Notice that I didn’t say that we’d like to perform a query that returns more than a single entity type, since CRM QueryExpression queries are limited to returning a single type of entity. We can get around this limitation by using the FetchXml interface, but the data is returned as Xml rather than BusinessEntity instances, so we would have to pull out what we need by hand.

One of the things that makes LinkEntities confusing initially is the idea that we are traversing the relationship in a particular direction — that is, there is a ‘from’ entity and a ‘to’ entity. In terms of a typical SQL join, we are dealing with sets, so we can express the relation from either end. That is, we could write a query that returns a parent row along with the related detail rows using either of the following SQL statements:

select detail.* from parent 
inner join detail
on detail.parentid = parent.id
where parent.id = 123

select detail.* from detail
inner join parent 
on detail.parentid = parent.id
where parent.id = 123

However, in CRM, we must start the relation with the entity that we wish to return as the result. So, if we’d like to return the detail entities for a particular parent entity, we’d need to traverse the relationship from detail to parent rather than parent to detail.

Let’s try to write a LinkEntity expression using the example given above. We know that the corresponding SQL statement would be of the second form in the code listing. The LinkEntity is constructed using the entity name and field on each side of the SQL ‘on’ clause. The only thing to watch out for is the order — that is, which entity is used as the ‘from’ side and which is the ‘to’ side. Since we are going from detail to parent given that we’d like to return the detail entities as the result, we’d use SQL of the form:

detail.parentid = parent.id

which corresponds to the following LinkEntity construction:

LinkEntity linkEntity = new LinkEntity();			
linkEntity.LinkFromEntityName = "detail";
linkEntity.LinkFromAttributeName = "parentid";
linkEntity.LinkToEntityName = "parent";
linkEntity.LinkToAttributeName = "id";
linkEntity.JoinOperator = JoinOperator.Inner;

That seemed pretty straightforward. There is one more thing that we must do in order to construct a useful query — add the condition that selects which parent instance we are interested in. I’ve covered FilterConditions in a previous post, and we are going to construct a FilterCondition in just the same way. Here is the code:

FilterExpression filter = new FilterExpression();
filter.FilterOperator = LogicalOperator.And;

ConditionExpression condition = new ConditionExpression();
condition.AttributeName = "id";
condition.Operator = ConditionOperator.Equal;
condition.Values = new int[] { 123 };

filter.Conditions = new ConditionExpression[] { condition };

Now that we have the filter, the only tricky part is where to put it. Since FilterExpressions don’t specify the entity to which they apply, the placement of the expression is critical. Since we want to apply this filter to the parent entity, and the parent entity is the ‘from’ entity of the LinkCriteria, we’ll need to attach it to the LinkCriteria of the LinkEntity rather than the FilterCriteria of the QueryExpression like this:

linkEntity.LinkCriteria = filter;

The full query code looks like this, including the creating of the QueryExpression:

LinkEntity linkEntity = new LinkEntity();			
linkEntity.LinkFromEntityName = "detail";
linkEntity.LinkFromAttributeName = "parentid";
linkEntity.LinkToEntityName = "parent";
linkEntity.LinkToAttributeName = "id";
linkEntity.JoinOperator = JoinOperator.Inner;

FilterExpression filter = new FilterExpression();
filter.FilterOperator = LogicalOperator.And;

ConditionExpression condition = new ConditionExpression();
condition.AttributeName = "id";
condition.Operator = ConditionOperator.Equal;
condition.Values = new int[] { 123 };

filter.Conditions = new ConditionExpression[] { condition };

linkEntity.LinkCriteria = filter;

QueryExpression query = new QueryExpression( in_fromEntity );
query.LinkEntities.Add( linkEntity );
query.ColumnSet = new AllColumns();

Hopefully this post clears up some of the confusing aspects of using LinkEntities. I have spent a long time trying to track down mysterious bugs related to getting the entity order wrong or attaching the FilterCriteria in the wrong place. The documentation doesn’t explain this well in my opinion, so hopefully this saves you some grief.

Advertisements


5 Responses to “Understanding CRM LinkEntities”

  1. 1 Yusuf

    Thanks, Topic is well explained .Keep posting …

  2. 2 Phil

    Brilliant. Thank you so much for this post.
    Explains Link Entities much clearer than the SDK Documentation.

    • 3 Phil

      One question, you mention that it is important to please the filter expression in the correct place.
      To filter the parent, you assign the condition expression to the LinkEntity.LinkCriteria. How would you add a filter to the detail entity aswell?

      • 4 newcome

        Phil, you should be able to add a FilterExpression directly to the QueryExpression using QueryExpression.Criteria. The key is to keep in mind which entity you are dealing with at a particular place in the query object tree. Since in the example above, the top level is the detail entity, which I called the “From” entity (the hypothetical in_fromEntity method parameter) and that is the entity that possesses the field that you want to filter on, you’d add it there.

        I think that this API is super-confusing. I wrote an API wrapper called CRMQuery to make common queries easy to write. You can check it out on GitHub. https://github.com/dnewcome/crmQuery

        It won’t work for every query but I’ve found that it can simplify the code a lot in many cases.

        Good luck.

      • 5 Phil

        Thanks newcome,

        I managed to figure it out just before I saw your reply.
        It certainly is quite confusing but im getting the hang of it.
        Will take a look at your wrapper.

        Thanks again.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


%d bloggers like this: