Virtual Entity : Chapter 3 – Translating an advanced find query to a query understood by the Data Source

Overview

This is of the four-part series tutorial where I am trying to explain virtual entity in Dynamics 365 CE. The chapters are,

• Chapter 1 - Core concept and definition
• Chapter 2 - Setting up custom data source and custom provider and hands on code
• Chapter 3 – Introducing searching capability (Current)
• Chapter 4 – Storing the settings for the api.

The demo is created on a 30 day trial edition of Dynamics 365. All the code mentioned in the tutorial is available on my Github Repo. All the customization used in the tutorial is found on releases section of the same Github repo. I have tagged the binaries as pre-release as I have not used the code in any production scenario. Although I am keeping an eye out for any possible bug reported in Github or to my email but please consider all the code as is. In the code I have integrated country Api found in this page along with the description of the each end point. A huge shout out to ApiLayer. A big thanks to Mark Findlater for reviewing the blog.

Chapter - 3

So now we have set up our Virtual Entity and it is connected to a Custom Data Provider. The Data Provider is getting data from third-party Data Source in an advanced find view. We can also open the record and see the details in a form.

Here are some problems that we have not yet discussed 

• Can we run a query on the advanced find view so that only selected records are returned?
• What happens if we have different Dynamics instance (like dev-test-uat-production) and we have custom API environment for each? Do we need to register a Custom Provider like I did in Part 1 and Part 2 all over again for each environment?

This chapter will answer the first of the above questions. Ready?

Visitor Pattern and Query Expression Visitor

I am not going to go into huge detail about the visitor pattern. I found that this article explains the whole pattern very clearly. Let me, however, give you some idea of the problem we are trying to solve here, in our case we are exposing external data from and API. You might want to expose data directly from an SQL server or may be from even an azure logic app. So your users in CRM need be able to construct a query in Dynamics in fetch xml in advanced find. When the user clicks on search this comes to your retrieve multiple plugin. It becomes the responsibility of your plug in to translate the fetch xml (or query expression) to something that your data source can understand. This is where visitor pattern comes it. Newly introduced Query Expression Visitor class in the CRM SDK leverages this pattern to translate fetch queries to the query that the data source API will understand. The translation code still needs to be written by you. The pattern is there to encapsulate it. A similar example of this can be found in this MS doc I recommend reading and understanding the article in the first link for the explanation of the pattern before proceeding any further with this.

Code Example

So first up I am going introduce two new methods in my CountryGet class that will search countries by capital and search countries by region. They are quite self-explanatory. The former searches all the countries by given capital while the later searches country by region. After adding the code to call the apis my class for calling the API looks like below,

	using CodeBug.CountryProvider.Interfaces;
	using CodeBug.CountryProvider.Models;
	using Microsoft.Xrm.Sdk;
	using System;
	using System.Collections.Generic;
	using System.Linq;
	using System.Net.Http;
	using System.Runtime.Serialization.Json;
	using System.Text;
	using System.Threading.Tasks;
	namespace CodeBug.CountryProvider.Services
	{
	public class CountryGet : ICountryGet
	{
	private readonly string _url;
	private readonly Dictionary<string, string> _headers;
	private readonly IEntityFormatter<Country> _entityFormatter;
	public CountryGet(string url, Dictionary<string, string> headers, IEntityFormatter<Country> entityFormatter)
	{
	_url = url;
	_headers = headers;
	_entityFormatter = entityFormatter;
	}
	public async Task<IEnumerable<Entity>> AllAsync()
	{
	var countries = await PerformGetAsync<Country[]>($"/all");
	return countries.Select(t => _entityFormatter.ConvertToEntity(t));
	}
	public async Task<Entity> ByCodeAsync(string alpha3Code)
	{
	var country = await PerformGetAsync<Country>($"/alpha/{alpha3Code}");
	return _entityFormatter.ConvertToEntity(country);
	}
	public async Task<IEnumerable<Entity>> ByCapitalCityAsync(string capital)
	{
	var countries = await PerformGetAsync<Country[]>($"/capital/{capital}");
	return countries.Select(t => _entityFormatter.ConvertToEntity(t));
	}
	public async Task<IEnumerable<Entity>> ByRegionAsync(string region)
	{
	var countries = await PerformGetAsync<Country[]>($"/region/{region}");
	return countries.Select(t => _entityFormatter.ConvertToEntity(t));
	}
	private async Task<T> PerformGetAsync<T>(string urlString)
	{
	using (var httpClient = new HttpClient())
	{
	var endPoint = $"{_url}{urlString}";
	_headers.ToList().ForEach(t =>
	httpClient.DefaultRequestHeaders.Add(t.Key.ToString(), t.Value.ToString()));
	var response = await httpClient.GetStreamAsync(endPoint);
	var deserializer = new DataContractJsonSerializer(typeof(T));
	return (T)deserializer.ReadObject(response);
	}
	}
	}
	}

