Monthly Archives: December 2020

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:

image

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', '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:

image

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:

image

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”:

image

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:

image

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

image

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:

image

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:

image

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:

image

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

image

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

image

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:

image

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

image

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.

Virtual entity revisited – let’s see how far we can push them

If you have not heard of the virtual entities for a while, it’s understandable. Sometimes, it seems there were no new developments on that front for a while now.

And, yet, it might not be quite fair, so, it seems, I’m going to write  a few posts on this topic (not that I like writing series, but there might be just too much for a single post )

I’ll start with the problem at hands.

Imagine there is a huge amount of data stored in the SQL database, and imagine you need access to that data in your model-driven application. You don’t necessarily need all that data to be updatable, though you might need to have the ability to update a few fields here and there.

One way to do it will be to bring over all that data right to the Dataverse, but that would involve lots, and I mean lots of data synchronization. Is it doable? Probably. But, of course, with the current licensing model where all API calls are counted, and where Dataverse storage space can be quite expensive, that’s just going to be it… expensive.

However, this is exactly what Virtual Entities were created for. They have never gotten to the point where they’d support full set of CRUD operations, at least yet. And you may think they are heading towards deprecation because of how little we’ve heard about them lately. But have a look here:

https://docs.microsoft.com/en-us/dynamics365/fin-ops-core/dev-itpro/power-platform/overview

Microsoft is, actually, relying on them quite heavily for integrations. So, why don’t we do the same?

CRUD might be a bit of a problem, but here is what I have in mind:

image

See, Embedded Canvas Apps are supported on the model-driven forms, and that is also true for the virtual entity forms. So, if we wanted to update a virtual entity which is sources from SQL… we could just use an embedded canvas app.

Of course there is more to it.

  • We need to create a virtual entity
  • We need to write a custom data provider
  • We need to create an embedded canvas app

 

As I mentioned at the beginning of this post, it may take a few posts to write about it. But here is a teaser so far.

  • You’ll see how we can search for a “virtual record” right in the model-driven application view
  • We can use virtual entities in the lookups
  • We can update those virtual records through an embedded canvas app

Virtual entities demo

All the data for “Frodo”, “Gandalf”, etc is coming directly out of the SQL database.

In the next post, you will find step-by-step instructions on how to set it up, including custom provider source code:

Virtual entities revisited: setting up a virtual entity

 

And, then, you’ll see how to use embedded canvas app to add “update” functionality to the virtual entity:

https://www.itaintboring.com/dynamics-crm/virtual-entities-revisited-using-embedded-canvas-app-for-updates/