Blog

Out of the box, Dynamics 365 V9 only comes with one data provider for virtual entities - the OData V4 Data Provider.  However, it is possible to create a custom data provider with a little bit of custom code.  There are a few examples floating around online, but none of them show how to set this up with a practical example (i.e. one that isn't hard-coded).  In this post, we'll walk through creating a custom data provider for an Azure Cosmos DB data source.

For a quick refresher on Virtual Entities, please refer to my previous post.

Custom data provider's are registered with the system through the Plugin Registration Tool.  Before we can do that, we need to create the Retrieve and RetrieveMultiple plugins which will be executed by our provider.  Unfortunately at this time it is not possible to set the Create/Update/Delete plugins for a custom data provider which means our custom provider will be read-only (just like the OData provider).  Hopefully Microsoft adds that functionality in the next release!

At a high level, the plugins need to 1) determine if they are running on a virtual entity, 2) get the data provider/source, and 3) connect to the external source to retrieve the data.  Microsoft has added a few extension methods/interfaces to help us out.  First, there is now an extension method on IOrganizationService which lets us easily get the metadata for the entity: GetEntityMetadata.  This is necessary so we can determine the External Name's of the attributes (i.e. the "column names" in our Azure Cosmos DB documents).  Second, there is now a service registered in the IServiceProvider which implements the IEntityDataSourceRetrieverService interface.  On that interface is a method called RetrieveEntityDataSource which lets us get the data source registered on the target entity of the plugin.  The data source entity contains the configuration values which define how we will connect to our external service.

Using these methods, we can create our Retrieve and RetrieveMultiple plugins.

namespace BGuidinger.Samples.AzureCosmosDB
{
    using Microsoft.Xrm.Sdk;
    using Microsoft.Xrm.Sdk.Extensions;

    public class RetrieveMultiple : Plugin
    {
        public override void OnExecute(IPluginProvider provider)
        {
            var entityName = provider.ExecutionContext.PrimaryEntityName;

            var metadata = provider.OrganizationService.GetEntityMetadata(entityName);
            var source = provider.EntityDataSourceRetrieverService.RetrieveEntityDataSource();

            var query = provider.ExecutionContext.InputParameterOrDefault<QueryExpression>("Query");

            var service = new AzureCosmosDbService(metadata, source);
            var entities = service.GetEntities(query);

            provider.ExecutionContext.OutputParameters["BusinessEntityCollection"] = entities;
        }
    }

    public class Retrieve : Plugin
    {
        public override void OnExecute(IPluginProvider provider)
        {
            var entityName = provider.ExecutionContext.PrimaryEntityName;

            var metadata = provider.OrganizationService.GetEntityMetadata(entityName);
            var source = provider.EntityDataSourceRetrieverService.RetrieveEntityDataSource();

            var service = new AzureCosmosDbService(metadata, source);
            var entity = service.GetEntity(provider.ExecutionContext.PrimaryEntityId);

            provider.ExecutionContext.OutputParameters["BusinessEntity"] = entity;
        }
    }
}

One important thing to note is that most blogs online say your external data source must have a GUID for a primary key.  This is only partially true.  As far as CRM is concerned, yes, the ID must be a GUID.  However, our custom data provider can act as a wrapper of sorts and do the translation from a GUID to another data type, for example an integer.

You'll notice that both plugins create an instance of the AzureCosmosDbService class.  This is a custom class which helps pass the queries to the Azure Cosmos DB documents and return the appropriate Entity objects.  It uses a few of the new methods from the Microsoft.Xrm.Sdk.Data namespace, as well as another custom class, SqlVisitor, which inherits the QueryExpressionVisitorBase class to handle query expressions using the visitor pattern.  As you can see, it is only parsing the "where" clause from the QueryExpression, but it could very easily parse the select columns, top/limit, joins, etc.  All of that would be handled by overriding the various Visit methods on the QueryExpressionVisitorBase base class.

namespace BGuidinger.Samples
{
    using Microsoft.Xrm.Sdk;
    using Microsoft.Xrm.Sdk.Data.Mappings;
    using Microsoft.Xrm.Sdk.Metadata;
    using Microsoft.Xrm.Sdk.Query;
    using System;
    using System.Collections.Generic;
    using System.IO;
    using System.Linq;
    using System.Net;
    using System.Security.Cryptography;
    using System.Text;

    public class AzureCosmosDbService
    {
        private readonly EntityMetadata _metadata;
        private readonly Entity _source;
        private EntityMap _mapper;

        public AzureCosmosDbService(EntityMetadata metadata, Entity source)
        {
            _metadata = metadata;
            _source = source;
            _mapper = EntityMapFactory.Create(metadata, new DefaultTypeMapFactory(), null);
        }

