Creating a bot pt. 2 – Proactive Messages

The key thing I want to do with my bot is to push messages out to users when something happens in D365, not just in response to the user sending a message to the bot. This was the part I was unsure of, but a bit of searching gives me the key phrase Proactive Messages. Apparently this is what I need to implement.

Required Information

From a bit of research it seems I need a few IDs to be able to send a proactive message:

  • my bot
  • the user to send the message to
  • the Azure Active Directory tenant the user is in

I already know the ID of my bot, but I need to capture the user and tenant IDs. The user ID is not the user’s login name (user@contoso.com) but an apparently random ID assigned by Teams (29:cXh2ZnlWREI2VgpMY3o3UFdGZU9xCnJMUzBScUJqNWIKSlVrZkpaanh0cgpOMEtXVThVSDl)

I can grab this user ID and the tenant ID from the events in my bot. I finally need to write some code!

Editing the bot code

In part 1 I created the bot using the Echo Bot sample, which deployed a fully-built bot for me. Now I need to start customising this bot, so I need to grab that code.

Back in the Azure Portal I just have to open the Web App Bot resource I created earlier, click on the Build item and then on “Download Bot source code”. That gives me a zip file – just unzip it and open the solution inside it in Visual Studio.

The core code that handles the incoming messages in this bot is:

protected override async Task OnMessageActivityAsync(ITurnContext<IMessageActivity> turnContext, CancellationToken cancellationToken)
{
    var replyText = $"Echo: {turnContext.Activity.Text}";
    await turnContext.SendActivityAsync(MessageFactory.Text(replyText, replyText), cancellationToken);
}

To get started I just want to quickly prove I can get the required information. I’m going to replace the current “Echo” response with all these IDs:

protected override async Task OnMessageActivityAsync(ITurnContext<IMessageActivity> turnContext, CancellationToken cancellationToken)
{
    var teamConversationData = turnContext.Activity.GetChannelData<TeamsChannelData>();

    await turnContext.SendActivityAsync(MessageFactory.Text("Your ID is " + turnContext.Activity.From.Id), cancellationToken);

    await turnContext.SendActivityAsync(MessageFactory.Text("My ID is " + turnContext.Activity.Recipient.Id), cancellationToken);

    await turnContext.SendActivityAsync(MessageFactory.Text("Tenant ID is " + teamConversationData.Tenant.Id));
}

The ID of the user and the bot is available in the standard turnContext parameter, but the tenant ID is specific to the Teams channel so we need to get that out of the TeamsChannelData class. This is a good start:

My bot ID appears to be the same ID I used when I created the Teams app package, but with the prefix 28:. The Azure Active Directory tenant ID is the same ID I’m used to seeing in various situations now, but the user ID appears completely random.

Sending a Proactive Message

Now I’ve got the required details I want to set up a simple console app. This should demonstrate I can send a message before I jump into building anything into D365. Following the docs again and using some hard-coded IDs for now just to get started I built:

async static Task Main(string[] args)
{
    const string url = "https://mybotapp.azurewebsites.net/";
    const string appId = "<guid>";
    const string appPassword = "<password>";

    MicrosoftAppCredentials.TrustServiceUrl(url);

    var client = new ConnectorClient(new Uri(url), appId, appPassword);

    // Create or get existing chat conversation with user
    var parameters = new ConversationParameters
    {
        Bot = new ChannelAccount("28:<guid>"),
        Members = new[] { new ChannelAccount("29:<user id copied from earlier chat>") },
        ChannelData = new TeamsChannelData
        {
            Tenant = new TenantInfo("<tenant id copied from earlier chat>"),
        },
    };

    var response = await client.Conversations.CreateConversationAsync(parameters);

    // Construct the message to post to conversation
    var newActivity = new Activity
    {
        Text = "Hello",
        Type = ActivityTypes.Message,
        Conversation = new ConversationAccount
        {
            Id = response.Id
        },
    };

    // Post the message to chat conversation with user
    await client.Conversations.SendToConversationAsync(response.Id, newActivity);
}

