Data Types - MarkMpn/Sql4Cds GitHub Wiki

SQL 4 CDS attempts to implement the same data types, precedence rules, conversions and precision, scale and length rules as SQL Server, with the following exceptions:

Unsupported data types

The following data types are not supported in SQL 4 CDS:

  • varbinary
  • timestamp
  • image
  • hierarchyid
  • User defined types
  • Cursors

XML type

SQL 4 CDS only supports the xml data type in a limited way. The following features are not supported:

EntityReference type

SQL 4 CDS also introduces a custom data type, EntityReference.

Lookup columns in D365 (including Customer and Owner columns) as well as primary ID columns use this type, which combines the unique identifier with the type name and source instance name information.

Select

The lookup column can be selected directly. When using the TDS Endpoint this will produce a uniqueidentifier value, but when the query is executed using Fetch XML instead it will produce an EntityReference value which includes information about the type of the table that is being referenced and other useful information.

Additional virtual attributes are also available. These have the same name as the lookup attribute but with additional suffixes added. These are:

  • name - the display name of the referenced record
  • type - the logical name of the referenced table (only for polymorphic lookups)
  • pid - the partition ID of the referenced record (only for lookups to elastic tables)

Display

When an EntityReference value is included in the results of a query it is shown as a link - double-click the link to open the record in a browser.

Insert & Update

To insert or update a value in a polymorphic lookup column you must supply both the ID and the entity type name the ID relates to. You can do this either by using an EntityReference type, or supplying the two elements separately, e.g.:

INSERT INTO contact (firstname, lastname, parentcustomerid)
SELECT 'Mark', 'Carrington', accountid
FROM   account
WHERE  name = 'Data8'

Because accountid is the primary ID column of the account table, it uses the EntityReference type and contains the ID and type name information required to populate the parentcustomerid polymorphic lookup column in the contact table.

INSERT INTO contact (firstname, lastname, parentcustomerid, parentcustomeridtype)
VALUES ('Mark', 'Carrington', '473EF7A5-35DD-4AD0-802F-48455C9BA05F', 'account')

Because the ID represented by the string literal does not contain any indication of the type of record it references, the parentcustomeridtype column must also be included in the INSERT statement and be set to either account or contact.

You can create an EntityReference value using the CREATELOOKUP() function, e.g.:

INSERT INTO contact (firstname, lastname, parentcustomerid)
VALUES ('Mark', 'Carrington', CREATELOOKUP('account', '473EF7A5-35DD-4AD0-802F-48455C9BA05F'))

Because the CREATELOOKUP() function returns an EntityReference value and not just a GUID, the parentcustomeridtype column does not need to be populated directly.

For references to elastic table records, the partition ID also needs to be included. Similarly to the logical name information for polymorphic lookups you can specify this as a separate column, or combine it with the ID into an EntityReference value using the CREATEELASTICLOOKUP() function:

INSERT INTO contact (firstname, lastname, new_elasticlookupid, new_elasticlookupidpid)
VALUES ('Mark', 'Carrington', '473EF7A5-35DD-4AD0-802F-48455C9BA05F', 'Partition1');

INSERT INTO contact (firstname, lastname, new_elasticlookupid)
VALUES ('Mark', 'Carrington', CREATEELASTICLOOKUP('new_elastictable', '473EF7A5-35DD-4AD0-802F-48455C9BA05F', 'Partition1'));

Conversions

EntityReference has a precedence below all the standard SQL types, and can be implicitly converted to the uniqueidentifier and string types.

For lookup columns that only reference a single entity type, string and uniqueidentifier values can be implicitly converted to the required EntityReference. For polymorphic lookups, or lookups to elastic tables, the CREATELOOKUP() or CREATEELASTICLOOKUP() functions should be used to create the value explicitly, or the associated type and/or pid virtual columns should be set as well.

Multi-select picklist ("Choices") fields

Multi-select picklist fields, known as "Choices" and represented in the SDK by the OptionSetValueCollection type, is handled throughout SQL 4 CDS as an nvarchar(max) column. The column will hold a comma-separated list of integer values listing the selected values, e.g. '1,2,3,4'.