Virtual Entities: ID and Relation Workarounds

UPDATE (2019-12-27): I tested adding CRM entity lookups to Virtual Entities in the latest version and it seems to work just fine. The corresponding field in the web service must be a nullable GUID for it to work, however.

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.

The Catch

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.

1-N relations

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.

Solutions

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.

Implementation

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:

public class Transaction
{
    public Guid Id { get; set; }
    public string Title { get; set; }
    public decimal Amount { get; set; }
    public string Account { get; set; }
}

Add the following lines to the function Register in the WebApiConfig file:

ODataModelBuilder builder = new ODataConventionModelBuilder();
builder.EntitySet<Transaction>("Transactions");
config.MapODataServiceRoute("odata", null, builder.GetEdmModel(),
    new DefaultODataBatchHandler(GlobalConfiguration.DefaultServer));
config.Count().Filter().OrderBy().Expand().Select().MaxTop(null);
config.EnsureInitialized();

Create the following controller:

[EnableQuery]
public class TransactionsController : ODataController
{
    private static readonly IQueryable<Transaction> transactions =
        new List<Transaction>
        {
            new Transaction { Id = Guid.NewGuid(), Title = "Pay Subscription", Amount = 100},
            new Transaction { Id = Guid.NewGuid(), Title = "Buy Gold Package", Amount = 5000,
                                Account = "565C78F5-142D-E911-A62D-00155D313700" }
        }.AsQueryable();

    public IQueryable<Transaction> Get()
    {
        return transactions;
    }

    public Transaction Get([FromODataUri] Guid key)
    {
        return transactions.FirstOrDefault(p => p.Id == key);
    }
}

CRM Setup

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.

Relation Solution

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:

function filterTransactions(executionContext)
{
    var formContext = executionContext.getFormContext();

    var htmlGrid = window.parent.document.getElementById("Transactions");
    var controlGrid = formContext.getControl("Transactions");

    var id = formContext.data.entity.getId();

    controlGrid.addOnLoad(function () // make sure that the grid is loaded
    {
        var fetchXml =
            '<fetch version="1.0" output-format="xml - platform" mapping="logical" distinct="true"> '
                + '<entity name = "ldv_transaction" > '
                + '<attribute name="ldv_name" /> '
                + '<attribute name="ldv_amount" /> '
                + '   <filter type="and"> '
                + '       <condition attribute="ldv_account" operator="eq" value="' + id.replace(/[{}]/gi, '').toUpperCase()
                + '" />'
                + '   </filter> '
                + ' </entity > '
                + ' </fetch > ';

        htmlGrid.control.SetParameter("fetchXml", fetchXml);

        // refresh grid to show filtered records only.
        htmlGrid.control.Refresh();
    });
}

Call filterTransactions on form load.

Notice the following line:

<condition attribute="ldv_account" operator="eq" value="' + id.replace(/[{}]/gi, '').toUpperCase()

Which filters the returning records to only ones related to this account.

Non-GUID Keys

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:

public abstract class TransactionBase
{
    public string Title { get; set; }

    public decimal Amount { get; set; }

    public string Account { get; set; }
}

public class Transaction : TransactionBase
{
    public int Id { get; set; }
}

public class TransactionCrm : TransactionBase
{
    public Guid Id { get; set; }
}

We also need to provide CRM with the new model in the WebApiConfig:

builder.EntitySet<TransactionCrm>("Transactions");

Next step would be to encode the returned IDs into a base GUID for CRM:

[EnableQuery]
public class TransactionsController : ODataController
{
    private static readonly IQueryable<Transaction> transactions =
        new List<Transaction>
        {
            new Transaction { Id = 1, Title = "Pay Subscription", Amount = 100},
            new Transaction { Id = 2, Title = "Buy Gold Package", Amount = 5000,
                                Account = "565C78F5-142D-E911-A62D-00155D313700" }
        }.AsQueryable();
    private static readonly Guid baseGuid = Guid.NewGuid();

    public IQueryable<TransactionCrm> Get()
    {
        return MapModel(transactions);
    }

    public TransactionCrm Get([FromODataUri] Guid key)
    {
        return MapModel(transactions).FirstOrDefault(t => t.Id == key);
    }

    private static IQueryable<TransactionCrm> MapModel(IQueryable<Transaction> transactions)
    {
        var baseGuidString = baseGuid.ToString("N").ToUpper();
        return transactions
            .Select(t => // use auto-mapper
                new TransactionCrm
                {
                    Id = Guid.Parse(baseGuidString.Remove(baseGuidString.Length - t.Id.ToString().Length) + t.Id),
                    Title = t.Title,
                    Amount = t.Amount,
                    Account = t.Account
                });
    }
}

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)

Final Thoughts

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.

Credits

Relation solution designed in co-ordination with @amrattia (LinkedIn).

Share

Tweet

Follow