Aaaannnddd… I get the error:

Operation returned an invalid status code 'NotFound'

After some frustrated searching I found that the URL I needed to connect to was not that of my bot itself, but a channel-specific URL provided by the Azure Bot Framework. I found some suggested URLs to use for different channels, but the way I went for was to include this URL in my bot response by adding in this line:

await turnContext.SendActivityAsync(MessageFactory.Text("Service URL is " + turnContext.Activity.ServiceUrl));

This gave me the URL https://smba.trafficmanager.net/emea/. I assume that will change depending on the region you’re in, so it does seem best to get this value dynamically in the same way as the other IDs rather than hard-coding it.

With that hurdle crossed I can now run my console app and get an alert in Teams!

Everything seems to work just how I’d hope at this point – I get the popup alert message with the contents of my message, the Teams app in the task bar is flashing to show I’ve got a message, and it appears in the timeline of my conversation when I open Teams. Great!

This seems to be all I need to get messages sent out to me, but currently I’m skipping a load of complexity by hard-coding my own user IDs. I need to start making this more dynamic so I can send messages to others too, which I’ll look at next time.

Creating a bot pt. 1 – Getting Started

Before I make any progress with the real logic of my new bot, I need to get the basics set up.

I first need to create a simple bot and be able to interact with it from Teams. This is where the whole Azure Bot Framework really shines – I need to know just about nothing about all these different moving parts to get started.

First, in the Azure portal, I created a new resource group to keep everything I’m going to need in one place. Then I click Add Resource and just type in “Bot”. You get quite a few options to choose from:

Creating the Bot

I want to build my own bot rather than starting from one of the third-party ones on offer, so I’m going to pick between these first two options I’ve highlighted. After a quick bit of research, the different options are:

  • Web App Bot hosts the bot within an automatically-created App Service resource as well as registering it with the Azure Bot Framework
  • Bot Channels Registration registers a bot you’ve already created and hosted elsewhere

I’m all in favour of not having to worry about the underlying infrastructure wherever I can, so I’ve gone for the Web App Bot option.

I’ve got to pick a few standard options when I’m creating the bot – all the usual things of what to call it and what region to host it in, a pricing tier (the free one will be fine for now), and, handily, a template for my bot. I’ve gone with the simple Echo bot for now, as I’m not planning on using natural language input from users at least to start with.

After creating the bot I’ve ended up with three new resources in my resource group:

  • App Service Plan to run my bot in
  • App Service to host my bot
  • Web App Bot to register my bot with the Bot Framework

It’s worth noting that although I used the free tier for the bot registration, the App Service Plan it’s created to host it is at the S1 pricing tier, which is going to cost me about £32 per month. Not the end of the world for a real scenario, but I’m only playing for the moment so the next thing I’m going to do is scale that down to the F1 (free) tier.

Quick Test

I haven’t written any code yet but I’d like to see it working before I start to change anything. The simplest way is to head over to the Web App Bot resource and click on the “Test in Web Chat” option. This gives me a quick and easy way to interact with my bot.

As expected, I can type in messages and the sample bot simply echos them back to me:

Great, the sample code is working! Now to link it to Teams.

Integrating with Teams

First off we have to tell the bot what channels we want to work with. These are the end-user messaging apps it’s going to work with. We’ve already seen it working with the testing web-chat channel.

To get started, click on the Channels option within the Web App Bot resource, then on the Teams icon. I’m not using the government cloud so I just leave the default options selected and click Save. Agree to the terms, and I’ve got my bot linked to Teams. Great! Now what?

Creating a Teams App Package

This is where it got confusing for me. In order to use by bot from Teams, apparently I have to create an app package for it. I think it’s the terminology that gets me here – I’m creating a bot, but now I have to create an app instead.

Once I decided to stop worrying about the words and get on with it though, it wasn’t too bad. I used the App Studio app in Teams to create the package with some basic details and add my bot to it. The key bit of information I needed here was the bot id (saved as the MicrosoftAppId in the Configuration section of the Web App Bot resource). All the other bot details were pulled in for me automatically after I entered this, which is very nice!