view raw CountryGet.cs hosted with ❤ by GitHub

Now comes the visitor class – very straight forward nothing super crafty about it.

	using CodeBug.CountryProvider.Interfaces;
	using Microsoft.Xrm.Sdk.Query;
	using System;
	using System.Linq;
	namespace CodeBug.CountryProvider.Services
	{
	public class CountryQueryVisitor : IQueryExpressionVisitor
	{
	public string SearchingBy { get; private set; }
	public string SearchedTerm { get; private set; }
	private readonly IQueryValidator _queryValidator;
	public CountryQueryVisitor(IQueryValidator queryValidator)
	{
	_queryValidator = queryValidator;
	}
	public QueryExpression Visit(QueryExpression query)
	{
	if (query == null)
	{
	throw new ArgumentNullException(nameof(query));
	}
	if (_queryValidator.Validate())
	{
	SearchingBy = query.Criteria.Conditions.Single().AttributeName;
	SearchedTerm = query.Criteria.Conditions.Single().Values.Single().ToString();
	}
	return query;
	}
	}
	}

view raw CountryQueryVisitor.cs hosted with ❤ by GitHub

My Retrieve Multiple now ties everything up. Notice that I have also introduced tracing.

	using CodeBug.CountryProvider.Constants;
	using CodeBug.CountryProvider.Services;
	using CodeBug.CountryProvider.Utils;
	using Microsoft.Xrm.Sdk;
	using Microsoft.Xrm.Sdk.Query;
	using System;
	using System.Collections.Generic;
	using System.Linq;
	namespace CodeBug.CountryProvider
	{
	public class RetrieveMultipleCountry : IPlugin
	{
	private readonly string _businessEntityColletion = "BusinessEntityCollection";
	private readonly string _queryParamName = "Query";
	public void Execute(IServiceProvider serviceProvider)
	{
	var context = (IPluginExecutionContext)serviceProvider.GetService(typeof(IPluginExecutionContext));
	var tracingService = (ITracingService)serviceProvider.GetService(typeof(ITracingService));
	var query = (QueryExpression)context.InputParameters[_queryParamName];
	var queryValidator = new QueryValidator(tracingService, query);
	var queryVisitor = new CountryQueryVisitor(queryValidator);
	query.Accept(queryVisitor);
	var countryGet = PluginHelper.PrepareCountryGet();
	List<Entity> countryEntityCollection = new List<Entity>();
	if (!string.IsNullOrEmpty(queryVisitor.SearchingBy) && queryVisitor.SearchingBy == CountrySchemaNames.Capital)
	{
	countryEntityCollection = countryGet.ByCapitalCityAsync(queryVisitor.SearchedTerm).Result.ToList();
	}
	else if (!string.IsNullOrEmpty(queryVisitor.SearchingBy) && queryVisitor.SearchingBy == CountrySchemaNames.Region)
	{
	countryEntityCollection = countryGet.ByRegionAsync(queryVisitor.SearchedTerm).Result.ToList();
	}
	else
	{
	countryEntityCollection = countryGet.AllAsync().Result.ToList();
	}
	var entityColletion = new EntityCollection
	{
	EntityName = CountrySchemaNames.EntitySchemaName
	};
	entityColletion.Entities.AddRange(countryEntityCollection.ToList());
	context.OutputParameters[_businessEntityColletion] = entityColletion;
	}
	}
	}

view raw RetrieveMultipleCountry.cs hosted with ❤ by GitHub

