Dynamic filter with AutoMapper, System.Dynamic.Linq and EntityFramework
Dynamic sorting and filtering on REST API endpoints was always a challenge especially when you try to offer your users a rich experience that allows them to sort on multiple columns, use complex cascading filters and pagination.
In this blog post I will describe how you can add dynamic sorting and filtering for a REST API endpoints using AutoMapper, System.Dynamic.Linq and EntityFramework.
First create a new ASP.NET Core Web API application using the dotnet cli.
dotnet new webapi
And then add the dependencies to your .csproj
<PackageReference Include="Microsoft.EntityFrameworkCore" Version="2.1.4" />
<PackageReference Include="Microsoft.EntityFrameworkCore.Sqlite" Version="2.1.4" />
<PackageReference Include="AutoMapper" Version="8.1.1" />
<PackageReference Include="System.Linq.Dynamic.Core" Version="1.0.19" />
Add your EntityFramework DbContext and your entities definition
public class OrdersContext : DbContext
{
public OrdersContext(DbContextOptions<OrdersContext> options)
: base(options)
{ }
public DbSet<Customer> Customers { get; set; }
public DbSet<Order> Orders { get; set; }
}
public class Customer
{
public int CustomerId { get; set; }
public string Name { get; set; }
public string City { get; set; }
public string Address { get; set; }
public List<Order> Orders { get; set; }
}
public class Order
{
public int OrderId { get; set; }
public DateTime OrderDate { get; set; }
public DateTime DeliveryDate { get; set; }
public string Description { get; set; }
public string DeliveryAddress { get; set; }
public int CustomerId { get; set; }
public Customer Customer { get; set; }
}
In order to show more complex and complete information about the orders like customer name or customer city you need to create a view model
public class OrderViewModel
{
public DateTime OrderDate { get; set; }
public DateTime DeliveryDate { get; set; }
public string DeliveryAddress { get; set; }
public string CustomerName { get; set; }
public string CustomerCity { get; set; }
}
Using AutoMapper’s QueryableExtensions methods you can define easily how the properties from related entities like Customer will be mapped to your view model. You can find the complete documentation at AutoMapper’s QueryableExtensions.
public class OrdersProfile : Profile
{
public OrdersProfile()
{
CreateMap<Order, OrderViewModel>()
.ForMember(m => m.CustomerName,
opt => opt.MapFrom(o => o.Customer.Name))
.ForMember(m => m.CustomerCity,
opt => opt.MapFrom(o => o.Customer.City));
}
}
Now you need to expose the list of the orders as an API endpoint. To achieve that add a new OrdersController to your application and then add the endpoint using the next method.
[HttpGet]
public async Task<IEnumerable<OrderViewModel>> Get()
{
return await _ordersContext.Orders
.ProjectTo<OrderViewModel>(_mapper.ConfigurationProvider)
.ToListAsync();
}
In this example the AutoMapper.ProjectTo extension method was used to let EntityFramework know how the CustomerName and CustomerCity column will be mapped. The _mapper is an instance of IMapper retrieved using ASP.NET Core dependency injection. You will find the link to the complete project at the end of the article.
This is the complete list of the orders, but in a real environment you will need to give your users the possibility to filter, sort and limit the number of items returned.
Define your filter model.
public class Filter
{
public string Field { get; set; }
public string Operator { get; set; }
public object Value { get; set; }
public string Logic { get; set; }
public IEnumerable<Filter> Filters { get; set; }
}
public class Sort
{
public string Field { get; set; }
public string Dir { get; set; }
}
public class FilterDTO
{
public int Offset { get; set; }
public int Limit { get; set; }
public IEnumerable<Sort> Sort { get; set; }
public Filter Filter { get; set; }
}
Next you need to transform he FilterDTO to a query expression like “Order By CustomerName” or “CustomerCity = ‘New York’” and then using System.Linq.Dynamic.Core you can do dynamic string-based querying through LINQ providers (IQueryable). You can find the complete documentation System.Linq.Dynamic.Core on github.com.
Add the following classes to your project.
public static class QueryableExtensions
{
public static IQueryable<T> ToFilterView<T>(
this IQueryable<T> query, FilterDTO filter)
{
// filter
query = Filter(query, filter.Filter);
//sort
if (filter.Sort != null) {
query = Sort(query, filter.Sort);
// EF does not apply skip and take without order
query = Limit(query, filter.Limit, filter.Offset);
}
// return the final query
return query;
}
private static IQueryable<T> Filter<T>(
IQueryable<T> queryable, Filter filter)
{
if ((filter != null) && (filter.Logic != null))
{
var filters = GetAllFilters(filter);
var values = filters.Select(f => f.Value).ToArray();
var where = Transform(filter, filters);
queryable = queryable.Where(where, values);
}
return queryable;
}
private static IQueryable<T> Sort<T>(
IQueryable<T> queryable, IEnumerable<Sort> sort)
{
if (sort != null && sort.Any())
{
var ordering = string.Join(",",
sort.Select(s => $"{s.Field} {s.Dir}"));
return queryable.OrderBy(ordering);
}
return queryable;
}
private static IQueryable<T> Limit<T>(
IQueryable<T> queryable, int limit, int offset)
{
return queryable.Skip(offset).Take(limit);
}
private static readonly IDictionary<string, string>
Operators = new Dictionary<string, string>
{
{"eq", "="},
{"neq", "!="},
{"lt", "<"},
{"lte", "<="},
{"gt", ">"},
{"gte", ">="},
{"startswith", "StartsWith"},
{"endswith", "EndsWith"},
{"contains", "Contains"},
{"doesnotcontain", "Contains"},
};
public static IList<Filter> GetAllFilters(Filter filter)
{
var filters = new List<Filter>();
GetFilters(filter, filters);
return filters;
}
private static void GetFilters(Filter filter, IList<Filter> filters)
{
if (filter.Filters != null && filter.Filters.Any())
{
foreach (var item in filter.Filters)
{
GetFilters(item, filters);
}
}
else
{
filters.Add(filter);
}
}
public static string Transform(Filter filter, IList<Filter> filters)
{
if (filter.Filters != null && filter.Filters.Any())
{
return "(" + String.Join(" " + filter.Logic + " ",
filter.Filters.Select(f => Transform(f, filters)).ToArray()) + ")";
}
int index = filters.IndexOf(filter);
var comparison = Operators[filter.Operator];
if (filter.Operator == "doesnotcontain")
{
return String.Format("({0} != null && !{0}.ToString().{1}(@{2}))",
filter.Field, comparison, index);
}
if (comparison == "StartsWith" ||
comparison == "EndsWith" ||
comparison == "Contains")
{
return String.Format("({0} != null && {0}.ToString().{1}(@{2}))",
filter.Field, comparison, index);
}
return String.Format("{0} {1} @{2}", filter.Field, comparison, index);
}
}
Now all you have to do is to create another endpoint on the OrdersController with the new parameter FilterDTO and then use the QueryableExtensions.ToFilterView method.
[HttpPost]
[Route("filter")]
public async Task<IList<OrderViewModel>> GetOrdersView(FilterDTO filter)
{
//define the base query
var ordersQuery = _ordersContext
.Orders
.ProjectTo<OrderViewModel>(_mapper.ConfigurationProvider);
// apply the sort and filter
ordersQuery = ordersQuery.ToFilterView(filter);
// return the result
return await ordersQuery.ToListAsync();
}
You can test your endpoint using the model. Save this is a file with name filter.json
{
"Offset": 0,
"Limit": 10,
"Sort": [{
"Field": "CustomerName",
"Dir": "desc"
}],
"Filter": {
"Field": "CustomerCity",
"Operator": "eq",
"Value":"New York",
"Logic": "and"
}
}
and then run this in a terminal.
curl -i -X POST \
-H "Content-Type: application/json" \
--data "@./filter.json" \
localhost:5080/api/orders/filter
If you debug the application you will notice that the following sql statement will be generated.
SELECT "dtoOrder.Customer"."City" AS "CustomerCity", "dtoOrder.Customer"."Name" AS "CustomerName", "dtoOrder"."DeliveryAddress", "dtoOrder"."DeliveryDate", "dtoOrder"."OrderDate"
FROM "Orders" AS "dtoOrder"
INNER JOIN "Customers" AS "dtoOrder.Customer" ON "dtoOrder"."CustomerId" = "dtoOrder.Customer"."CustomerId"
WHERE "dtoOrder.Customer"."City" = 'New York'
ORDER BY "CustomerName" DESC
LIMIT @__p_1 OFFSET @__p_0
As you can see the filter from the model was converted into the sql expression WHERE “dtoOrder.Customer”.“City” = ‘New York’. Pretty cool!
Done!.
There are a few very interesting key takeaways with this approach:
- you can filter and sort on any column including the view model columns. In this example the CustomerName exists only on the view model.
- high performance because everything is converted to a single sql statement and passed to the database server to handle it.
- the model is extensible for any kind of operators and data types.
You can find the complete source code at github.com/gabihodoroaga/demo-dynamic-filters