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);