The other thing was to understand the scope of my bot. When you add the bot to the app package you have to select from Personal, Team or Group Chat scopes. In my case I think everyone is going to have their own personal conversations with the bot so I’m going to leave this on the default – Personal. I assume I can come back and change this later if I need to though.

Installing & Testing

Now I’ve created my app package, I just need to install it. This is where I hit a bit of friction. I expected to be able to use the “Test and distribute” section within App Studio to do this, but I kept getting unhelpful permissions-related errors.

Eventually I found I had to enable an option in the Teams admin centre to let me do this. Under Teams apps > Setup policies, edit the relevant policy and turn on the “Upload custom apps” option.

Now the frustrating part – it still doesn’t work! Teams policies seem to take some time to apply, so come back to it later, go to App Studio and click that Install button again. If everything’s going smoothly your bot should now appear as a new app in the left hand menu, and you can start chatting to it in Teams!

Hooray! I still haven’t written a single line of code, but I’ve got a fully operational bot that I can chat to from Teams. Brilliant!

That’s enough for today, next time I’ll take a look at how I can get my bot to push out messages to Teams rather than just respond when the user sends a message.

My first bot – D365 Notifications

At Data8 we’ve successfully used posts on CRM records for some time to share updates. The problem was that people didn’t know when someone had written a post, so they would resort to email instead.

To avoid this problem I set up a service to automatically email people that might be interested in a record whenever someone posted on it. They could read the new post in their email, and click on a link to take them to the record if they wanted to reply.

Add a post on a record in CRM
An automatic email is sent out with the details to anyone who might be interested

Teams

This works OK, but as we’ve now moved to Teams for most communication it seems sensible to move this over as well. A bot that could automatically send people a notification whenever something interesting happens in D365 (new posts in this case, but I could see it being wider in future) seems ideal.

After a quick bit of searching it seems this should be possible with the Azure Bot Framework and Teams. I’ve never written a bot before, though I’ve attended some great sessions by Artur Zielinski that made me think it shouldn’t be too hard to get started.

I’m still investigating exactly how it all works, but the general picture in my head right now is something like:

My general plan is a plugin in D365, which will trigger when a post is added. That will send a notification to the bot, which will create a message to send on to the bot framework. That in turn sends it on to Teams, so the user can see it. That replicates what we currently have via email, but ideally we’ll also get the opposite direction working too. When the user replies to a Teams message it gets passed all the way back to being saved as a reply to the post in D365.

I’ll be working on this over the next few weeks – check back to see how I’m doing!

SQL 4 CDS 2.1.0 – the T-SQL edition

Since I’ve been doing some investigation into the new preview T-SQL endpoint for CDS recently, I’ve decided to integrate it into SQL 4 CDS. Update to 2.1.0 to get:

Enable/Disable T-SQL Endpoint

The current official way to enable or disable the T-SQL endpoint is rather awkward, and doesn’t seem to work for me (though it does for others).

SQL 4 CDS 2.1.0 introduces a new T-SQL Endpoint item in the Object Explorer pane (if your instance is on a high-enough version). Right-click on it to enable or disable it.

Run queries using T-SQL Endpoint

Once enabled you can run SELECT queries using SQL Server Management Studio, but you can now get SQL 4 CDS to use the same endpoint for SELECT queries while still being able to run UPDATE / INSERT / DELETE statements as well.

To enable this option, open the settings dialog and tick the “Use T-SQL Endpoint where possible” option.

Now when you run a SELECT query in an instance where the T-SQL endpoint is enabled, SQL 4 CDS will use it to execute the query. Other queries will continue to work as well as they’ll be sent to the standard CDS API.

For this to work you’ll need to use an OAuth method of authenticating with CDS. You’ll know if you’ve done this as the T-SQL Endpoint item in the Object Explorer pane will show a green icon. If it’s got a yellow warning triangle, you’re using another authentication method and SQL 4 CDS won’t use the T-SQL endpoint.

