CRM Linq find all parents that have 0 children

Question

How can I find (preferably using CRM Linq) parent entities that have 0 children. For example how can I find all accounts that have 0 contacts.

Answer 1

If you are going to use the query expression route, which I would recommend then the following code will be useful

var entityAlias = "con";
var query = new QueryExpression
        {
            EntityName = "account",
            ColumnSet = new ColumnSet(true),
            Criteria =
            { 
                FilterOperator = LogicalOperator.And,
                Conditions =
                {
                    new ConditionExpression(entityAlias, "contactid",ConditionOperator.Null)
                }
            }
            LinkEntities =
            {
                new LinkEntity
                {
                    EntityAlias = entityAlias,
                    LinkFromEntityName = "account",
                    LinkFromAttributeName = "accountid",
                    LinkToEntityName = "contact",
                    LinkToAttributeName = "parentcustomerid",
                    Columns = new ColumnSet("parentcustomerid", "contactid"),
                    JoinOperator = JoinOperator.LeftOuter,
                }
            },
        };

var response = service.RetrieveMultiple(query);
var accounts = response.Entities;

In this code I have not limited the columns, this will reduce performance and you should only return the columns needed.

If there is the case for more than 5000 records are going to be returned then you will need to use paging and loop the query to find all the entities, This can be found here:

https://msdn.microsoft.com/en-us/library/gg327917.aspx

However if you are certain you want to use LINQ then you can use the following code:

public static IEnumerable<Account> FindAccountsWithNoContacts()
{
   var contactRelationship = new Relationship("contact_customer_accounts");
   foreach(var account in XrmContext.AccountSet)
   {
      XrmContext.LoadProperty(contactRelationship);
      if(!account.RelatedEntities.ContainsKey(contactRelationship)
      yield return account;

   }  
}

My problem with the LINQ code is that all the enities, both the account and contact entities, will be loaded into memory. With large entity sets this can cause OutOfMemoryException, whereas the query expression route will pass the query to the Dynamics server to execute; which should make the execution of the code faster.