Dynamics 365 : Virtual entities revisited: setting up a virtual entity

In the previous post, I blogged about how useful virtual entities can be when integrating external data with Dataverse, and how we can take that integration even further by adding CRUD support through an embedded canvas app.

But there was a teaser video and no technical details, so, in this post, I’ll talk about setting up the virtual entity and custom provider. In the next post, I’ll talk about the embedded canvas app.

Just to recap, the idea would be to get data from SQL, do surface it in Dataverse as a virtual entity, and, also, to allow at least basic search functionality directly in Dataverse over that entity:

Here is the summary of what needs to be done:

Create a virtual entity and define all the fields
Create a custom data provider for that virtual entity
Configure a virtual entity data source
Connect virtual entity to the newly configured data source

Let’s do it.

But, first, let’s set up a SQL database and create a table with some data there.

That database has to be on the server that’s accessible from the plugin. In my case, I just used an Azure SQL database.

And here is the script:

CREATE TABLE ITAExternalContact
(
   Id uniqueidentifier primary key,
   FirstName nvarchar(50), 
   LastName nvarchar(50),
   Email nvarchar(255)
);


INSERT INTO ITAExternalContact
(id, firstname, lastname, email)
values
( newid(), 'Gandalf', 'The Gray', 'gandalfthegray@test.com');

INSERT INTO ITAExternalContact
(id, firstname, lastname, email)
values
( newid(), 'Frodo', 'Baggins', 'frodobaggins@test.com');
INSERT INTO ITAExternalContact
(id, firstname, lastname, email)
values
( newid(), 'John', 'Doe', 'johndoe@test.com');
INSERT INTO ITAExternalContact
(id, firstname, lastname, email)
values
( newid(), 'Santa', 'Claus', 'santaclaus@test.com');

With that done, let’s get back to the Dataverse side.

1. Setting up the virtual entity

This part is very straightforward. It seems we still have to use classic designer for the virtual entities, but, other than that, you just need to create a new entity and mark it as a virtual entity:

We are going to use our own custom provider, so I’m not sure it’s important what you put into the “External Name” and “External Collection Name”, but, as you can see above, those fields are mandatory, and I put some values there. Have not used them anywhere else, though.

Leave “Data Source” empty for now.

This entity is going to have 5 fields:

Basically, those fields correspond to the SQL columns (they don’t have to – the mapping is done in the custom provider anyway). And the “name” field is there for any entity.

Below is a screenshot for the “First Name”:

All other fields are set up in the same way. Again, when using a custom provider, “External Name” does not have to be populated.

As with any entity, it might be a good idea to configure some views, so I added those columns to the view below:

Last but not least, don’t forget to add that entity to a model-driven application so you could see your entity somewhere:

Save, publish all, make sure the application is there and the entity is there.

Just don’t expect it to work yet – we still have not defined the data source. If you try lookin at the entity data at this point, you’ll get an ugly error message. Which is fine, just keep moving on.

2. Setting up the data provider

For this step, there is a good article at the docs site that covers the basics Sample: Generic virtual entity data provider plug-in

We will need a bit more, though, but let me summarize how it works first.

There is a plugin that works on RetrieveMultiple and on Retrieve
That plugin is working in stage 30
We only need to register the plugin – there is no need to register the steps. It’ll happen automatically when registering a data provider
For most of the registration steps, we’ll be using plugin registration tool

And here are a couple of observations, btw:

Stage 30 will always get “Query” as a QueryExpression. Even if the original query came in as a Fetch. This is different from Stage 20, where that query would still be in Fetch format. This is why we don’t need to convert one format to another in stage 30
We’ll need this plugin to support basic search, and, for that, do not forget to go back to the virtual entity configuration and add some fields to the “Quick Find” view. You can do it now or later, but make sure you do it

With that, here is “Execute” method of my plugin:

I did put Retrieve and RetrieveMultiple into the same plugin for this example, but, of course, you might want to have two different plugins instead.

Turned out handling that keyword is a bit tricky, but, in the end, the code above worked (sometimes, leading “%” is surrounded by square brackets).

Note: once again, don’t forget to add some columns to the Quick Find view for your entity, or there would be no conditions in the query:

And back to the plugin again.

Once there is a keyword (or a guid), the rest is all about loading that data from SQL:

Why am I using TOP 3? Well, for this proof of concept, I have 4 rows in the table, and I wanted to show that I can

Control how much data is displayed
No matter how much data is there in SQL and how much data is displayed, I can search on all that data and display top 3 matches

There is a sql connection part there, so you’ll need to define your own connection string: