The data can be selected with a SELECT statement, that is SQL-92 standard compliant. SELECT statements are executed in the following way:

1. A SELECT statement is parsed and the list of the tables that are queried is determined.
2. dotConnect for Dynamics 365 (formerly Dynamics CRM) checks if this SELECT statement can be translated to API calls.
3. If this statement can be translated to API calls, it is translated and executed remotely.
4. If the statement cannot be translated to API calls, dotConnect for Dynamics 365 retrieves all the data from the queried tables, caches it in the temporary cache, and executes this query locally.

The local query execution usually takes longer and uses more resources if the data is not cached already, so to optimize application performance it is better to use queries that can be translated to API calls. To learn what queries can be translated to API calls, read the SQL Translation topic.

dotConnect for Dynamics 365 (formerly Dynamics CRM) supports most of SQLite SELECT syntax for local execution.

Disabling Local Execution

While having the ability of local SQL execution is beneficial in most cases, because it allows running far more complex SELECT statements, in some cases it may cause performance issues and be undesirable. For example, if you want to query a small portion of records from a cloud object that contains a huge volume of data and use SELECT features that cannot be directly translated to API calls, dotConnect for Dynamics 365 will query all the data from this object to local cache internally. This may take a huge amount of time, Dynamics 365 API calls, and resources.

For such cases, you may prefer to receive an exception instead of executing SQL locally. You can disable local SQL execution, either for a separate command, or on the connection level. For this you just need to set the either the LocalSQLEngine property of the corresponding command object or the Local SQL Engine connection string parameter to False. In this case, if a SELECT statement cannot be translated to API calls directly, an exception is raised with the message: "Select statement cannot be directly executed by service".