Dynamics CRM: Implement Virtual Entity – Part 2

Last week, we already learn how to build a simple code to get data from Web-API to project the result in RetriveMultiple message that you can access here. Now we will continue to implement advanced scenarios to filtering, sorting and paginating the data.

Filtering + Sorting Data

Because we are using JSONPlaceholder API, we only can do filtering using an in-memory way. Meaning that we will do filtering after the data being loaded to the memory and show it. In a real-world case, suppose you need to custom the API to reduce the resources for both sides.

Here is the code for filtering and sorting (the highlighted):

using Microsoft.Xrm.Sdk;
using Microsoft.Xrm.Sdk.Query;
using RestSharp;
using System;
using System.Collections.Generic;
using System.Linq;
namespace Demo.Plugins
{
    public class RetrieveTodos
    {
        public class TodoModel
        {
            public int Id { get; set; }
            public string Title { get; set; }
            public bool Completed { get; set; }
        }
        public class Operation
        {
            public TodoModel[] Execute()
            {
                var client = new RestClient("https://jsonplaceholder.typicode.com/todos");
                var request = new RestRequest(Method.GET);
                var response = client.Execute<List<TodoModel>>(request);
                return response.Data.ToArray();
            }
        }
    }
    public class TodosRetrieveMultiplePlugin : IPlugin
    {
        public void Execute(IServiceProvider serviceProvider)
        {
            var context = (IPluginExecutionContext)serviceProvider.GetService(typeof(IPluginExecutionContext));
            var query = (QueryExpression)context.InputParameters["Query"];
            var data = new RetrieveTodos.Operation().Execute()
                .Select(rw =>
                {
                    var entity = new Entity("new_todo2")
                    {
                        Attributes =
                        {
                            ["new_name"] = rw.Title,
                            ["new_id"] = rw.Id,
                            ["new_completed"] = rw.Completed,
                            ["new_todo2id"] = Guid.NewGuid()
                        }
                    };
                    return entity;
                }).Where(entity => FilterEntity(query, entity)).ToArray();
            var sortData = Sort(query, data).ToArray();
            var entityCollection = new EntityCollection(sortData) { MoreRecords = false, PagingCookie = null };
            context.OutputParameters["BusinessEntityCollection"] = entityCollection;
        }
        private static bool FilterEntity(QueryExpression query, Entity entity)
        {
            if (!query.Criteria.Conditions.Any()) return true;
            var valid = false;
            foreach (var condition in query.Criteria.Conditions)
            {
                var value = entity.Contains(condition.AttributeName) ? entity[condition.AttributeName] : null;
                if (value == null) continue;
                if (condition.Values.Any())
                {
                    switch (condition.Operator)
                    {
                        case ConditionOperator.Equal:
                            valid = value.Equals(condition.Values[0]);
                            break;
                        case ConditionOperator.NotEqual:
                            valid = !value.Equals(condition.Values[0]);
                            break;
                        case ConditionOperator.Like:
                            valid = value.ToString().Contains(condition.Values[0].ToString().Replace("%", ""));
                            break;
                    }
                }
                if (!valid) break;
            }
            return valid;
        }
        private Entity[] Sort(QueryExpression query, Entity[] data)
        {
            if (!query.Orders.Any()) return data;
            var temp = data;
            foreach (var order in query.Orders)
            {
                temp = order.OrderType == OrderType.Ascending
                    ? temp.OrderBy(dt => dt.Contains(order.AttributeName) ?
                    dt[order.AttributeName] : null).ToArray()
                    : temp.OrderByDescending(dt => dt.Contains(order.AttributeName) ?
                    dt[order.AttributeName] : null).ToArray();
            }
            return temp;
        }
    }
}

From there what you can see, we need to implement every ConditionOperator that we support. In this sample, I take equal, not-equal, and like operators.

We sorting the data in the code above using the LINQ method. For more detailed information about these methods (OrderBy or OrderByDescending), you can check this URL.

Pagination

Here is the code for doing pagination (the highlighted):

