January 8, 2022

A look at the SysDa query API

Filed under: 365 for Finance and Operations — Joe Brewer @ 12:31 pm

A new query API was introduced in PU22 which enables a database query to be generated and executed through a lightweight framework. This post is not meant to be a comprehensive analysis of all functionality that this framework provides, rather a look at some of the functionality in order to encourage you to look deeper into the code to help you understand what it can and cannot do.

The official Microsoft documentation can be found here: Access data by using the SysDa classes – Finance & Operations | Dynamics 365 | Microsoft Docs. Of course the best documentation is the code itself and I would recommend using the “find references” and metadata search functionality to find examples and check for any best practice violations when writing your own code as there have been subtle changes changes to the API since it has been released.

The API, which is nicknamed SysDa, is reportedly faster at generating TSQL than the current Query api and also supports set based operations. Set based deletes using multiple are possible with this API which is particularly useful since the new “in” keyword that was introduced a while ago only currently supports enums.  

Microsoft are looking to replace select statements with this new api since it will make queries easier to extend – macros defining field lists or joins to inventDim will become a thing of the past. 


Powered by WordPress