When you use this new option you can run any SELECT query that the T-SQL endpoint supports, whether SQL 4 CDS can convert it to FetchXML or not, giving you all the flexibility it offers plus the support for INSERT / UPDATE / DELETE that SQL 4 CDS supports.

CDS T-SQL Endpoint pt 8 – Security

The security model is a key advantage of CDS over alternatives such as Sharepoint or SQL. Data is one of any organisation’s most precious assets, and the built-in ability to restrict access to records based on ownership, sharing, teams, hierarchy and even per-field makes this an incredibly powerful feature.

All these security options take effect at the lowest levels of the CDS API, so however you access the data the same rules are automatically applied. So long as you’ve got your security roles set up correctly there should be no difference between whether your users use your Dynamics 365 app, XrmToolBox or even start building their own Power Apps.

Of course, the T-SQL endpoint provides a new way to get at your data, so we need to be sure that the same security rules apply here too.

Authentication

We’ve already seen that any user needs to authenticate with the T-SQL endpoint with the same details they use for any existing app. It knows who is connected and should be able to apply the same security rules.

Security Roles

Roles are the starting point for CDS data security. They identify the basic set of records that the user can access based on who owns the record. These can be very complex, but for now T-SQL is read-only so we only need to worry about the read permissions.

I’ve created a few account records and assigned them to one user. When I log in as another user and run

SELECT name, accountnumber FROM account

as another user with only user-level read permission, it sees no records. So far so good.

If I now update the security role to give organisation-wide read permission and re-run the query, all the accounts are returned. No need to disconnect & reconnect, all the changes are applied immediately. Great!

Field Level Security

While security roles define which records the user can access, field level security defines which fields within those records they can access. By default every field is readable & writable, but you can lock down specific fields to be read-only or even unreadable to all but specific users or teams.

To test this out I applied field level security to the accountnumber field on the account record. As a system administrator I automatically get full access to any secured fields. My other user account however does not. This time when I run the same query I get:

Results for system administrator

Results from normal user

So field level security rules are also applied as expected, and an unreadable field will be returned as NULL.

Teams

Rather than working with individual users, it can be more efficient to work with teams. This allows you to assign permissions to a whole set of users at once. To complicate matters there are two different types of teams – Owner Teams and Access Teams. I won’t go into the differences here, but I will give both of them a go.

Owner Teams

I’ve switched my security roles back to give my normal user access to only records it owns. That user now can’t see my account records any longer. Next I’ve created an owner team, given it the same security role and reassigned one of the accounts to the team.

Now when I run the same query again as my normal user I see that one record. Why? Because I am part of the team, the team owns the record and the team’s security role says it can read accounts it owns.

Access Teams

I’ve now reassigned all my accounts back to my system administrator account, so my normal user can’t see any again. I’ve created an access team and added my normal user to it.

When I share one of the accounts to the access team, my normal user can now see it.

Sharing

Rather than using teams to give extra users access to a record, you can also share the record directly with a specific user. This doesn’t scale well as it gets time consuming to do for large numbers of records, but works well for odd cases.

Again I removed the team that was giving access to the accounts to my normal user account and ensured it couldn’t see any records, then shared one of the records directly to that user. Again, that record appears in the results as expected.

Hierarchy Security

With hierarchy security, the privileges of a user can change depending on where they are in the organisational hierarchy. For my test I’ve enabled the Manager Hierarchy option, which gives a manager access to the same record as the users that report to them.

I’ve reset everything so my normal user does not have access to any account records. Next I’ve updated the Manager field of my system administrator user to be my normal user, and re-run my query. Immediately both users can now see all the records, as the normal user has inherited access from the system administrator.

Summary

So far as I can tell, the T-SQL endpoint honours all the security restrictions that are imposed by the rest of CDS, which should be a big tick for enterprises allowing this to be rolled out. This should enable Power BI reports to finally start replacing the older charts and dashboards that we’ve had to use to ensure the correct security filtering is applied.

CDS T-SQL Endpoint pt 7 – Extensibility

One of the aspects of the CDS platform I’ve liked since Dynamics CRM 4.0 is the extensibility options – the ability to add plugins into the core of the engine so that your custom code will be executed however someone is using it, whether through the standard UI, portals, external SDK app etc.

Although it already has a very flexible security model, you can always add plugins to the Retrieve and RetrieveMultiple messages to further log or filter the data that a user has access to.

If the T-SQL endpoint is a view onto CDS rather than a direct path to the SQL database then these plugins should still be fired. Alas, they’re not. No matter how I formulate the query or configure the plugin step it just doesn’t fire.

This makes some sense – you can’t translate all the possible SQL you might fire at it into any of the existing query types that the plugin might be able to get out of the parameters. I did wonder if there might be a new QueryBase-derived class to represent the SQL queries, but apparently not.

So, beware if Retrieve or RetrieveMultiple plugins form a core part of your security or business logic, as users could use T-SQL to bypass them!

So maybe there’s an entirely different SDK message processing step that I need to register my plugin on instead?

There’s nothing obvious listed when I type into the “Message” field in the plugin registration tool, so I decided to compare the list of available messages from my test instance to an old on-premise system to find what messages have been added. I ran SELECT name FROM sdkmessage against both systems and compared them in Excel. There’s a lot of extra messages online compared to on-premise, but one in particular stood out:

ExecutePowerBISql

This is almost certainly the one, given that the main purpose of this endpoint was to support running reports in Power BI.

Unfortunately the Plugin Registration Tool doesn’t offer the option of adding a step against this message. I’ve also tried adding one directly via the SDK and it gives the error:

Custom SdkMessageProcessingStep is not allowed on the specified message and entity.

So as far as I can tell the SQL requests do go via the core CDS service, but use a different message than a standard RetrieveMultiple that currently makes it impossible to attach custom plugins to inspect or alter what’s going on.

CDS T-SQL Endpoint pt 6 – Aggregates

One of the things that is very frustrating when trying to do any sort of analysis of data in CDS is the fetch aggregate limit. If you haven’t come across this before, this is a hard limit of 50,000 records that it will process when trying to calculate aggregate values such as sum, count etc. If you try to run a query in FetchXML Builder for example that needs to process more than 50k records you’ll get an “AggregateQueryRecordLimit exceeded” error.

The documentation advises working around this error by breaking up your query into smaller chunks and then aggregating the results. This will work, but involves a lot more thought, time and scope for error. Improving this experience was one of my main objectives for SQL 4 CDS.

T-SQL Results

My first simple test was to do a bulk import into the Lead entity and then run a simple queyr:

SELECT count(*) FROM lead

The result was simple but very pleasing:

Woohoo! It looks like the aggregate limit has gone for T-SQL!

Just to check this isn’t a special case for this one query (which you can do via the SDK with the RetrieveTotalRecordCountRequest), I also tried:

SELECT firstname, COUNT(*) FROM lead GROUP BY firstname ORDER BY 2 DESC

and a number of my other common data quality checks that use aggregation and they all worked correctly. 👍

New Options

FetchXML provides the most common aggregate options:

  • COUNT
  • MIN
  • MAX
  • SUM
  • AVG

These can be used across the entire result set, or you can use GROUP BY to get the results for each group. Another bugbear of mine has been the lack of an equivalent to the HAVING clause.

So I was pleased to find we now also have support for:

  • all the rest of the aggregate functions supported by T-SQL to do more advanced analysis such as standard deviation and variance
  • a HAVING clause to filter the aggregated results
  • GROUP BY xxx WITH ROLLUP to generate sub-totals and grand totals
  • window functions to generate running totals and row numbers in non-aggregated results

Thoughts

So this gets me to thinking, why is it different in T-SQL than FetchXML? Both are, after all, querying the same underlying database.

The aggregate query limit has been around for a long time – is this just a hold-over from when the hardware couldn’t cope with this as well as it can today, and could be removed entirely now?

Or is it that the T-SQL endpoint is running in such a fundamentally different way than the rest of the platform that the limit is either not necessary or too difficult to implement?

