celedonpartners.com

Microsoft Dynamics CRM and Salesforce.com: Query Language Comparison

by | Oct 27, 2015 | Blog, Microsoft Dynamics CRM and Salesforce.com | 2 comments

Microsoft Dynamics CRM and Salesforce both offer proprietary languages to query the respective platform’s databases. In this post, I will provide a comparison of FetchXML (Microsoft) and SOQL (Salesforce) and how they relate to Transact SQL (T-SQL, Microsoft’s proprietary SQL language. Many of the development teams Celedon works with often know T-SQL and need to understand how to translate T-SQL to SOQL or FetchXML. This post will provide examples of some of the basic functionalities more commonly used. This is not a comprehensive guide.

Note: Microsoft Dynamics CRM provides QueryExpression addition to FetchXML. This post does not cover QueryExpression, but Microsoft provides samples and the FetchXMLToQueryExpression class to convert FetchXML to QueryExpression. Additionally, Salesforce’s SOSL language is not covered in this post, but you can read about it here.

T-SQL Expression Comparisons

The following table outlines some of the more common expressions and commands used within a T-SQL statement and how they translate or are accomplished in SOQL and FetchXML.

T-SQL Expression
SOQL Equivalent
FetchXML Equivalent

SELECT

SELECT

Entity element, attribute of name=”entity name”

WHERE

WHERE

Filter and condition elements, attribute of attribute=”column name” and operator=”filter type”

HAVING

HAVING

Not supported

ORDER

ORDER

Order element

GROUP BY

GROUP BY

Attribute element, attribute of groupby=”true”

FROM

FROM

Entity element, attribute of name=”entity”

INNER JOIN

Path expression within SELECT to return values. To filter by another object, include statement in WHERE clause.

Link-entity element, attribute of like-type = “inner”

OUTER JOIN

Option 1 – Path expressions within the SELECT

Option 2 – Nested SOQL statement

Link-entity element, attribute of like-type = “outer”

LEFT / RIGHT ANTI JOIN

Option 1 – Filter by WHERE clause – Column = NULL

Option 2 – WHERE column NOT IN (SELECT column)

Link-entity and filter / condition elements with attribute of operator=”null”

COUNT

Count()

Aggregate =”count” on attribute node

SUM

SUM(Column)

Aggregate =”sum” on attribute node

AVERAGE

AVG(Column)

Aggregate =”avg” on attribute node

MIN

MIN(Column)

Aggregate =”min” on attribute node

MAX

MAX(Column)

Aggregate =”max” on attribute node

IS NULL

= NULL

Condition node, operator=”null”

IS NOT NULL

!= NULL

Condition node, operator=”not-null”

TOP N

LIMIT N

Count=”N” within the root node

Nested Statements

Available. Many of the options available to standard SOQL statements are available to nested options.

Not supported

General Development Limitations

T-SQL is a very powerful language and offers an extensive library of capabilities expected from a mature database query language. It is not reasonable to expect that all or majority of the items available within the T-SQL library can be replicated to the platform languages of SOQL or FetchXML. The core of both languages are designed to execute the general operations needed to perform core business logic or data operations. Advanced data manipulations or calculations may need to be done in the supporting application layers or through external processes. Below are a few examples of items that can be done within T-SQL or the SQL Server platform that neither language can achieve without external processes or additional development:

Function or command
Description

UNION Statement

Both platforms require a single command per execution. SOQL offers nesting statements and FetchXML allows for multiple relationships to be returned in a single

Stored procedures or views

Neither platform offers the ability to group statements into a view or stored procedure like manner, both platforms offer the ability to use class statements within APEX (Salesforce) or C# (Dynamics) to group statements.

Cross database or server joins

Within T-SQL you can call other databases (same or different server). Within FetchXML and SOQL, you are limited to the organization database instantiated.

Cartesian joins

T-SQL allows for Cartesian joins across multiple tables even when a relationship does not exist between the joining objects. SOQL and FetchXML require relationships to exist between the objects being joined and automatically joins the objects by referential integrity.

Join count limit

From SQL 2008 R2 onward, there is virtually an unlimited number of joins that can be contained within a SQL statement (resource dependent). SOQL and FetchXML both have limitations on the number of joins.

 

Statement Examples

This table outlines a few examples translating from a T-SQL command to SOQL and FetchXML. There are many more examples and often times many ways to achieve the object. It is beyond the scope of this post to list all possible solutions.

Description
T-SQL
SOQL
FetchXML

Retrieve accounts where the number of employees column Is not null

SELECT ...

FROM dbo.Account

WHERE NumberOfEmployees IS NOT NULL

SELECT ...

FROM Account

WHERE NumberOfEmployees != NULL

<fetch mapping="logical" version="1.0"><entity name="account"><attribute name="accountId" /<filter><condition attribute="ID" operator="not-null" /></filter></entity></fetch>

Join the contact and account tables. Return values from both and filter where there are matching records.

SELECT...

FROM Contact Con

INNER JOIN Account Acct

ON Con.AccountId = Acct.Id

SELECT ...

FROM Contact

WHERE AccountId IN (SELECT Id FROM Account)

<fetch mapping="logical" version="1.0"><entity name="Contact"><attribute name="FirstName" /><link-entity name="Account" from="Id" to="AccountId" alias="Acct" link-type="inner"><attribute name="Name" /></link-entity></entity></fetch>

Outer join – return all records from the contact and only those that match from the account.

SELECT...

FROM Contact Con

LEFT OUTER JOIN Account Acct ON Con.AccountId = Acct.Id

SELECT Account.Name, FirstName FROM Contact

<fetch mapping="logical" version="1.0"><entity name="Contact"><attribute name="FirstName" /><link-entity name="Account" from="Id" to="AccountId" alias="Acct" link-type="outer"><attribute name="Name" /></link-entity></entity></fetch>

Anti Join – return only those records from the contact table where there are no matching account records.

SELECT...

FROM Contact Con

LEFT JOIN Account Acct

ON Con.AccountId = Acct.Id

WHERE Acct.Id IS NULL

SELECT ...FROM Contact Con

WHERE Acct.Id NOT IN (SELECT Id FROM Account)

 <fetch version=”1.0″ output-format=”xml-platform” mapping=”logical” distinct=”true”> <entity name=”contact”> <link-entity name=”account” from=”accountid” to=”accountid” alias=”ab” link-type=”outer”> <attribute name=”accountid” /> </link-entity> <filter type=”and”> <condition entityname=”ab” attribute=”accountid” operator=”null” /> </filter> </entity> <fetch/>

Count of all account records

SELECT count(AccountNumber)

FROM Account

SELECT count()

FROM Account

<fetch mapping="logical" aggregate="true" version="1.0"><entity name="account"><attribute name="accountId" alias="count_accountId" aggregate="countcolumn" /></entity></fetch>

Return the top 100 records from the account table.

SELECT TOP 100

...

FROM Account

SELECT ...

FROM Account

LIMIT  100

<fetch mapping=”logical” count=”100″ version=”1.0″><entity name=”Account”><attribute name=”AccountNumber” /></entity></fetch>

As a general rule of thumb, I have been able to achieve the majority of what is required to meet the business objectives with the query languages provided by Microsoft and Salesforce. There are times that creativity is required to complete the task and it might have been faster had T-SQL been an option, but it is important to recognize the strengths that each language is bringing to their respective platform.

Some great references

Happy Coding!

2 Comments

    • Chris Scott
      Chris Scott on 10/27/2015 at 5:43 pm

      Thank you Natraj! This is new and am very pleased to see this addition. It was a gap in FetchXML for quite a while.