Introduction
This article describes a simple, easy and dynamic way to handle Entity Framework queries in an ASP.NET project. The main goal is to provide a dynamic and reusable way to perform complex searches, as well as paging and filtering operations, on an Entity Framework DbContext
.
Table of Contents
- The QueryFilter: In this section, we will create the base class of all our filters
- A QueryFilter example: In this section, we will create our first
QueryFilter
- The QueryViewModel: In this section, we will create the class that will help us communicate back and forth with the view
- The ModelAbstractionBinder: In this section, we will create our own
IModelBinder
in order to bind our abstract QueryFilter
class to its implementations
- Using the code: Learn how to use the code
1. The QueryFilter
The QueryFilter
is the abstract
class that all the filters we will create will inherit from.
It exposes one property, Discriminator
, that will be used to store the name of its implementation type, and it defines an abstract Filter()
method - as well as its generic overload - which will be where the magic will happen.
[ModelBinder(typeof(ModelAbstractionBinder<QueryFilter>))]
public abstract class QueryFilter
{
public string Discriminator { get; set; }
public abstract IQueryable Filter(Type entityType, IQueryable query);
public IQueryable<T> Filter<T>(IQueryable<T> query)
{
object result;
result = this.Filter(typeof(T), query);
return result as IQueryable<T>;
}
}
See the attribute decorating our class? It tells MVC we are going to rely on our own IModelBinder
to do the binding work. Why is that? Simply because the DefaultModelBinder
MVC uses - by default, obviously - is not able to bind to abstract
classes. Now, remember our Discriminator
property, in the QueryFilter
class? This is what our ModelAbstractionBinder
will use to help the DefaultModelBinder
to bind to the correct classes.
2. A QueryFilter Example
Better than a lot of words on how the all processes work, let’s make our own OrderByFilter
, which we will use to sort a query.
public class OrderByFilter
: QueryFilter
{
private static readonly MethodInfo OrderByMethod =
typeof(Queryable).GetMethods().Single
(m => m.Name == "OrderBy" && m.GetParameters().Length == 2);
private static readonly MethodInfo OrderByDescendingMethod =
typeof(Queryable).GetMethods().Single(m => m.Name ==
"OrderByDescending" && m.GetParameters().Length == 2);
public bool Descending { get; set; }
public string PropertyPath { get; set; }
public override IQueryable Filter(Type entityType, IQueryable query)
{
PropertyPath propertyPath;
ParameterExpression parameterExpression;
MemberExpression getPropertyExpression;
LambdaExpression lambdaExpression;
MethodInfo orderByMethod;
MethodCallExpression filterExpression;
parameterExpression = Expression.Parameter(entityType, "param");
if (!DynamicSearchesExample.PropertyPath.TryParse(this.PropertyPath, out propertyPath))
{
throw new Exception(string.Format
("Failed to parse the specified value '{0}' into a {1}", this.PropertyPath, nameof(DynamicSearchesExample.PropertyPath)));
}
getPropertyExpression = propertyPath.ToExpression(parameterExpression);
lambdaExpression = Expression.Lambda(getPropertyExpression, parameterExpression);
if (this.Descending)
{
orderByMethod = OrderByFilter.OrderByDescendingMethod.MakeGenericMethod
(entityType, getPropertyExpression.Type);
}
else
{
orderByMethod = OrderByFilter.OrderByMethod.MakeGenericMethod
(entityType, getPropertyExpression.Type);
}
filterExpression = Expression.Call
(orderByMethod, query.Expression, Expression.Quote(lambdaExpression));
return query.Provider.CreateQuery(filterExpression);
}
}
As you can see, our OrderByFilter
takes a PropertyPath
property. For those of you who are familiar with WPF, it works in a similar fashion. For the others, understand it is just an array of string
containing the names of successive properties, used in my example to produce a MemberExpression
(example: Client.Address.StreetName
).
3. The QueryViewModel
The QueryViewModel<T>
is the class we will used to transmit our variables back and forth to the view. It will hold our QueryFilter
instances as well as some variables, used in this example for paging the results of our query. Note that we could have developed both a SkipFilter
and a TakeFilter
to achieve the exact same result.
public class QueryViewModel<T>
where T : class
{
private static MethodInfo CountMethod =
typeof(Queryable).GetMethods().Single(m => m.Name ==
"Count" && m.GetParameters().Count() == 1);
private static MethodInfo SkipMethod =
typeof(Queryable).GetMethods().Single(m => m.Name ==
"Skip" && m.GetParameters().Count() == 2);
private static MethodInfo TakeMethod =
typeof(Queryable).GetMethods().Single(m => m.Name ==
"Take" && m.GetParameters().Count() == 2);
private static MethodInfo ToListMethod =
typeof(Enumerable).GetMethod("ToList");
public int ResultsPerPage { get; set; }
public int PageIndex { get; set; }
public int PageCount { get; set; }
public IEnumerable<T> Results { get; set; }
public IEnumerable<QueryFilter> Filters { get; set; }
public void ExecuteQuery(DbContext context)
{
IQueryable query;
MethodInfo countMethod, skipMethod, takeMethod, toListMethod;
int pageCount, remainder;
query = context.Set<T>();
if(query == null)
{
throw new NullReferenceException(string.Format
("Failed to find a {0} of the specified type '{1}'",
nameof(DbSet), typeof(T).Name));
}
if(this.Filters != null)
{
foreach (QueryFilter queryFilter in this.Filters)
{
query = queryFilter.Filter(query.ElementType, query);
}
}
if (!typeof(IOrderedQueryable).IsAssignableFrom(query.Expression.Type)
|| this.Filters == null)
{
query = new OrderByFilter()
{ PropertyPath = query.ElementType.GetProperties().First().Name }
.Filter(query.ElementType, query);
}
countMethod = CountMethod.MakeGenericMethod(query.ElementType);
pageCount = Math.DivRem((int)countMethod.Invoke(null,
new object[] { query }), this.ResultsPerPage, out remainder);
if (remainder != 0)
{
pageCount++;
}
this.PageCount = pageCount;
skipMethod = SkipMethod.MakeGenericMethod(query.ElementType);
query = (IQueryable)skipMethod.Invoke(null, new object[]
{ query, this.ResultsPerPage * this.PageIndex });
takeMethod = TakeMethod.MakeGenericMethod(query.ElementType);
query = (IQueryable)takeMethod.Invoke(null, new object[]
{ query, this.ResultsPerPage });
toListMethod = ToListMethod.MakeGenericMethod(query.ElementType);
this.Results = (IEnumerable<T>)toListMethod.Invoke(null, new object[]
{ query });
}
}
4. The ModelAbstractionBinder
The ModelAbstractionBinder
is an IModelBinder
inheriting directly from the DefaultModelBinder
. Thanks to it (and to our Discriminator
property), we will be able to bind the abstract
QueryFilter
class to the adequate implementations.
public class ModelAbstractionBinder<T>
: DefaultModelBinder
{
public override object BindModel
(ControllerContext controllerContext, ModelBindingContext bindingContext)
{
string key, discriminator;
IEnumerable<Type> searchFilterTypes;
Type searchFilterType;
key = controllerContext.HttpContext.Request.Form.Keys.
OfType<string>().FirstOrDefault(k => k.Contains("Discriminator"));
discriminator = controllerContext.HttpContext.Request.Form[key];
if (string.IsNullOrWhiteSpace(discriminator))
{
return base.BindModel(controllerContext, bindingContext);
}
searchFilterTypes = TypeCacheUtil.FindFilteredTypes(typeof(T).Name,
(type) => typeof(T).IsAssignableFrom(type));
searchFilterType = searchFilterTypes.FirstOrDefault
(sft => sft.Name == discriminator);
if (searchFilterType == null)
{
throw new NullReferenceException
("Failed to find a " + typeof(T).Name +
" with the specified discriminator '" +
discriminator + "'");
}
bindingContext.ModelMetadata =
ModelMetadataProviders.Current.GetMetadataForType(null, searchFilterType);
return base.BindModel(controllerContext, bindingContext);
}
}
As you can see, we use the adequate Discriminator
field contained in the current request’s form data to retrieve the ModelMetaData
of our QueryFilter
implementation.
5. Using the Code
5.1. Create an Action that accepts a QueryViewModel<T> as Parameter
[HttpPost]
public ActionResult Index(QueryViewModel<UserMockup> model)
{
if (!this.ModelState.IsValid)
{
return this.View(model);
}
model.ExecuteQuery(this.DbContext);
return this.View(model);
}
5.2. Create a View to List Your Data
@model DynamicSearchesExample.Models.QueryViewModel<DynamicSearchesExample.Models.UserMockup>
@{
ViewBag.Title = "Home Page";
}
<div class="panel panel-default">
<div class="panel-heading">Search</div>
<div class="panel-body">
<div class="container">
<div class="row">
<div class="form-group col-md-4">
<label>Search by</label>
<select class="form-control js-search-by">
<option value="Id">Id</option>
<option value="LastName">Last name</option>
<option value="FirstName">First name</option>
<option value="DateOfBirth">Date of birth</option>
<option value="Email">Email</option>
<option value="Address">Address</option>
</select>
</div>
<div class="form-group col-md-4">
<label>Rule</label>
<select class="form-control js-search-mode">
<option value="StringStartsWith">String starts with</option>
<option value="StringEndsWith">String ends with</option>
<option value="StringContains">String contains</option>
<option value="StringDoesNotContain">String does not contain</option>
<option value="Equals">Equals</option>
<option value="NotEquals">Not equals</option>
<option value="IsLowerThan">Is lower than</option>
<option value="IsLowerThanOrEquals">Is lower than or equals</option>
<option value="IsGreaterThan">Is greater than</option>
<option value="IsGreaterOrEquals">Is greater than or equals</option>
<option value="IsNull">Is null</option>
<option value="IsNotNull">Is not null</option>
</select>
</div>
<div class="form-group col-md-4">
<label>Value to find</label>
<input type="text" class="form-control js-search-value" />
</div>
</div>
</div>
<a class="form-control btn btn-primary js-search-do">Execute search</a>
<a class="form-control btn btn-warning js-search-clear">Clear filters</a>
</div>
</div>
<div class="panel panel-default">
<div class="panel-body">
@using (Html.BeginForm("index", "home",
FormMethod.Post, new { @class = "js-form-pagination" }))
{
@Html.AntiForgeryToken()
@Html.HiddenFor(m => m.PageIndex)
@Html.HiddenFor(m => m.PageCount)
<div class="form-group">
<label for="ResultsPerPage">Results per page</label>
<select name="ResultsPerPage" class="form-control js-page-items">
<option value="25" @(Model.ResultsPerPage == 25 ?
"selected" : "")>25</option>
<option value="50" @(Model.ResultsPerPage == 50 ?
"selected" : "")>50</option>
<option value="75" @(Model.ResultsPerPage == 75 ?
"selected" : "")>75</option>
<option value="100" @(Model.ResultsPerPage == 100 ?
"selected" : "")>100</option>
<option value="125" @(Model.ResultsPerPage == 125 ?
"selected" : "")>125</option>
<option value="150" @(Model.ResultsPerPage == 150 ?
"selected" : "")>150</option>
</select>
</div>
<div class="form-group">
<nav aria-label="Pagination">
<ul class="pagination">
<li class="page-item @(Model.PageIndex == 0 ?
"disabled" : "")">
<a class="page-link js-page-link"
data-index="@((Model.PageIndex - 1).ToString())">Previous</a>
</li>
@for (int i = 0; i < Model.PageCount; i++)
{
<li class="page-item @(i == Model.PageIndex ?
"active" : "")">
<a class="page-link js-page-link"
data-index="@i">@((i + 1).ToString())</a>
</li>
}
<li class="page-item @(Model.PageIndex ==
Model.PageCount - 1 ? "disabled" : "")">
<a class="page-link js-page-link"
data-index="@((Model.PageIndex + 1).ToString())">Next</a>
</li>
</ul>
</nav>
</div>
int filterIndex = 0;
if (Model.Filters != null)
{
foreach (DynamicSearchesExample.Models.QueryFilter filter in Model.Filters)
{
<div class="js-page-filter">
@foreach (System.Reflection.PropertyInfo property in
filter.GetType().GetProperties().Where(p => p.CanRead && p.CanWrite))
{
<input type="hidden"
name="Filters[@filterIndex].@property.Name"
value="@property.GetValue(filter)" />
}
</div>
filterIndex++;
}
}
<input type="submit" class="hide" value="paginate" />
}
</div>
</div>
<table class="table table-bordered table-striped table-hover">
<thead>
<tr>
<th class="js-page-orderby"
data-orderby="Id">Id</th>
<th class="js-page-orderby"
data-orderby="FirstName">FirstName</th>
<th class="js-page-orderby"
data-orderby="LastName">LastName</th>
<th class="js-page-orderby"
data-orderby="DateOfBirth">DateOfBirth</th>
<th class="js-page-orderby"
data-orderby="Address">Address</th>
<th class="js-page-orderby"
data-orderby="Email">Email</th>
</tr>
</thead>
<tbody>
@foreach(DynamicSearchesExample.Models.UserMockup user in Model.Results)
{
<tr>
<td>@user.Id</td>
<td>@user.FirstName</td>
<td>@user.LastName</td>
<td>@user.DateOfBirth</td>
<td>@user.Address</td>
<td>@user.Email</td>
</tr>
}
</tbody>
</table>
@section Scripts
{
@Scripts.Render("~/scripts/searches.js")
}
5.3. Add the Following Script to the View Created in Point 5.2
$(document).ready(function () {
$('.js-page-link').click(function () {
var $this = $(this);
var $form = $('.js-form-pagination');
var pageIndex = $this.data('index');
var $pageIndex = $form.find('input[name="PageIndex"]');
if ($this.parent().hasClass('disabled')) {
return;
}
$pageIndex.val(pageIndex);
$form.submit();
});
$('.js-page-orderby').click(function () {
var $this = $(this);
var orderByPath = $this.data('orderby');
var thenByPath = $this.data('thenby');
var descending = false;
var $form = $('.js-form-pagination');
var $pageIndex = $form.find('input[name="PageIndex"]');
var $filters = $form.find('div.js-page-filter');
var $filter = $('<div class="js-page-filter">');
var $input = null;
var $existingFilter = $('div.js-page-filter input[name$="Discriminator"]
[value="OrderByFilter"]').closest('.js-page-filter');
var existingPath, existingDescending = null;
if ($existingFilter.length > 0) {
existingPath = $existingFilter.find('input[name$="PropertyPath"]').val();
existingDescending = $existingFilter.find
('input[name$="Descending"]').val();
descending = !existingDescending;
}
$pageIndex.val(0);
$filters.remove();
$input = $('<input type="hidden"
name="Filters[0].Discriminator" value="OrderByFilter">');
$filter.append($input);
$input = $('<input type="hidden"
name="Filters[0].Descending" value="' + descending + '">');
$filter.append($input);
$input = $('<input type="hidden"
name="Filters[0].PropertyPath" value="' + orderByPath + '">');
$filter.append($input);
$form.append($filter);
if (thenByPath !== undefined) {
$filter = $('<div class="js-page-filter">');
$input = $('<input type="hidden"
name="Filters[1].Discriminator" value="ThenByFilter">');
$filter.append($input);
$input = $('<input type="hidden"
name="Filters[1].Descending" value="' + descending + '">');
$filter.append($input);
$input = $('<input type="hidden"
name="Filters[1].PropertyPath" value="' + orderByPath + '">');
$filter.append($input);
$form.append($filter);
}
$form.submit();
});
$('.js-page-items').on('change', function (e) {
var $this = $(this);
var $form = $('.js-form-pagination');
var $pageIndex = $form.find('input[name="PageIndex"]');
$pageIndex.val(0);
$form.submit();
});
$('.js-search-do').click(function () {
var $form = $('.js-form-pagination');
var searchBy = $('.js-search-by').val();
var searchValue = $('.js-search-value').val();
var comparisonMode = $('.js-search-mode').val();
var $filters = $form.find('div.js-page-filter');
var $filter = $('<div class="js-page-filter">');
var $pageIndex = $form.find('input[name="PageIndex"]');
$pageIndex.val(0);
$filters.remove();
$input = $('<input type="hidden"
name="Filters[0].Discriminator" value="WhereFilter">');
$filter.append($input);
$input = $('<input type="hidden"
name="Filters[0].PropertyPath" value="' + searchBy + '">');
$filter.append($input);
$input = $('<input type="hidden"
name="Filters[0].ValueComparison" value="' + comparisonMode + '">');
$filter.append($input);
name="Filters[0].CaseSensitive" value="' + caseSentitive + '">');
//$filter.append($input);
$input = $('<input type="hidden"
name="Filters[0].Value" value="' + searchValue + '">');
$filter.append($input);
$form.append($filter);
//Submit the form
$form.submit();
});
$('.js-search-clear').click(function () {
var $form = $('.js-form-pagination');
var $filters = $form.find('div.js-page-filter');
//Clear query filters
$filters.remove();
//Submit the form
$form.submit();
});
});
5.4. Voilà!