Sunday, February 23, 2014

All about outer join queries in CRM 2011 and CRM 2013


Outer joins are the way to implement the commonly requested queries in which you are searching records that have no child records (e.g. All accounts without contacts, all users without security roles, etc.). This article explains what the options are for such queries in CRM.
A recurring business scenario is to have a view of records with no child records (display all leads without activities, accounts that have no contacts, etc.) and we also have sometimes a similar requirement but for N:N relationships, for example, provide a list of all users without security roles. Unfortunately achieving these requirements is not as straight forward as we’d like.
Ideally we’d like to be able to use Advanced Find to produce those results. The reality is that Advanced Find does not support outer join constructs so we are not able to build such queries. So what are the options?

1. CRM 2011 or CRM 2013: SQL Report
If you are not in CRM Online you can always build a custom SSRS report and build your SQL query to provide such results. This is a very straight forward query in SQL. For example, if you’d like to retrieve all leads that have no tasks associated the following query will do:
SELECT lead.FullName
FROM Leads as lead
LEFT OUTER JOIN Tasks as ab
ON (lead.leadId  =  ab.RegardingObjectId)
WHERE ab.RegardingObjectId is null



2. CRM 2011: Use the SDK

You can always use the CRM SDK to query records and process the results. However, there is no way to specify an outer join in a QueryExpression, FetchXML or LinqExpression in CRM 2011 in the way that we need. Note that you might find a “LeftOuter” join type in the JoinOperator enum, however, this will not help you in this case. Therefore if you want to retrieve all leads without tasks you’d have to do multiple queries and filter the results manually in your application. For example, if you are looking for all leads without tasks then you’d have to first retrieve all leads and then retrieve all tasks regarding a lead. Finally, you’d then have to merge the results to exclude from your entire lead list those that have a task from your second query. This is less than ideal because if you have thousands of records then making use of paging can be a challenge. It is also not possible to have a view in CRM that would return these results because the CRM platform in CRM 2011 does not support this construct in a single query operation.



3. CRM 2013: Use FetchXML

The good news is that in CRM 2013 the platform has been improved to be able to process these type of queries. Therefore FetchXML capabilities and syntax have been expanded to be able to use outer joins. For our example of retrieving all leads without tasks, this would be the fetchXML you would need:

<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="true">
  <entity name="lead">
    <attribute name="fullname" />
    <link-entity name="task" from="regardingobjectid" to="leadid" alias="ab" link-type="outer">
       <attribute name="regardingobjectid" />
    </link-entity>
    <filter type="and">
        <condition entityname="ab" attribute=" regardingobjectid" operator="null" />
    </filter>
  </entity>
<fetch/>
The bad news is that the Advanced Find interface does not yet support designing these type of queries. However, you can update the fetchXML of your system view or your saved query and it will work (users can access this query via CRM views), nonetheless, they will not be able to design or modify this query in the Advanced Find designer, they can only see the results. To update the FetchXML you would need to export the view in a solution and then modify the FetchXML of your view in the customizations.xml file, then you can import the solution back and voila.


4. CRM 2013: Use QueryExpression

Since we know that besides aggregation capabilities, FetchXML and QueryExpression are equivalent (any FetchXML query can be converted to QueryExpression and vice-versa), then we know that QueryExpression has also been expanded to support outer joins. Here is the CRM 2013 syntax you can use in your QueryExpression for outer joins, the following query returns all leads that have no tasks associated:
QueryExpression qx = new QueryExpression("lead");
qx.ColumnSet.AddColumn("subject");

LinkEntity link = qx.AddLink("task", "leadid", "regardingobjectid", JoinOperator.LeftOuter);
link.Columns.AddColumn("subject");
link.EntityAlias = "tsk";

qx.Criteria = new FilterExpression();
qx.Criteria.AddCondition("tsk", "activityid", ConditionOperator.Null);





Perfect, now what about N:N relationships?

For example, how can you query users that have no security roles associated? (There is an N:N relationship between user and security role). The trick with N:N relationships is that the “intersect” entity is actually an entity that can be queried, therefore you can see N:N relationships as two 1:N relationships:


SystemUser : Role (N:N)

Is the same as the combinaation of these two:


SystemUser : SystemUserRoles (N:1)


SystemUserRoles : Role (1:N)

Every N:N relationship has an intersect entity, in the case of the user : role relationship, the name of the intersect entity is “systemuserroles”. Therefore if you want to query users that have no security roles, this is the same as querying users who have no SystemUserRoles (intersect entity) associated, and that is a simple 1:N relationship. So you could simply do it in this way:

QueryExpression qx = new QueryExpression(SystemUser.EntityLogicalName);
LinkEntity link = qx.AddLink(SystemUserRoles.EntityLogicalName, "systemuserid", "systemuserid", JoinOperator.LeftOuter);
link.Columns.AddColumn("fullname");
link.EntityAlias = "sur";
qx.Criteria = new FilterExpression();
qx.Criteria.AddCondition("sur", "systemuserroleid", ConditionOperator.Null);





7 comments:

  1. Hi Gonzalo,

    Strange behavior when trying to add a customized System View (method discussed in #3) to a dashboard. The view shows up in the normal view but does not show up in the list of views to be selected.

    Is there an XML node/attribute that is required in order to be able to add a System View to a Dashboard?

    Thanks in advance,

    Joe

    ReplyDelete
  2. Gonzalo, if we want to return only Leads that do not have active tasks but may or may not have inactive tasks, would we nest the filter in the or in the ?

    ReplyDelete
  3. Step "2. CRM 2011: Use the SDK" isn't entirely correct (Or maybe put in a better way, your work around isn't the best possible), You can perform a single query with a left outer join, and return back the id of the outer joined table as an aliased attribute. Then use LINQ to objects on the client side to only actually return Entities that don't have the joined table. Using the entities in your example, you'd still be returning all of the leads (So you'd have to consider paging if there are more than 5000), but you wouldn't be returning all of the Tasks, and there is no merge on the client side.

    ReplyDelete
  4. Same question as Joe above.. How does one go about adding one of these tweaked system views to a dashboard. For some reason said left outer join view is missing from the view selector when adding a dashboard component....

    ReplyDelete
  5. http://blog.cobalt.net/blog/building-not-in-queries-using-dynamics-crm-advanced-find

    We made a solution for 2013 and 2015 that does outer joins depending on how you setup your query in advanced find. This allows the user to easily do "not in" queries.

    ReplyDelete
  6. Number 3 is not correct, fetchxml is closed incorrectly, there is no entity called ab in the entire fetchxml. It seems that author has not tested his own code.

    ReplyDelete
    Replies
    1. Thanks there was a typo which is now corrected. I was missing defining the alias "ab"

      Delete