CDS T-SQL endpoint pt 5 - EntityFramework / ORM

Now I can reliably connect to the CDS T-SQL endpoint and execute queries against it, I want to get the data into some objects for me to use. I currently use EntityFramework and Dapper for this in other projects, so I’ll take a look at those two.

Entity Framework

Update 2020-05-24: Microsoft have confirmed that Entity Framework is not supported with CDS, which is disappointing but understandable considering it’s not a direct real SQL connection. I’ve left the details of my investigations below for interest, but if you’re looking for an alternative try skipping on down to the section on Dapper.

Update 2024-09-21: Entity Framework Core is supported with Dataverse, using the reverse engineering tools included with EF Core 8.

To get started, in Visual Studio I selected Add New Item and selected “ADO.NET Entity Data Model” and used the “EF Designer from database” option. The first thing you’ve got to do here is set up the connection. With my connection details in hand I split them up to fit the fields on the connection dialog – server name is contoso.crm.dynamics.com,5558, authentication method is Active Directory Password Authentication and entered my username and password. Click Test Connection and it says it’s succeeded, but I can’t click OK just yet:

So far I haven’t selected a database name. No matter, I’ll just click this drop down and… oh, there’s nothing there. OK, I’ve already seen in SSMS that the database name is the same as the organization name (e.g. contoso, or more likely org12345678), so I’ll type this in instead. Great, the OK button is enabled. Click that and…

It’s obviously connected, but it looks like the EF tooling is now sending a SQL command to CDS that CDS doesn’t support.

Next I tried connecting to a regular SQL Server database to create a model, then switched the connection string over to point to CDS and add the tables I want in that way. This also failed, initially giving me the same error as before then:

So, I don’t think this is ready to go with Entity Framework just yet. But I’m still curious what it’s failing on. From the last error message the INFORMATION_SCHEMA.VIEWS view is not available, but what other query didn’t it like before when I got the “Unsupported Sql query” error? Is there something in there that I need to be aware of for my own queries?

I tried setting up a new model against an actual SQL Server database again, but this time with the profiler open so I could see what queries the Entity Framework designer was running, then ran those same queries myself one at a time against CDS in SSMS and found a few bugs in how it handles them.

CASE expressions

The first query it runs is:

SELECT CASE WHEN Convert(varchar, SERVERPROPERTY(N'EDITION')) = 'SQL Data Services' OR SERVERPROPERTY(N'EngineEdition') = 5 THEN 1 ELSE 0 END

presumably to check if the SQL Server is a supported version. If I run this against SQL Server I get a result of 0. CDS however gives the individual server properties as separate columns and not the calculated value from the CASE expression.

I’ve checked a similar query against regular CDS data and it did work as expected, so I’m not sure why the first one here is failing:

Listing Databases

Next up it runs one query to get the list of available databases, and another to get the current database name:

SELECT name FROM master.dbo.sysdatabases WHERE HAS_DBACCESS(name) = 1 ORDER BY name

SELECT DB_NAME()

The first of these gives the error:

Msg 40000, Level 16, State 1, Line 6
Invalid table name sysdatabases. Line:2, Position:18

and the second one gives:

Msg 40000, Level 16, State 1, Line 9
Unsupported Sql query.

OK, we’ve found the source of our error message! Hopefully it should be possible to get this one fixed before this feature is out of preview.

Version Information

Carrying on though to see if there are any more queries that might cause it a problem, it next runs SET LOCK_TIMEOUT 30000 which it handles fine, then another version check query that has the same bug as before.

Next it runs this query to get a whole bunch of information about the server and connection environment:

DECLARE @edition sysname;
SET @edition = cast(SERVERPROPERTY(N'EDITION') as sysname);
SELECT case when @edition = N'SQL Azure' then 2 else 1 end as 'DatabaseEngineType',
SERVERPROPERTY('EngineEdition') AS DatabaseEngineEdition,
SERVERPROPERTY('ProductVersion') AS ProductVersion,
@@MICROSOFTVERSION AS MicrosoftVersion;
select host_platform from sys.dm_os_host_info
if @edition = N'SQL Azure' 
  select 'TCP' as ConnectionProtocol
else
  exec ('select CONVERT(nvarchar(40),CONNECTIONPROPERTY(''net_transport'')) as ConnectionProtocol')

This fails because it doesn’t understand the @@MICROSOFTVERSION variable or the sys.dm_os_host_info DMV.

Database listing

Next up it tries to switch to the requested database using

use [contoso];

Surprisingly this also fails with:

Msg 40000, Level 16, State 1, Line 28
Unsupported Sql query.

Next it tries to determine if this is a contained database using:

