Dynamics CRM: Implement Virtual Entity - Part 2
Last week, we already learned 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 for 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 is loaded to the memory and show it. In a real-world case, suppose you need to customize 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 ConditionOperatorthat we support. In this sample, I take equal, not-equal, and like operators.
We sorted 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 we need to show per page, then we only need to know how much the total data is to get how many pages. To know which page, we store the data that we need in query.PageInfo.PagingCookie. In the code above, I store the 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 Retrievemessage. 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 depends 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 a virtual entity is not easy. What do you think?
Leave a comment
Your comment is sent privately to the author and isn't published on the site.