Dataverse Virtual Entities: QueryExpression to Linq

When working with custom data providers in your Power Platform projects, it can be relatively easy to successfully present data from an external data source. But what happens when your users want to start applying all kinds of filters to your virtual entities?

Today I am going to show you how you can translate any QueryExpression to Linq queries easily that you can apply directly to your in-memory list of entities.

Query expressions in model-driven apps

In many scenarios, you will be facing a situation where you will need to query an external data source which doesn’t support filtering. Or even if it does, it would be cumbersome to adapt it to how model-driven apps handle data filtering.

For instance, QueryExpression has a property called Criteria which is a FilterExpression and it can consist of one or multiple conditions:

QueryExpression query = new QueryExpression("contact");  
query.Criteria.AddCondition("firstname", ConditionOperator.BeginsWith, "D");

FilterExpressions can also be grouped with each other, allowing the user to create grouped conditions like below:

QueryExpression query = new QueryExpression("contact");  
query.Criteria = new FilterExpression();   
query.Criteria.FilterOperator = LogicalOperator.And;
query.Criteria.AddCondition("address1_city", ConditionOperator.Equal, "Redmond");   
  
FilterExpression childFilter = query.Criteria.AddFilter(LogicalOperator.Or);   
childFilter.AddCondition("lastname", ConditionOperator.Equal, "Tharpe");   
childFilter.AddCondition("lastname", ConditionOperator.Equal, "Brown"); 

// Result after QueryExpression to Linq conversion: (contact.address1_city == "Redmond" && (contact.lastname == "Tharpe" || contact.lastname == "Brown"))

While these query expressions work great when working with data inside Dataverse – and the backend takes care of processing it -, you will need to implement a logic to support them somehow with your data provider – or just don’t support filtering at all.

FilterExpressionTransform to the rescue

After hours of searching on the internet for a possible solution, I have came across a library developed by Microsoft – but without any documentation or any reference to it on the internet: Microsoft.Xrm.Sdk.Data.Expressions.

The library consists of the following classes:

• FilterExpressionTransform<T>
  Translates a FilterExpression to a Linq expression targeting a class of T type.
• OrderExpressionTransform<T>
  Executes an OrderExpression on an IQueryable collection of type T.
• PageInfoTransformer<T>
  Executes paging on an IQueryable collection of type T using the provided PagingInfo.

The awesome news is that all these classes combined can be used to filter, order and page your in-memory list of entities based on the QueryExpression received from your model-driven app.

In this article we will be focusing on the FilterExpressionTransform class to project our filters onto our list of entities.

Preparation

In this example, I will assume that you already have set up your custom data provider. Furthermore, you should be at a point where you have a collection of entities awaiting for filtering.

To start working with any of the transforms above, the following preparations should be made:

• Your project must implement early binding on your entities.
• Your early bound class properties must match the logical name of the attributes. (Important)
• After all transformation has been done, always cast your objects back to Entity type or you will be presented with exceptions and errors.

All classes above are using reflection combined with the entity metadata and will be matching attributes to your classes based on their logical name and their types. For this reason, it is really important to have the proper types and property names defined in your early bound classes!

QueryExpression to Linq – Lets code

Please note that you will find a link to my GitHub repository where this sample will be found. The file names presented here are matching the name of the files in the repository where the code segment might be found.

EntityFilter.cs

First of all, we will download the necessary NuGet package so we can start using the mentioned library:

Install-Package Microsoft.CrmSdk.Data -Version 9.0.2.32

Now we will implement the filtering logic to make an instance of the FilterExpressionTransform class, build the linq expression and return the filtered list.

public class EntityFilter<T> where T : Entity
{
    public IList<T> FilterBy(IList<T> entities, QueryExpression queryExpression)
    {
        // FilterExpressionTransform throws an exception if no criteria has been defined
        if (queryExpression.Criteria.Conditions.Count > 0 || queryExpression.Criteria.Filters.Count > 0)
        {
            // FilterExpressionTransform transform the filters from the specified queryExpression
            // to LINQ expressions that can be evaluated on collections that support querying.
            var transformedExpression = 
                new FilterExpressionTransform<T>().Transform(queryExpression.Criteria);
            var compiledQuery = transformedExpression.Compile();

            return entities.Where(compiledQuery).ToList();
        }

        return entities;
    }
}

Explanation of code above, can be skipped: FilterExpressionTransform<T>.Transform(FilterExpression filter) will take the filter specified and using reflection, it will attempt to map all the conditions specified in it to a class of T type in a Linq expression. Properties will be matched by logical name on your class and if any of them are not found an exception will be thrown!

Then the expression will be compiled into executable code which will be used in our where statement to apply the filtering.

EntityFilterTests.cs

In this example I will be using a unit test project to easily reproduce a state where we already have a collection of early bound entities and a query expression. Now we will be calling the code we have prepared previously:

// Create source entity list
List<Account> accounts = GetAccountList();

// Define queries to be appled on source
var queryExpression = new QueryExpression();
queryExpression.Criteria.AddCondition("name", ConditionOperator.EndsWith, "LLC");

// Apply filters on entity list
IEntityFilter<Account> entityFilter = new EntityFilter<Account>();
var filteredResults = entityFilter.FilterBy(accounts, queryExpression);

After running the code above you will be presented with a list of entities that has been filtered by the specified conditions – which can be more and more complex, FilterExpressionTransform will take care of the rest.

GitHub: Sample Code

You may find the complete sample code with examples in my GitHub repositry at Bhawk90/Dataverse.DataProviders.Samples.

Summary

When I have first developed a custom data provider, I was trying to map query expressions manually to my list, supporting only a set of conditions – mostly equals.

It was a surprise for me that I did not find this library in the past and there were no documentations or references to it at all at the time of writing. Anyways, I am happy that Microsoft took the time and published a library that we can use to make translation of these query expressions easier!