My gut feeling is that it’s more towards the later. After seeing some of the other queries that are now possible that aren’t in FetchXML, and some of the ways that it’s not returning the results I’d expect just yet, my current thought is that the T-SQL endpoint does some re-writing of the incoming SQL query to make it safe, then passes it on to the underlying Azure SQL Database. With the variety of ways that you can write a SQL query it must be very hard to ensure that you get the same results after the rewriting that you’d expect without it, so this might be a case of caution on the part of the product team to avoid breaking too many queries.

But at the same time I have to ask, if we can get this now via T-SQL, can we have the limit removed for FetchXML too please? 🙏

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.

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 4 – Performance

A common question I’ve seen so far about the new CDS T-SQL endpoint is how it compares performance-wise to using FetchXML. After all, FetchXML is “native” to CDS while T-SQL might be viewed as the johnny-come-lately compatibility layer, so it’s going to be slower, right?

To check it out I set up a simple test. I’ve loaded about 60,000 lead records and used SQL and FetchXML to retrieve them.

The (abbreviated) test code looks like this:

private void SQL()
{
  using (var con = new SqlConnection("<connectionstring>"))
  {
    con.Open();

    using (var cmd = con.CreateCommand())
    {
      cmd.CommandText = "SELECT leadid, firstname, lastname, companyname, emailaddress1 FROM lead";

      using (var reader = cmd.ExecuteReader())
      {
        while (reader.Read())
        {
          var leadId = reader.GetGuid(0);
          var firstName = reader.GetString(1);
          var lastName = reader.GetString(2);
          var companyName = reader.GetString(3);
          var emailAddress = reader.GetString(4);
        }
      }
    }
  }
}

private void FetchXML()
{
  using (var org = new OrganizationServiceProxy(...)
  {
    var fetch = @"
      <fetch page='1' paging-cookie=''>
        <entity name='lead'>
          <attribute name='leadid' />
          <attribute name='firstname' />
          <attribute name='lastname' />
          <attribute name='companyname' />
          <attribute name='emailaddress1' />
        </entity>
      </fetch>
    ";
    var xml = new XmlDocument();
    xml.LoadXml(fetch);
    var page = 1;

    while (true)
    {
      var result = org.RetrieveMultiple(new FetchExpression(xml.OuterXml));

      if (!result.MoreRecords)
        break;

      xml.SelectSingleNode("/fetch/@page").Value = (page++).ToString();
      xml.SelectSingleNode("/fetch/@paging-cookie").Value = result.PagingCookie;
    }
  }
}

I then ran each of these once, untimed, to allow the .NET runtime to do its initialisation thing so the results weren’t skewed by one-time startup costs.

Next I ran each one 10 times, timing each one, and took the average. The results are very encouraging:

MethodAvg. Time
SQL5.758 sec
FetchXML8.358 sec

So in this simple test, SQL comes out about 1.5x faster!

Now this is not quite fair, as the SqlConnection is automatically pooled so we don’t actually have any overhead in connecting to SQL on each attempt, but with the FetchXML test we had to open and close the connection each time. If we move the opening of each connection out of the tests so we do those once and only measure the actual query execution and result retrieval time, the results get a lot closer:

MethodAvg. Time
SQL5.026 sec
FetchXML8.129 sec

Still an impressive performance for the rookie!

Don’t put too much store in these exact numbers – I’m running these tests from the UK and my test instance is in Canada, which is going to add a lot of network overhead – but I’d expect the ratio between the two versions to be pretty consistent.

CDS T-SQL endpoint pt 3 – SqlClient

As a developer, the first thing I want to do with any new tool is try and integrate or extend it with my own code. This is always one of the real benefits of CDS and the Power Platform for me, as it’s so much more open to extensibility than others I could mention. Adding a T-SQL endpoint should just make this much wider.

The demos I’ve seen so far for this have all been around using SQL Server Management Studio. That’s great for ad-hoc queries, but what about if I want to do some regular data export/integration? If I was talking to any other SQL Server database I’d probably drop to C# code and connect to it through there, so I’ve given that a go here.

Connecting with SqlClient

You can use either System.Data.SqlClient or the newer Microsoft.Data.SqlClient to connect, either work just fine. The connection string you’ll need is in the format:

server=contoso.crm.dynamics.com,5558;user id=your-username;password=your-password;Authentication="Active Directory Password"

Note the Authentication parameter at the end to use the correct authentication method rather than the standard SQL Server authentication.

This is a quick code snippet to export the names of your account records:

using (var con = new SqlConnection("server=contoso.crm.dynamics.com,5558;user id=your-username;password=your-password;Authentication=\"Active Directory Password\""))
{
  con.Open();

  using (var cmd = con.CreateCommand())
  {
    cmd.CommandText = "SELECT name FROM account";

    using (var reader = cmd.ExecuteReader())
    {
      while (reader.Read())
      {
        Console.WriteLine(reader.GetString(0));
      }
    }
  }
}

Note that you don’t need to specify a database name in the connection string – you’ll only see one database when you connect and you’re automatically connected to that database so there’s no need to change. There’s no harm in including it though, so if you use

server=contoso.crm.dynamics.com,5558;user id=your-username;password=your-password;Authentication="Active Directory Password";database=contoso

it will still work just fine.

Broken Connection Strings

I did try a few other variants of connection strings but I only found one that didn’t seem to work so well:

server=tcp:contoso.crm.dynamics.com,5558;user id=your-username;password=your-password;Authentication="Active Directory Password"

Using the tcp: prefix on the server name is quite a common pattern in samples, but in this case you’ll get the error:

A connection was successfully established with the server, but then an error occurred during the login process. (provider: TCP Provider, error: 0 - The specified network name is no longer available.)

Application User

Having the username & password in the connection string is A Bad Thing. In most integration scenarios you should probably be using an application user instead. Azure SQL Database and now CDS supports this using the AccessToken property on the connection using code like:

var clientId = "<guid>";
var clientSecret = "<secret string>";
var aadTenantId = "<guid>";

var aadInstance = "https://login.windows.net/{0}";
var resourceId = "https://contoso.crm.dynamics.com/";

var authenticationContext = new AuthenticationContext(string.Format(aadInstance, aadTenantId));
var clientCredential = new ClientCredential(clientId, clientSecret);

var authenticationResult = await authenticationContext.AcquireTokenAsync(resourceId, clientCredential);

using (var con = new SqlConnection("server=contoso.crm.dynamics.com,5558"))
{
  con.AccessToken = authenticationResult.AccessToken;
  con.Open();

  using (var cmd = con.CreateCommand())
  {
    cmd.CommandText = "SELECT name FROM account";

    using (var reader = cmd.ExecuteReader())
    {
      while (reader.Read())
      {
        Console.WriteLine(reader.GetString(0));
      }
    }
  }
}

A few things to note here, especially if you’re copying code from an Azure SQL Database sample.

First, the AuthenticationContext class gets the access token asynchronously. Please don’t just use var authenticationResult = authenticationContext.AcquireTokenAsync(ResourceId, clientCredential).Result; as you’ll almost certainly end up with deadlocks at some point.

Second, all the Azure SQL Database sample code will use https://database.windows.net/ as the resource ID that’s passed to AcquireTokenAsync. This gives you a token that’s valid for that resource. If you use that to try to access CDS you’ll get the error:

The HTTP request is unauthorized with client authentication scheme 'Anonymous'. The authentication header received from the server was 'Bearer authorization_uri=https://login.microsoftonline.com/<guid>/oauth2/authorize, resource_id=https://contoso.crm.dynamics.com/

This is because you’re not actually accessing Azure SQL Database, but CDS pretending to be a SQL database. You therefore need to change the resource ID to be the URL of your CDS instance (https://contoso.crm.dynamics.com/ in this sample) and it should all work smoothly.

It does take some time to get the access token, so if you’re calling this code regularly make sure you cache the tokens (taking care to watch out for token expiry – the tokens are only valid for 1 hour so you’ll need to periodically get a new one).

Once you’ve got connected you can use the connection in the same way as any other SqlConnection – because that’s exactly what it is!