Querying Virtual Entities

Virtual entities are a great way of exposing external data sources in Dataverse, and with the recent release of create/update/delete support I’m sure they’re about to become much more widely adopted. Your data immediately becomes available for your apps and appears no differently to your users than any other entity. That means users and tools can try to query your data in ways you didn’t expect. In this post I’ll look at some of the things you have to be careful of when publishing or consuming virtual entities.

At its core, a custom virtual entity provider consists of at least two plugins – one for Retrieve and one for RetrieveMultiple.

The Retrieve plugin tends to be quite straightforward. Given a record ID and list of column names, get those details of the requested record.

The RetrieveMultiple plugin can be a lot more complex to get right.

ExecuteFetch vs RetrieveMultiple

The first thing to bear in mind is that older tools and integrations may be using an ExecuteFetchRequest to query the data. This doesn’t trigger the RetrieveMultiple plugin and tries to retrieve your data from the standard Dataverse SQL database. If this happens you’ll get an error like:

Sql error: Generic SQL error. CRM ErrorCode: -2147204784 Sql ErrorCode: -2146232060 Sql Number: 208

Microsoft deprecated ExecuteFetchRequest in favour of the RetrieveMultiple message, so you should update any code using this method anyway.

RetrieveMultiple Query Types

The RetrieveMultiple request includes one parameter called “Query”. Generally this can be set to any query type that inherits from QueryBase:

• FetchExpression
• QueryExpression
• QueryByAttribute

However, it appears that some pre-processing goes on for virtual entities so that your plugin will only ever get a QueryExpression. I don’t believe this is documented behaviour so it could change, but as it stands today this has a few impacts.

If the request originally used a FetchExpression, that is converted to a QueryExpression automatically before your plugin is called. That’s great as it means your code doesn’t have to do that conversion itself. Unfortunately though, QueryExpression doesn’t support all of the FetchXML language. In particular it doesn’t support aggregates. If you try to execute an aggregate FetchXML query against your virtual entity you’ll get an error:

FetchExpression cannot be converted because aggregates aren't supported by QueryExpression

If the request uses a QueryByAttribute instead you’ll get an exception with the rather unhelpful text “An unexpected error occurred”. Dig into the exception some more (Detail.InnerFault) and you’ll see the message:

Query type 'Microsoft.Xrm.Sdk.Query.QueryByAttribute' is not supported

There’s nothing you can do to add support for either of these situations in your plugin. The errors are being generated earlier in the pipeline that you don’t have control over.

Handling QueryExpression Features

You might have implemented your RetrieveMultiple plugin so it handles the queries you’re expecting, but your users can throw a lot of different queries at it. Are you sure you’re going to give back the right results?

My starting point for a provider will be to check for each feature of QueryExpression, and if it’s being used, throw an exception to indicate the provider doesn’t support it. Then as I add support for different features I can remove the exceptions.

This might mean the user gets errors back when they try to run a query that the provider doesn’t support, but I think that’s better than giving them back results that don’t actually match the query they’ve written.

Although QueryExpression doesn’t cover all the features of FetchXML there’s still a lot in there to check for. A good starting point is:

public void Execute(IServiceProvider serviceProvider)
{
    var context = (IPluginExecutionContext)serviceProvider.GetService(typeof(IPluginExecutionContext));
    var req = new RetrieveMultipleRequest { Parameters = context.InputParameters };
    var qry = (QueryExpression)req.Query;

    if (qry.Criteria != null)
        throw new InvalidPluginExecutionException("Filter criteria are not supported");

    if (qry.Distinct)
        throw new InvalidPluginExecutionException("Distinct queries are not supported");

    if (qry.LinkEntities.Count > 0)
        throw new InvalidPluginExecutionException("Linked entities are not supported");

    if (qry.NoLock)
        throw new InvalidPluginExecutionException("NoLock hint is not supported");

    if (qry.Orders.Count > 0)
        throw new InvalidPluginExecutionException("Sort orders are not supported");

    if (qry.PageInfo != null)
        throw new InvalidPluginExecutionException("Paging is not supported");

    if (!String.IsNullOrEmpty(qry.QueryHints))
        throw new InvalidPluginExecutionException("Query hints are not supported");

    if (qry.SubQueryExpression != null)
        throw new InvalidPluginExecutionException("Subquery is not supported");

    if (qry.TopCount != null)
        throw new InvalidPluginExecutionException("Top count is not supported");

    // TODO: Execute the query against the remote data source and convert the results to entities
    context.OutputParameters["BusinessEntityCollection"] = new EntityCollection(results);
}

Cross-Provider Queries

A single query can reference entities from multiple different providers. However, the entire query gets sent to the provider for the main entity in the query. It’s then up to that provider to figure out how to handle it.

If the main entity is a standard one, and you’ve added links in to virtual entities, you’ll get an exception:

RetrieveMultiple cannot have LinkEntity which is virtual entity

When the main entity is your virtual entity but there are links to other entities, the entire query is going to be given to your RetrieveMultiple plugin and you need to figure out what to do with it.

If the links are to other related virtual entities you might be able to include them in the query you send to your data source. Otherwise you need to decide whether to do something clever to try to get the related records from their own data source and join them together yourself, or just to throw an exception to indicate that the query is not supported.

Getting record counts

A useful way of getting the total number of records in an entity is the RetrieveTotalRecordCountRequest. Unfortunately this doesn’t work with virtual entities, and it generates the error:

Entity <name> is a virtual entity, which is not supported

As we can’t use aggregate FetchXML queries either, there’s no direct way to do this. The best option at the moment seems to be to add a new custom API that understands how to retrieve this information for your data source.