Web DevelopmentVisual Studio 2013Entity FrameworkDesign / GraphicsMVCBeginnerjQueryHTMLIntermediateDevJavascript.NETASP.NETC#
Dynamically Querying Entity Framework with ASP.NET
5.00/5 (14 votes)
A ready-to-use solution for dynamically querying an Entity Framework DbContext in ASP.NET
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
IModelBinderin order to bind ourabstract QueryFilterclass 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.
/// <summary>
/// Defines the basic interaction logic of all
/// <see cref="QueryFilter"/> implementations
/// </summary>
[ModelBinder(typeof(ModelAbstractionBinder<QueryFilter>))]
public abstract class QueryFilter
{
/// <summary>
/// Gets/sets a string that represents the
/// <see cref="QueryFilter"/>'s discriminator,
/// which is the type name of the <see cref="QueryFilter"/> implementation
/// </summary>
public string Discriminator { get; set; }
/// <summary>
/// Filters the specified query
/// </summary>
/// <param name="entityType">The type of the query</param>
/// <param name="query">The query to filter</param>
/// <returns>The filtered query</returns>
public abstract IQueryable Filter(Type entityType, IQueryable query);
/// <summary>
/// Filters the specified query
/// </summary>
/// <typeparam name="TEntity">The type of the query to filter</typeparam>
/// <param name="query">The query to filter</param>
/// <returns>The filtered query</returns>
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.
/// <summary>
/// Represents a <see cref="QueryFilter"/>
/// used to order the results of a query
/// </summary>
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);
/// <summary>
/// Gets/sets a boolean indicating whether the OrderBy is descending or ascending
/// </summary>
public bool Descending { get; set; }
/// <summary>
/// Gets/sets the raw property path
/// </summary>
public string PropertyPath { get; set; }
/// <summary>
/// Filters the specified query
/// </summary>
/// <param name="entityType">The type of the query</param>
/// <param name="query">The query to filter</param>
/// <returns>The filtered query</returns>
public override IQueryable Filter(Type entityType, IQueryable query)
{
PropertyPath propertyPath;
ParameterExpression parameterExpression;
MemberExpression getPropertyExpression;
LambdaExpression lambdaExpression;
MethodInfo orderByMethod;
MethodCallExpression filterExpression;
//Creates the parameter expression
parameterExpression = Expression.Parameter(entityType, "param");
//Attempts to parse the PropertyPath
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)));
}
//Creates the expression to get the value returned by the targeted property
//(ex: 'param.Property1.Property2')
getPropertyExpression = propertyPath.ToExpression(parameterExpression);
//Creates the lambda (ex: '(param) -> param.Property1.Property2')
lambdaExpression = Expression.Lambda(getPropertyExpression, parameterExpression);
//Check whether or not the OrderBy is descending
if (this.Descending)
{
//The OrderByDescending method
orderByMethod = OrderByFilter.OrderByDescendingMethod.MakeGenericMethod
(entityType, getPropertyExpression.Type);
}
else
{
//The OrderBy method
orderByMethod = OrderByFilter.OrderByMethod.MakeGenericMethod
(entityType, getPropertyExpression.Type);
}
//Create the filter expression (ex: 'query.OrderBy
//((param) -> param.Property1.Property2)')
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.
/// <summary>
/// The view model of a filterable query
/// </summary>
/// <typeparam name="T">The type of the query</typeparam>
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");
/// <summary>
/// Gets/sets the maximum results per page
/// </summary>
public int ResultsPerPage { get; set; }
/// <summary>
/// Gets/sets the current page index
/// </summary>
public int PageIndex { get; set; }
/// <summary>
/// Gets/sets the current page count
/// </summary>
public int PageCount { get; set; }
/// <summary>
/// Gets/sets the results of the query
/// </summary>
public IEnumerable<T> Results { get; set; }
/// <summary>
/// Gets/sets the
/// <see cref="QueryFilter"/>s associated with the query
/// </summary>
public IEnumerable<QueryFilter> Filters { get; set; }
/// <summary>
/// Executes the query represented by the
/// <see cref="QueryViewModel{T}"/> in the specified
/// <see cref="DbContext"/>
/// </summary>
/// <param name="context">The
/// <see cref="DbContext"/> to execute the query into</param>
public void ExecuteQuery(DbContext context)
{
IQueryable query;
MethodInfo countMethod, skipMethod, takeMethod, toListMethod;
int pageCount, remainder;
//Create the query
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)
{
//Apply each filter to the query
foreach (QueryFilter queryFilter in this.Filters)
{
query = queryFilter.Filter(query.ElementType, query);
}
}
//If we dont do the following, which is a nasty trick,
//an exception will be thrown when attempting the following Skip() call
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.
/// <summary>
/// The <see cref="IModelBinder"/> implementation used to bind abstract classes
/// </summary>
/// <typeparam name="T">The type of the model to bind</typeparam>
public class ModelAbstractionBinder<T>
: DefaultModelBinder
{
public override object BindModel
(ControllerContext controllerContext, ModelBindingContext bindingContext)
{
string key, discriminator;
IEnumerable<Type> searchFilterTypes;
Type searchFilterType;
//Find the Discriminator value in the current request's form data
key = controllerContext.HttpContext.Request.Form.Keys.
OfType<string>().FirstOrDefault(k => k.Contains("Discriminator"));
discriminator = controllerContext.HttpContext.Request.Form[key];
if (string.IsNullOrWhiteSpace(discriminator))
{
//The Discriminator value is null, we therefore cannot do anything
return base.BindModel(controllerContext, bindingContext);
}
//Find the loaded type that matches the Discriminator's value
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 + "'");
}
//Set the ModelMetadata, used by the DefaultModelBinder to do all the binding work
bindingContext.ModelMetadata =
ModelMetadataProviders.Current.GetMetadataForType(null, searchFilterType);
//Let the DefaultModelBinding do the work for us
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;
//Check if an orderByFilter exists
if ($existingFilter.length > 0) {
existingPath = $existingFilter.find('input[name$="PropertyPath"]').val();
existingDescending = $existingFilter.find
('input[name$="Descending"]').val();
descending = !existingDescending;
}
//Reset the page index
$pageIndex.val(0);
//Clear query filters
$filters.remove();
//Create new OrderByFilter
$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);
//Create new ThenByFilter if required
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);
}
//Submit the form
$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 caseSentitive = $('.js-search-caseSensitive').is(':checked');
var $filters = $form.find('div.js-page-filter');
var $filter = $('<div class="js-page-filter">');
var $pageIndex = $form.find('input[name="PageIndex"]');
//Reset the page index
$pageIndex.val(0);
//Clear query filters
$filters.remove();
//Create new WhereFilter
$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);
//$input = $('<input type="hidden"
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();
});
});