Dataverse.Sql 5.3.1.510

Dataverse.Sql

This project uses the code of Mark Carrington's sole SQL 4 CDS engine which originally targets only full framework. The original engine and their tests were ported to .NET5 (Core). Some modifications were necassary since Dataverse.Sql uses the Microsoft.PowerPlatform.Dataverse.Client but the original namespace is left as is.

Dataverse.Sql wraps around the SQL 4 CDS engine and exposes the methods to retrieve data from your Dataverse Environment via SQL. It also provides the opportunity to set the engine options in a settings file (dataversesql.json) which is delivered with the package.

This library will continuously be synchonized with changes in the SQL 4 CDS engine. Version numbers will be accordingly updated. The Revision number of the Dataverse.Sql version will hereby be taken from the Version number of the used Dataverse Client (currently using 0.5.10 which becomes .510)

The SQL engine is able to convert the provided SQL query into the corresponding FetchXML syntax and allows the associated query to be executed, including the following types of query:

• SELECT
• INSERT
• UPDATE
• DELETE

For example:

-- Get contact details
SELECT
    c.firstname,
    c.lastname,
    a.telephone1
FROM
    contact AS c
    JOIN account AS a ON (c.parentcustomerid = a.accountid)
WHERE
    c.firstname = 'Mark'
    AND a.statecode = 0
ORDER BY
    c.createdon DESC

-- Deactivate contacts without an email address
UPDATE
    contact
SET
    statecode = 1, statuscode = 2
WHERE
    emailaddress1 IS NULL

The engine converts all the SQL syntax that has a direct equivalent in FetchXML. It also attempts to support some more SQL features that do not have an equivalent in FetchXML, such as calculated fields, HAVING clauses and more.

When executing a query it will take into account specific Dataverse features to improve the performance or results compared to simply executing the FetchXML directly, e.g.:

• Faster SELECT count(*) FROM entity query execution using RetrieveTotalRecordCountRequest
• Automatically retrieving multiple pages of large result sets
• Work around AggregateQueryRecordLimit errors by retrieving all the individual records and applying the aggregation in-memory.

As well as querying data with FetchXML, Dataverse.Sql can also query metadata by translating the SQL query into a RetrieveMetadataChangesRequest or RetrieveAllOptionSetsRequest:

-- Find attributes without a description
SELECT
    entity.logicalname,
    attribute.logicalname
FROM
    metadata.entity
    JOIN metadata.attribute ON (entity.logicalname = attribute.entitylogicalname)
WHERE  
    attribute.description IS NULL