I have cleaned the code just to be very nit-picking, I introduced factory pattern in the country get like so,

	using CodeBug.CountryProvider.Interfaces;
	using CodeBug.CountryProvider.Models;
	using Microsoft.Xrm.Sdk;
	using System;
	using System.Collections.Generic;
	using System.Linq;
	using System.Net.Http;
	using System.Runtime.Serialization.Json;
	using System.Text;
	using System.Threading.Tasks;
	namespace CodeBug.CountryProvider.Services
	{
	public class CountryGet : ICountryGet
	{
	private readonly string _url;
	private readonly Dictionary<string, string> _headers;
	private readonly IEntityFormatter<Country> _entityFormatter;
	private readonly Dictionary<string, Func<string, Task<IEnumerable<Entity>>>> _apiCaller
	= new Dictionary<string, Func<string, Task<IEnumerable<Entity>>>>();
	public CountryGet(string url, Dictionary<string, string> headers, IEntityFormatter<Country> entityFormatter)
	{
	_url = url;
	_headers = headers;
	_entityFormatter = entityFormatter;
	ConstructApiCaller();
	}
	public async Task<IEnumerable<Entity>> RunSearch(string searchedBy, string searchedTerm)
	{
	return await _apiCaller[searchedBy](searchedTerm);
	}
	public async Task<IEnumerable<Entity>> AllAsync(string allText)
	{
	var countries = await PerformGetAsync<Country[]>($"/{allText}");
	return countries.Select(t => _entityFormatter.ConvertToEntity(t));
	}
	public async Task<Entity> ByCodeAsync(string alpha3Code)
	{
	var country = await PerformGetAsync<Country>($"/alpha/{alpha3Code}");
	return _entityFormatter.ConvertToEntity(country);
	}
	public async Task<IEnumerable<Entity>> ByCapitalCityAsync(string capital)
	{
	var countries = await PerformGetAsync<Country[]>($"/capital/{capital}");
	return countries.Select(t => _entityFormatter.ConvertToEntity(t));
	}
	public async Task<IEnumerable<Entity>> ByRegionAsync(string region)
	{
	var countries = await PerformGetAsync<Country[]>($"/region/{region}");
	return countries.Select(t => _entityFormatter.ConvertToEntity(t));
	}
	private void ConstructApiCaller()
	{
	_apiCaller.Add("all", AllAsync);
	_apiCaller.Add("ic_capital", ByCapitalCityAsync);
	_apiCaller.Add("ic_region", ByRegionAsync);
	}
	private async Task<T> PerformGetAsync<T>(string urlString)
	{
	using (var httpClient = new HttpClient())
	{
	var endPoint = $"{_url}{urlString}";
	_headers.ToList().ForEach(t =>
	httpClient.DefaultRequestHeaders.Add(t.Key.ToString(), t.Value.ToString()));
	var response = await httpClient.GetStreamAsync(endPoint);
	var deserializer = new DataContractJsonSerializer(typeof(T));
	return (T)deserializer.ReadObject(response);
	}
	}
	}
	}

view raw CountryGet.cs hosted with ❤ by GitHub

For that my visitor is slightly changed as well.

	using CodeBug.CountryProvider.Interfaces;
	using Microsoft.Xrm.Sdk.Query;
	using System;
	using System.Linq;
	namespace CodeBug.CountryProvider.Services
	{
	public class CountryQueryVisitor : IQueryExpressionVisitor
	{
	public string SearchingBy { get; private set; }
	public string SearchedTerm { get; private set; }
	private readonly IQueryValidator _queryValidator;
	public CountryQueryVisitor(IQueryValidator queryValidator)
	{
	_queryValidator = queryValidator;
	}
	public QueryExpression Visit(QueryExpression query)
	{
	SearchingBy = "all";
	SearchedTerm = "all";
	if (query == null)
	{
	throw new ArgumentNullException(nameof(query));
	}
	if (_queryValidator.Validate())
	{
	SearchingBy = query.Criteria.Conditions.Single().AttributeName;
	SearchedTerm = query.Criteria.Conditions.Single().Values.Single().ToString();
	}
	return query;
	}
	}
	}

view raw CountryQueryVisitor.cs hosted with ❤ by GitHub

And my Retrieve Multiple is a breeze

	using CodeBug.CountryProvider.Constants;
	using CodeBug.CountryProvider.Services;
	using CodeBug.CountryProvider.Utils;
	using Microsoft.Xrm.Sdk;
	using Microsoft.Xrm.Sdk.Query;
	using System;
	using System.Collections.Generic;
	using System.Linq;
	namespace CodeBug.CountryProvider
	{
	public class RetrieveMultipleCountry : IPlugin
	{
	private readonly string _businessEntityColletion = "BusinessEntityCollection";
	private readonly string _queryParamName = "Query";
	public void Execute(IServiceProvider serviceProvider)
	{
	var context = (IPluginExecutionContext)serviceProvider.GetService(typeof(IPluginExecutionContext));
	var tracingService = (ITracingService)serviceProvider.GetService(typeof(ITracingService));
	var query = (QueryExpression)context.InputParameters[_queryParamName];
	var queryValidator = new QueryValidator(tracingService, query);
	var queryVisitor = new CountryQueryVisitor(queryValidator);
	query.Accept(queryVisitor);
	var countryGet = PluginHelper.PrepareCountryGet();
	var countryEntityCollection = countryGet.RunSearch(queryVisitor.SearchingBy, queryVisitor.SearchedTerm)
	.Result.ToList();
	var entityColletion = new EntityCollection
	{
	EntityName = CountrySchemaNames.EntitySchemaName
	};
	entityColletion.Entities.AddRange(countryEntityCollection.ToList());
	context.OutputParameters[_businessEntityColletion] = entityColletion;
	}
	}
	}

view raw RetrieveMultipleCountry.cs hosted with ❤ by GitHub

As you could rightly say by now formatting the query for a Custom Provider is not that hard, but it is a bit of work nonetheless. You should consider allowing support only for certain operator like I did on the code above. Like you should consider rejecting any queries that has date operator. The problem also become some order of magnitude harder when the user use combination of filter criteria.
You should also consider allowing search on the field that you support. In this way you can give searchability when it is relevant.