        public EntityCollection GetEntities(QueryExpression query)
        {
            var visitor = new SqlVisitor(_mapper);
            query.Accept(visitor);

            return new EntityCollection(ExecuteQuery($"SELECT * FROM s WHERE {visitor.Where}").ToArray());
        }

        public Entity GetEntity(Guid id)
        {
            var query = new QueryExpression();
            query.Criteria.AddCondition(_metadata.PrimaryIdAttribute, ConditionOperator.Equal, id);

            return GetEntities(query).Entities.FirstOrDefault();
        }

        private IEnumerable<Entity> ExecuteQuery(string query)
        {
            var uri = _source.GetAttributeValue<string>("new_uri");
            var masterKey = _source.GetAttributeValue<string>("new_masterkey");
            var database = _source.GetAttributeValue<string>("new_database");
            var collection = _metadata.ExternalName;

            var date = DateTime.UtcNow.ToString("R");

            var resourceType = "docs";
            var resourceId = $"dbs/{database}/colls/{collection}";

            var hash = GenerateHash("POST", resourceType, resourceId, date, masterKey, "master", "1.0");

            var request = WebRequest.CreateHttp($"{uri}/{resourceId}/{resourceType}");
            request.Method = "POST";
            request.ContentType = "application/query+json";
            request.Headers.Add("Authorization", hash);
            request.Headers.Add("X-MS-Date", date);
            request.Headers.Add("X-MS-Version", "2017-02-22");
            request.Headers.Add("X-MS-DocumentDB-IsQuery", "True");

            var body = Encoding.UTF8.GetBytes($"{{\"query\": \"{query}\"}}");
            using (var stream = request.GetRequestStream())
            {
                stream.Write(body, 0, body.Length);
            }

            using (var response = request.GetResponse())
            using (var stream = response.GetResponseStream())
            using (var reader = new StreamReader(stream))
            {
                var rows = ParseResponse(reader.ReadToEnd());
                foreach (var row in rows)
                {
                    var entityId = row[_mapper.MapAttributeNameExternal(_metadata.PrimaryIdAttribute)];
                    var entity = new Entity(_metadata.LogicalName, new Guid(entityId.ToString()));

                    foreach (var attribute in _metadata.Attributes)
                    {
                        var logicalName = attribute.LogicalName;
                        var externalName = _mapper.MapAttributeNameExternal(logicalName);
                        entity[logicalName] = row[externalName];
                    }

                    yield return entity;
                }
            }
        }

        private string GenerateHash(string verb, string resourceType, string resourceId, string date, string key, string keyType, string version)
        {
            var hash = new HMACSHA256 { Key = Convert.FromBase64String(key) };

            var payLoad = $"{verb.ToLower()}\n{resourceType.ToLower()}\n{resourceId}\n{date.ToLower()}\n\n";
            var payloadHash = hash.ComputeHash(Encoding.UTF8.GetBytes(payLoad));

            var signature = Convert.ToBase64String(payloadHash);

            return WebUtility.UrlEncode($"type={keyType}&ver={version}&sig={signature}");
        }
    }
}

With these plugins, you can now register the assembly and create the Data Provider.  To do this, open up the Plugin Registration Tool and connect to your instance.  From here, register the assembly like normal, and then register a new Data Provider.  If you don't already have a solution, you can create a new one from the drop down.

You can also create the Data Source entity which is where the configuration values are stored.  After creating the entity through the Plugin Registration Tool, you can add the entity to a solution to add fields and modify the form.  Note, you may have to define an External Name for the "ID" column of your data source entity or you will get an error when creating the data source below.

Finally, choose the assembly you just registered and set the event handlers appropriately and click Register.

Now, if you log into CRM and go to Settings > Administration > Virtual Entity Data Sources, you can create a new data source.  When you do this, you are prompted to choose which data provider you want to use, and you should see the new provider you created in the list!

Choosing the Azure Cosmos DB Data Provider will take you to the configuration form (notice that I added the configuration fields that are used by the AzureCosmosDbService above) where you can fill in the values and save it.

From here you can create a virtual entity using this data source.  What's really cool is that since the plugins dynamically retrieve the External Names for the entity/fields from the metadata, you can create a virtual entity for each table in the database.  All you have to do is configure the External Name values on the entity/fields!

This same type of process would also work for any other online data service.

Comments

PhuocLe December 18, 2018

Can you upload the source code to github ?

Thanks

Gunnar Sturzrehm January 10, 2019

It would be nice if you can update the example with the code of method ParseResponse and code of class SqlVisitor

Thanks
Gunnar

Bob Guidinger January 11, 2019

Sorry, I don't have the source code for this anymore. If you're interested in using Azure Cosmos DB with virtual entities, I recommend checking out the custom data provider from Microsoft on AppSource.

Christina Bayley November 20, 2019

Did you have any issues with Sorting or Paging in Dynamics? Some of our entities return a hundred records and in the subgrid, we're not able to sort or go to the next page.