Virtual entities revisited: setting up a virtual entity

By | December 6, 2020

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

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.

15 thoughts on “Virtual entities revisited: setting up a virtual entity

  1. Andreas

    Great post! What is your recommendation for storing the connection string securely? Since we are not registering a plugin step we can’t use the “secure plugin configuration”. Is there another way?

    Reply
    1. Alex Shlega Post author

      There seems to be a workaround – will write about it in more details tomorrow. In short, try registering a step for the same plugin in pre-operation(for both retrieve and retrievemultiple), read connection string from secure config, and store it in the sharedvariable. Stage 30 will have access to that shared variable. Stage 20 will always run before Stage 30, so this should work. And it does seem to work, I just wanted to test it a bit more:)

      Reply
  2. Mark Lonsway

    Good stuff, I would love to ultimately see Power Platform Connectors somehow be accessible to the custom data provider , thereby eliminating the connectionstring and connection management – but I assume this would require a bit more platform convergence to make possible….someday.

    Reply
  3. SANDEEP BHASKAR

    Does it effect the database server ? If number of users in my organization are more and they are continuously trying to access the external data source by virtual entities. Does it loads my database?

    Reply
  4. SANDEEP BHASKAR

    Does it effect the database server ? If number of users in my organization are more and they are continuously trying to access the external data source by virtual entities. Does it loads my database?

    Reply
  5. Paul

    Thanks for the Pre-op suggestion.
    I was disappointed to see no config on the data provider.
    I will be adding this step for sure.

    Reply
  6. Paul

    Alex, thanks for the tip on the Pre-Operation step to grab the Secure Config.
    I was disappointed to see that the Data Provider registration didn’t have a similar concept but this works well.

    Reply
  7. Harpreet

    We are getting following error when opening a virtual entity record:

    Insufficient Permissions

    You do not have permissions to execute this action. Talk to an administrator to find out about updating your permissions.
    Technical Details
    Error Code: 2200000000
    Session Id: 064cb039-1111-4866-a79d-2b09cc8eeffa
    Activity Id: 75acd27c-23bc-4677-bac0-4a3d440b99fe
    Timestamp: Fri Mar 05 2021 12:08:54 GMT+0100 (India Standard Time)
    Create privilege violation encountered.
    UciError: Create privilege violation encountered. at i (https://.crm4.dynamics.com/uclient/scripts/app.js?v=1.4.2205-2102.1:4345:77) at new d (https://.crm4.dynamics.com/uclient/scripts/app.js?v=1.4.2205-2102.1:2427:1385) at C (https://.crm4.dynamics.com/uclient/scripts/app.js?v=1.4.2205-2102.1:1493:4635) at https://.crm4.dynamics.com/uclient/scripts/12.js?v=1.4.2205-2102.1:40:7220

    Reply
  8. Arun Sobti

    Thank you for sharing this Alex. Very helpful.
    I was wondering if we are able to store secure connection (encrypted) in our “config settings” entity, and configure it as needed for each environment. We can add the decryption in the pluvin Itself. Also, if we can do the same for the top 3 value. Either grab it from crm (if there is a way to get it) or from the config settings entity.

    Reply
    1. Alex Shlega Post author

      Hi Arun. You could probably just retrieve that configuration entity from within the virtual entity plugin (through a Retrieve / RetrieveMultiple call to the org service). Not sure if there is a way to somehow pass those configuration settings to the virtual entities plugins(so you would not have to do an extra “Retrieve”) – will try to find out.

      Reply
  9. e

    Quick note – the dropdown list of data sources on the entity definition form was missing the value for the new data source provider created. It took me a minute to find that I was missing a step to create it (manually in my case, not sure why) by going to Settings > Administration > Virtual Entity Data Sources and adding it there.
    Once I did that, the rest was smooth sailing as described in this post and a few others.

    Reply
  10. Alan

    Hey Alex,

    thx for the article. Do you know whether there is any way to create a relationship between the virtual entity and a normal entity?

    THX and regards,
    Alan

    Reply
  11. Paul

    Great post, thanks for writing this tutorial and providing sample code. Do you know if there is any way that I can route the connection via a data gateway to connect to on premise data sources? It seems strange the Microsoft haven’t enabled this as part of the OData 4 provider and wondering if a custom provider is able to be routed via the gateway.

    Reply
  12. Mahak Jain

    Hi Alex,

    Thanks for the article.. I am facing some issue with pagination.. It working for all cases accept if we have lookup column in the view.

    Thanks & regards,
    Mahak J

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *