Virtual Entities: ID and Relation Workarounds
Ahmed Sawalhy • March 4, 2019
Virtual Entities was a much needed feature in Dynamics CRM until its introduction in v9. It facilitates querying data in real-time that exists in an external system.
It used to be that we had to synchronise and save a copy of the data locally in order to access it seamlessly. That approach created a headache: outdated, missing, or erroneous data; in addition to the wasted space to save the data locally.
With the introduction to Virtual Entities (VEs), you get to link external data sources with local schema. It is done in such a way that by accessing the data just like a normal entity, the system automatically queries the other system instead of the local DB seamlessly.
With such flexibility comes a few limitations. It is understandable considering that it’s still in its infancy.
I will list the two missing features that could greatly expand the scope of usage in most projects. That is my prespective only, and it does not reflect the community’s point of view, of course.
Non-GUID Primary Keys
Dynamics CRM has a unified system of IDs; it uses GUIDs as the primary key format for all tables in its DB.
When dealing with other systems, we will definitely find other key formats; however, CRM assumes that those keys are in the same format. It creates an easy implementation for the feature, but severely limits its adoption.
One to many relations between CRM entities and VEs are not supported. You can indeed add a lookup to VEs but as soon as you query the entity, the system throws an error.
Lookups in VEs exist to relate external entities to each other, mirroring the external schema. However, creating a link with CRM entities does not work, and it makes sense that it should not. Unless the two systems are tightly coupled, or at least developed to be linked, the other system is not aware of CRM and its ID system. On the other hand, when using VEs you are explicitly creating a dependency between CRM and another system.
The good news is that N-1 relations are supported; CRM entities can lookup VEs, just like normal entities.
One way to overcome this limitation is to adapt the external system to CRM’s. This is by no means a practical solution.
Another approach would be to create a custom data provider in CRM to handle the different format or schema. However, custom providers are a major hassle to implement. They require handling too many variables that you would question its worth. Check this article if you would like to explore this option.
The most effective solution would be to wrap the external service with your own, creating a proxy that create mappings to handle all issues you might face.
I will go in depth in the next section, explaining the technical details of how to implement this solution.
Proxy Service Setup
Let’s assume we have an external system that contains the transaction history of accounts.
Create an empty Web API project.
Install OData v4 web service by installing NuGet package Microsoft.AspNet.OData.
Create a model for the transactions:
Add the following lines to the function Register in the WebApiConfig file:
Create the following controller:
We already have all accounts in CRM, but we don’t need the transaction history to be saved locally because they are read-only.
We’ll create a VE for the transactions. Add an account field of type text (remember we can’t have local lookups in VEs). This field will hold the account’s ID in CRM as a string.
Add a sub-grid on the account form listing all transactions.
The current sub-grid will retrieve all transactions in the external system, which does not serve our purpose.
To filter the retrieved records, we need to inject a condition on the account’s field in the transaction records. To achieve this, we will modify the FetchXML query of the sub-grid in an unsupported manner; hopefully, Microsoft will add a supported method soon.
Use the following code on the Account form:
Which filters the returning records to only ones related to this account.
If the external system uses non-GUID primary keys, we will have to find a way to link the same record in both systems using their respective ID format. I will present here two ways of doing it.
First, we could create an intermediate database. When we receive a request from CRM on the web service above, map the IDs returned from the data source to new GUIDs. The IDs are store as mappings in the intermediate DB for when the same record is being returned.
This solution is not necessary if the external system’s IDs are short enough.
A clever workaround would be to encode the IDs into a base GUID using a standardised algorithm, and return the result to CRM. Let me show you how we can do so.
Let’s change the model to reflect the external system’s ID system:
We also need to provide CRM with the new model in the WebApiConfig:
Next step would be to encode the returned IDs into a base GUID for CRM:
Of course, you have to handle the following cases for IDs:
- Contain letters: make sure they are Hex-encoded
- Length is greater than 32 after encoding: use the first method (DB mapping)
VEs is a great feature that has the potential to reduce projects’ implemention effort, drastically in some cases. In this article, I have introduced a solution to two frustrating issues that would prevent the adoption of VEs in some projects.
Hopefully, Microsoft will address those issues soon enough, or provide us with a supported alternative for some parts of the code used above.