SQL translation

ODBC Driver for Dynamics 365 translates a subset of SQL-92 SELECT statements into FetchXML, the native query language used by Dynamics 365. Data modification operations like UPDATE, INSERT, and DELETE are mapped to corresponding Dynamics 365 Web API calls.

Supported SQL operations

SELECT

SQL SELECT statements are translated into FetchXML queries for remote execution.

UPDATE, INSERT, DELETE

SQL UPDATE, INSERT, and DELETE statements are translated into Dynamics 365 Web API calls that modify data in the corresponding entities.

Requirements for SQL to FetchXML translation

To be translatable into FetchXML, a SELECT statement must meet the following requirements:

SELECT clause

• The SELECT clause can include only column names, or the wildcard asterisk (*).
• SQL functions or expressions are not translatable.

FROM clause

• The FROM clause must contain only one table or joined tables. A JOIN is translatable if:
  • The tables represent related entities in Dynamics 365.
  • The join uses a relationship field (e.g., lookup fields in Dynamics 365) and the ID field.
  • The child entity should appear first in the JOIN clause.

JOIN clause

• INNER JOIN and LEFT OUTER JOIN are supported.
  • INNER JOIN can be used multiple times for joining multiple tables.
  • LEFT JOIN must be used only in cases where it is the only join in the query.
• Join conditions:
  • The join must be on relationship fields and ID fields that define the relationship between entities in Dynamics 365.

WHERE clause

• The WHERE clause should contain only comparisons of columns and constants or the LIKE operator.
• Logical operators AND and OR can be used to combine multiple conditions.
• FetchXML does not restrict column placement in comparison expressions.

ORDER BY and GROUP BY clauses

• ORDER BY and GROUP BY clauses are supported.
  • ORDER BY can be used for sorting results by fields.
  • GROUP BY can be used for grouping by fields.
  • Expressions (like CASE WHEN) are not translatable into FetchXML.

Example: SQL to FetchXML translation

SQL example

SELECT AccountName, ContactName
FROM Account
INNER JOIN Contact ON Account.ContactId = Contact.ContactId
WHERE Account.City = 'New York'
ORDER BY Account.AccountName;

Translated FetchXML query

<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false">
  <entity name="account">
    <attribute name="name" alias="AccountName" />
    <link-entity name="contact" from="contactid" to="contactid" alias="Contact">
      <attribute name="fullname" alias="ContactName" />
    </link-entity>
    <filter type="and">
      <condition attribute="address1_city" operator="eq" value="New York" />
    </filter>
    <order attribute="name" descending="false" />
  </entity>
</fetch>

Performance considerations

FetchXML queries executed remotely provide significantly higher performance than local execution. It’s recommended to use translatable SQL queries for best performance, as this minimizes overhead and maximizes the speed of data retrieval.

Limitations

• Complex SQL expressions (e.g., SQL functions, subqueries, etc.) are not supported and cannot be translated into FetchXML.
• Multi-table Joins: Only relationships defined between entities in Dynamics 365 can be used for joins.
• The ORDER BY and GROUP BY clauses support only sorting and grouping by fields, not by expressions.