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 a virtual entity and a custom provider. In the next post, I’ll talk about the last missing piece, which is that embedded canvas app.

Just to recap, the idea would be to get data from SQL, to 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 this 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', '[email protected]');

INSERT INTO ITAExternalContact
(id, firstname, lastname, email)
values
( newid(), 'Frodo', 'Baggins', '[email protected]');
INSERT INTO ITAExternalContact
(id, firstname, lastname, email)
values
( newid(), 'John', 'Doe', '[email protected]');
INSERT INTO ITAExternalContact
(id, firstname, lastname, email)
values
( newid(), 'Santa', 'Claus', '[email protected]');

 

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 on 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

UPDATED CODE BELOW – IF YOU READ THIS EARLIER, YOU MAY WANT TO REVIEW AGAIN

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.

The part of this method that’s setting a shared variable is there to prepare SQL connection string. With the custom data provider, we won’t be registering steps, so there will be no place to read secure configuration.

However, we can use the same plugin, configure “Pre Operation” steps on both RetrieveMultiple and Retrieve, and use secure config to provide connection string.

That way, we can pass connection string through a shared variable to stage 30.

Also, there seems to be two different ways the framework can be calling retrieve multiple, and, so, there are two different ways to handle “conditions” and to parse them:

Besides, it 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:

This is where I used that “trick” with pre-operation to pass secure config from pre-operation to Stage 30 through a shared variable:

There is a similar step for RetrieveMultiple.

You’ll find complete plugin code (less the connection string) here:

https://github.com/ashlega/ITAintBoring.VirtualEntities

 

Once you’ve built the plugin, open PluginRegistration tool and register the assembly:

You don’t need to add any steps for the data provider, but, in order to use secure config to pass SQL connection string (and as I mentioned above), create two more steps:

• PreOperation on RetrieveMultiple
• PreOperation on Retrieve

Then add SQL connection string to the secure configuration of both steps.

What’s left is to register the data provider. While registering the data provider, you’ll also need to register the data source:

Once it’s done, you’ll see those registrations in the Plugin Registration tool:

Finally, link your virtual entity to the new data source:

Unless I forgot some of the steps, at this point you can save your changes, publish everything, and do a test run.

You should be able to see virtual entities data in the model-driven app:

You should be able to do basic search on that data:

And you should be able to open each individual record, although, at this point those records will be read-only.

In the next post, I’ll use embedded canvas app to add “update” functionality.