using Microsoft.Xrm.Sdk;
using Microsoft.Xrm.Sdk.Query;
using RestSharp;
using System;
using System.Collections.Generic;
using System.Linq;
namespace Demo.Plugins
{
    public class RetrieveTodos
    {
        public class TodoModel
        {
            public int Id { get; set; }
            public string Title { get; set; }
            public bool Completed { get; set; }
        }
        public class Operation
        {
            public TodoModel[] Execute()
            {
                var client = new RestClient("https://jsonplaceholder.typicode.com/todos");
                var request = new RestRequest(Method.GET);
                var response = client.Execute<List<TodoModel>>(request);
                return response.Data.ToArray();
            }
        }
    }
    public class TodosRetrieveMultiplePlugin : IPlugin
    {
        public void Execute(IServiceProvider serviceProvider)
        {
            var context = (IPluginExecutionContext)serviceProvider.GetService(typeof(IPluginExecutionContext));
            var query = (QueryExpression)context.InputParameters["Query"];
            var data = new RetrieveTodos.Operation().Execute()
                .Select(rw =>
                {
                    var entity = new Entity("new_todo2")
                    {
                        Attributes =
                        {
                            ["new_name"] = rw.Title,
                            ["new_id"] = rw.Id,
                            ["new_completed"] = rw.Completed,
                            ["new_todo2id"] = Guid.NewGuid()
                        }
                    };
                    return entity;
                }).Where(entity => FilterEntity(query, entity)).ToArray();
            var sortData = Sort(query, data).ToArray();
            var totalRecordPerPage = query.PageInfo.Count;
            var totalPage = (int)Math.Ceiling((decimal)data.Length / totalRecordPerPage);
            var pageInfo = string.IsNullOrEmpty(query.PageInfo.PagingCookie)
                ? new[] { 0, totalPage, -1 }
                : query.PageInfo.PagingCookie.Split(new[] { '/' }, StringSplitOptions.RemoveEmptyEntries)
                    .Select(e => int.Parse(e.Trim())).ToArray();
            var pageNumber = pageInfo[2] > -1 && pageInfo[2] != data.Length ? 0 : pageInfo[0];
            var pagingData = sortData.Skip(totalRecordPerPage * pageNumber).Take(totalRecordPerPage).ToArray();
            pageNumber += 1;
            query.PageInfo.PageNumber = pageNumber;
            query.PageInfo.PagingCookie = $"{pageNumber}/{totalPage}/{data.Length}";
            var entityCollection = new EntityCollection(pagingData)
            {
                MoreRecords = pageNumber < totalPage,
                PagingCookie = query.PageInfo.PagingCookie,
                TotalRecordCount = data.Length
            };
            context.OutputParameters["BusinessEntityCollection"] = entityCollection;
        }
        private static bool FilterEntity(QueryExpression query, Entity entity)
        {
            if (!query.Criteria.Conditions.Any()) return true;
            var valid = false;
            foreach (var condition in query.Criteria.Conditions)
            {
                var value = entity.Contains(condition.AttributeName) ? entity[condition.AttributeName] : null;
                if (value == null) continue;
                if (condition.Values.Any())
                {
                    switch (condition.Operator)
                    {
                        case ConditionOperator.Equal:
                            valid = value.Equals(condition.Values[0]);
                            break;
                        case ConditionOperator.NotEqual:
                            valid = !value.Equals(condition.Values[0]);
                            break;
                        case ConditionOperator.Like:
                            valid = value.ToString().Contains(condition.Values[0].ToString().Replace("%", ""));
                            break;
                    }
                }
                if (!valid) break;
            }
            return valid;
        }
        private Entity[] Sort(QueryExpression query, Entity[] data)
        {
            if (!query.Orders.Any()) return data;
            var temp = data;
            foreach (var order in query.Orders)
            {
                temp = order.OrderType == OrderType.Ascending
                    ? temp.OrderBy(dt => dt.Contains(order.AttributeName) ?
                    dt[order.AttributeName] : null).ToArray()
                    : temp.OrderByDescending(dt => dt.Contains(order.AttributeName) ?
                    dt[order.AttributeName] : null).ToArray();
            }
            return temp;
        }
    }
}

For the pagination function, what we know is how much the data per page (which is store in query.PageInfo.Count). That information is based on the setting that we set from Personal options > Records per page.

Records per page information

After we know how much data that we need to show per page, then we only need to know how much the total data to get how many pages. For knowing which page, we store the data that we needed in query.PageInfo.PagingCookie. In the code above, I store page number, total page, and data length to validate with the data that I take from Web-API.

Here is the demonstration of the code above:

Demonstration for Pagination, Filter + Sorting

Retrieve

The last part of the code is to enable the Retrieve message. Here is the code for it (the highlighted code):

using Microsoft.Xrm.Sdk;
using Microsoft.Xrm.Sdk.Query;
using RestSharp;
using System;
using System.Collections.Generic;
using System.Linq;
namespace Demo.Plugins
{
    public static class Helper
    {
        public static Guid IntToGuid(int value)
        {
            byte[] bytes = new byte[16];
            BitConverter.GetBytes(value).CopyTo(bytes, 0);
            return new Guid(bytes);
        }
        public static int GuidToInt(Guid value)
        {
            byte[] b = value.ToByteArray();
            int bint = BitConverter.ToInt32(b, 0);
            return bint;
        }
    }
    public class RetrieveTodos
    {
        public class TodoModel
        {
            public int Id { get; set; }
            public string Title { get; set; }
            public bool Completed { get; set; }
        }
        public class Operation
        {
            public TodoModel[] Execute()
            {
                var client = new RestClient("https://jsonplaceholder.typicode.com/todos");
                var request = new RestRequest(Method.GET);
                var response = client.Execute<List<TodoModel>>(request);
                return response.Data.ToArray();
            }
        }
    }
    public class TodosRetrievePlugin : IPlugin
    {
        public void Execute(IServiceProvider serviceProvider)
        {
            var context = (IPluginExecutionContext)serviceProvider.GetService(typeof(IPluginExecutionContext));
            var model = new RetrieveTodos.Operation().Execute()
                .FirstOrDefault(e => e.Id == Helper.GuidToInt(context.PrimaryEntityId)) ?? new RetrieveTodos.TodoModel();
            var entity = new Entity("new_todo")
            {
                Attributes =
                {
                    ["new_name"] = model.Title,
                    ["new_id"] = model.Id,
                    ["new_completed"] = model.Completed,
                    ["new_todo2id"] = Helper.IntToGuid(model.Id)
                }
            };
            context.OutputParameters["BusinessEntity"] = entity;
        }
    }
    public class TodosRetrieveMultiplePlugin : IPlugin
    {
        public void Execute(IServiceProvider serviceProvider)
        {
            var context = (IPluginExecutionContext)serviceProvider.GetService(typeof(IPluginExecutionContext));
            var query = (QueryExpression)context.InputParameters["Query"];
            var data = new RetrieveTodos.Operation().Execute()
                .Select(rw =>
                {
                    var entity = new Entity("new_todo2")
                    {
                        Attributes =
                        {
                            ["new_name"] = rw.Title,
                            ["new_id"] = rw.Id,
                            ["new_completed"] = rw.Completed,
                            ["new_todo2id"] = Helper.IntToGuid(rw.Id)
                        }
                    };
                    return entity;
                }).Where(entity => FilterEntity(query, entity)).ToArray();
            var sortData = Sort(query, data).ToArray();
            var totalRecordPerPage = query.PageInfo.Count;
            var totalPage = (int)Math.Ceiling((decimal)data.Length / totalRecordPerPage);
            var pageInfo = string.IsNullOrEmpty(query.PageInfo.PagingCookie)
                ? new[] { 0, totalPage, -1 }
                : query.PageInfo.PagingCookie.Split(new[] { '/' }, StringSplitOptions.RemoveEmptyEntries)
                    .Select(e => int.Parse(e.Trim())).ToArray();
            var pageNumber = pageInfo[2] > -1 && pageInfo[2] != data.Length ? 0 : pageInfo[0];
            var pagingData = sortData.Skip(totalRecordPerPage * pageNumber).Take(totalRecordPerPage).ToArray();
            pageNumber += 1;
            query.PageInfo.PageNumber = pageNumber;
            query.PageInfo.PagingCookie = $"{pageNumber}/{totalPage}/{data.Length}";
            var entityCollection = new EntityCollection(pagingData)
            {
                MoreRecords = pageNumber < totalPage,
                PagingCookie = query.PageInfo.PagingCookie,
                TotalRecordCount = data.Length
            };
            context.OutputParameters["BusinessEntityCollection"] = entityCollection;
        }
        private static bool FilterEntity(QueryExpression query, Entity entity)
        {
            if (!query.Criteria.Conditions.Any()) return true;
            var valid = false;
            foreach (var condition in query.Criteria.Conditions)
            {
                var value = entity.Contains(condition.AttributeName) ? entity[condition.AttributeName] : null;
                if (value == null) continue;
                if (condition.Values.Any())
                {
                    switch (condition.Operator)
                    {
                        case ConditionOperator.Equal:
                            valid = value.Equals(condition.Values[0]);
                            break;
                        case ConditionOperator.NotEqual:
                            valid = !value.Equals(condition.Values[0]);
                            break;
                        case ConditionOperator.Like:
                            valid = value.ToString().Contains(condition.Values[0].ToString().Replace("%", ""));
                            break;
                    }
                }
                if (!valid) break;
            }
            return valid;
        }
        private Entity[] Sort(QueryExpression query, Entity[] data)
        {
            if (!query.Orders.Any()) return data;
            var temp = data;
            foreach (var order in query.Orders)
            {
                temp = order.OrderType == OrderType.Ascending
                    ? temp.OrderBy(dt => dt.Contains(order.AttributeName) ?
                    dt[order.AttributeName] : null).ToArray()
                    : temp.OrderByDescending(dt => dt.Contains(order.AttributeName) ?
                    dt[order.AttributeName] : null).ToArray();
            }
            return temp;
        }
    }
}

Because the data depending on the Todo.Id which is int, I found an article from Carina that gives me the idea to parse int to GUID using the method above.

Setting up virtual entity is not easy. What you think?

One thought on “Dynamics CRM: Implement Virtual Entity – Part 2

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.