Handing Salesforce SOQL Relationship aka Joins in C#

Salesforce SOQL is not SQL which often causes great frustrations to experienced database developers when they work with it. Being of the generation that worked with Network Data Models and Hierarchical Data Model  - i.e. pre-Relational Databases! (as well as database on Tape!), I actually find a lot of "familiar tones" in SOQL.

Recently I was needing to build a query that in SQL would be:

SELECT Account.Name AS AccountName, 
  Contact.Name, Contact.Email, 
  Case.CaseNumber, Case.CreatedDate, Case.Subject, Case.Status, Case.Resolution__c,  Case.ClosedDate, Case.SuppliedEmail,      Case.SuppliedName 
 FROM Account  JOIN Case ON ... JOIN Contact ON ....
 WHERE Contact.Email in ({0})
  AND Case.CreatedDate = LAST_N_DAYS:366 
   AND Case.Origin != 'Internal' 
   AND Case.OwnerId !=     '00G40000001RTyyEAG' 
   AND Case.RecordTypeId IN ('01240000000UTWP', '01240000000UWxM', '01240000000UWxl', '01240000000UWxb')

The equivalent query in SOQL turned out to be less complex to read (because of the intrinsic joins that exists in Salesforce). Understanding these intrinsic - relationships is essential for writing queries!

SELECT Name, 
  (SELECT Name, Email FROM Contacts), 
  (SELECT CaseNumber, CreatedDate, Subject, Status, Resolution__c,  ClosedDate, SuppliedEmail,SuppliedName FROM Cases
   WHERE CreatedDate = LAST_N_DAYS:366 
   AND Origin != 'Internal' 
   AND OwnerId !=     '00G40000001RTyyEAG' 
   AND RecordTypeId IN ('01240000000UTWP', '01240000000UWxM', '01240000000UWxl', '01240000000UWxb')

  ) 
 FROM Account  
 WHERE ID IN (SELECT AccountId FROM Contact Where Email in ({0}))

The next piece is dealing with the query results. When you execute the above in the developer's console, you will see that the data returns as JSON.
This means a little magic when the response is received. What we have received is a dataset consisting of three linked tables. There are two approaches that seem obvious:
  • Explode the dataset into a single data table
  • Use the dataset as a linked table
In my case, I needed to display the results in a flat grid of cases, so I went down the first path. There's a little magic that needed to be done because one account has many contacts(1:n) and many cases (1:m). This means that you need to walk the cases, and lookup the contact for each case record. As a result, I have to include the ContactId in Cases to flatten the data structure and get the matching contact ID. The result should be a table with m rows.


SELECT Name, 
  (SELECT Id, Name, Email FROM Contacts), 
  (SELECT ContactId, CaseNumber, CreatedDate, Subject, Status, Resolution__c,  ClosedDate, SuppliedEmail,SuppliedName FROM Cases
   WHERE CreatedDate = LAST_N_DAYS:366 
   AND Origin != 'Internal' 
   AND OwnerId !=     '00G40000001RTyyEAG' 
   AND RecordTypeId IN ('01240000000UTWP', '01240000000UWxM', '01240000000UWxl', '01240000000UWxb')

  ) 
 FROM Account  
 WHERE ID IN (SELECT AccountId FROM Contact Where Email in ({0}))

Examining the results reveal that we are dealing with XML and can thus convert them easily via XML activites
I noticed that applying LINQ to SOQL results seems to be difficult for some, but if you work at the XML level, some of the pain disappears quickly.

The code below uses the "fat" XmlDocument (and thus should not be used in real systems) for purposes of illustration:

        public static XmlDocument QueryCases(string myquery)
        {
            XmlDocument dom = new XmlDocument();
            dom.LoadXml("");
            XmlNode recordNode;
            try
            {
                var binding = new AdminConsole.WebSalesForce.SforceService();
                binding.SessionHeaderValue = new AdminConsole.WebSalesForce.SessionHeader();
                var salesforce = SaleforceUrl;
                if (salesforce != null)
                {
                    binding.SessionHeaderValue.sessionId = salesforce.sessionId;
                    binding.Url = salesforce.serverUrl;

                    binding.UseDefaultCredentials = true;
                    var results = binding.query(myquery);
                    var done = false;
                    while (!done && results.size > 0)
                    {
                        dom.DocumentElement.AppendChild(recordNode = dom.CreateElement("record"));
                        foreach (var record in results.records)
                        {
                            foreach (XmlElement key in record.Any)
                            {
                                recordNode.AppendChild(dom.ImportNode(key, true));
                            }
                        }
                        done = results.done;
                    }
                }
            }
            catch (Exception ex)
            {
                AdminConsole.Diagnostics.SplunkLogger.Error(ex);
            }           
            return dom;
        }

Now you have your data is a format that you can proceed easily with via LINQ etc.

Comments

Popular posts from this blog

Yet once more into the breech (of altered programming logic)

Simple WP7 Mango App for Background Tasks, Toast, and Tiles: Code Explanation

How to convert SVG data to a Png Image file Using InkScape