if (db_id() = 1)
begin
-- contained auth is 0 when connected to master
select 0
end
else
begin
-- need dynamic sql so that we compile this query only when we know resource db is available
exec('select case when authenticating_database_id = 1 then 0 else 1 end from
sys.dm_exec_sessions where session_id = @@SPID')
end

Just about everything about this query fails unfortunately. db_id(), SELECT 0 and sys.dm_exec_sessions all produce errors.

Next it tries to check if we have access to the requested database using:

SELECT CASE WHEN has_dbaccess(N'contoso') = 1 THEN 'true' ELSE 'false' END

This gives another Unsupported Sql query error. Just to check, I simplified this to:

SELECT CASE WHEN 1 = 1 THEN 'true' ELSE 'false' END

and this also fails. Giving it an alias does let it run, but always produces NULL, so there’s definitely something broken with how it handles CASE statements.

Lastly there’s a couple more queries to get some more environmental information:

SELECT
user_name() AS [UserName],
(select schema_name()) AS [DefaultSchema]

SELECT
CAST(
        serverproperty(N'Servername')
       AS sysname) AS [Name],
CAST(
        ISNULL(serverproperty(N'instancename'),N'')
       AS sysname) AS [InstanceName]

Both of these queries run, but return NULL for everything except the server name. I feel it should be able to return useful values for the first query, and even if the instancename is NULL then it really should have been converted to empty string by the ISNULL function.

Having looked through the queries that the tooling is generating here I’m not altogether surprised that there was something in there that failed. It seems to me though that there are some key things here that really need to be addressed before this is out of preview, especially the handling of CASE expressions, USE [dbname] statements that might be frequently generated by existing code, un-aliased expressions like SELECT 1 + 1 and common functions like ISNULL and COALESCE that run without error but don’t produce the expected result.

Dapper

Dapper is a much simpler system than Entity Framework. You’re still in control of the SQL that gets executed, but Dapper handles mapping the query results into your object model.

Taking my example from earlier I can easily execute:

var leads = con.Query<Lead>("SELECT leadid, firstname, lastname, companyname, emailaddress1 FROM lead");

and I get a collection of Lead objects. Of course, I first have to have defined the Lead class somewhere, and this is where Daryl LaBar’s Early Bound Generator XrmToolBox tool comes in to quickly generate all these classes.

Things get more complicated if I want to include a lookup value in my query. For example, if I add ownerid to my previous example:

var leads = con.Query<Lead>("SELECT leadid, firstname, lastname, companyname, emailaddress1, ownerid FROM lead");

This fails with the error Error paring column 6 (ownerid=<guid>). This is because the ownerid property on the generated class is an EntityReference which Dapper can’t create from the Guid that I’m getting back from SQL.

I can get around this using the mapping functionality of Dapper, but this will get tedious very quickly. However, it does bring me onto a minor gripe I have with the data available from the SQL endpoint:

Polymorphic fields

The ownerid field is a polymorphic lookup – that is, it can point to more than one other type of entity. In this case it can point to a user or a team. Other examples in CDS are the customer lookups that can be either an account or a contact, and the Regarding fields on activities, amongst others.

When you use the IOrganizationService to retrieve data, you get these lookup fields back as an EntityReference object that includes the ID, type and name of the related entity.

In SQL it gets a little more complicated. According to the metadata of the table, the only column available is ownerid. If you actually run SELECT * FROM lead and see what columns you get back, you also get owneriddsc, owneridname, owneridtype and owneridyominame.

What does it all mean?

I had to do some digging to work out what the dsc field was. It seems that this relates to a feature from CRM 4.0 which allowed you to “soft-delete” a record, and this would contain the “deletion state code” (hence DSC) of the related record. This feature was dropped in CRM 2011, so quite why it’s exposed in a brand new feature in 2020 I’m not sure.

The name and yominame fields give us the display name of the related record without having to join to the target table. This can be helpful, and matches with the EntityReference.Name property we would get from the IOrganizationService.

The one I have a problem with is owneridtype. This gives us not the entity type name as EntityReference.LogicalName would do, but the object type code. In this case I see 8, which maps to the User entity.

The object type code is not nice to use. The documentation explicitly says not to use it, and to use the schema name in preference. Unless it’s a number I’ve seen plenty of times before I’ve got to do some searching to find out what it means. Any references to it in an AppSource submission would be rejected.

So, pretty please, can this change to give the logical name instead before this is out of preview?

Can it be easier to use?

With the Dapper & early bound model, it’s painful to consume these lookup fields as they need manually mapping.

What would make life a lot easier would be if the early bound class had separate properties for lookup field’s ID and Name properties. Something like:

class LeadSql : Lead
{
  public new Guid? OwnerId
  {
    get => base.OwnerId?.Id;
    set
    {
      if (value == null)
      {
        base.OwnerId = null;
        return;
      }

      if (base.OwnerId == null)
        base.OwnerId = new EntityReference();

      base.OwnerId.Id = value.Value;
    }
  }

  public string OwnerIdType
  {
    get => base.OwnerId?.LogicalName;
    set
    {
      if (value == null)
      {
        if (value == null)
        {
          base.OwnerId = null;
          return;
        }

        if (base.OwnerId == null)
          base.OwnerId = new EntityReference();

        base.OwnerId.LogicalName = value;
      }
    }
  }
}

CDS T-SQL endpoint pt 5 – EntityFramework / ORM