使用 ASP.NET 动态查询 Entity Framework
5.00/5 (14投票s)
一个用于在 ASP.NET 中动态查询 Entity Framework DbContext 的即用型解决方案
引言
本文描述了一种简单、易于理解且动态的方式来处理 ASP.NET 项目中的 Entity Framework 查询。 主要目标是提供一种动态且可重用的方式来对 Entity Framework DbContext 执行复杂搜索，以及分页和过滤操作。
目录
- QueryFilter：在本节中，我们将创建所有过滤器的基类
- QueryFilter 示例：在本节中，我们将创建第一个
QueryFilter - QueryViewModel：在本节中，我们将创建帮助我们与视图来回通信的类
- ModelAbstractionBinder：在本节中，我们将创建自己的
IModelBinder，以便将我们的abstract QueryFilter类绑定到它的实现 - 使用代码：学习如何使用代码
1. QueryFilter
QueryFilter 是所有我们将创建的过滤器将继承的 abstract 类。
它公开一个属性 Discriminator，该属性将用于存储其实现类型的名称，并且它定义一个 abstract Filter() 方法 - 以及它的泛型重载 - 在这里奇迹将发生。
/// <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>;
}
}
请看修饰我们类的属性？它告诉 MVC 我们将依靠我们自己的 IModelBinder 来完成绑定工作。 为什么呢？ 简单来说，因为 MVC 默认使用的 DefaultModelBinder 显然无法绑定到 abstract 类。 现在，还记得我们在 QueryFilter 类中的 Discriminator 属性吗？这就是我们的 ModelAbstractionBinder 将用来帮助 DefaultModelBinder 绑定到正确的类。
2. QueryFilter 示例
比起用很多文字说明整个过程是如何运作的，让我们创建自己的 OrderByFilter，我们将使用它来对查询进行排序。
/// <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);
}
}
正如你所看到的，我们的 OrderByFilter 采用 PropertyPath 属性。 对于那些熟悉 WPF 的人来说，它的工作方式类似。 对于其他人来说，理解它只是一个包含连续属性名称的 string 数组，在我的示例中用于生成一个 MemberExpression（例如：Client.Address.StreetName）。
3. QueryViewModel
QueryViewModel<T> 是我们将用于将变量来回传递给视图的类。 它将保存我们的 QueryFilter 实例以及一些变量，在此示例中用于对查询结果进行分页。 请注意，我们可以开发 SkipFilter 和 TakeFilter 来获得完全相同的结果。
/// <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. ModelAbstractionBinder
ModelAbstractionBinder 是一个 IModelBinder，直接从 DefaultModelBinder 继承。 借助它（以及我们的 Discriminator 属性），我们将能够将 abstract QueryFilter 类绑定到适当的实现。
/// <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);
}
}
正如你所看到的，我们使用当前请求的表单数据中包含的适当 Discriminator 字段来检索 QueryFilter 实现的 ModelMetaData。
5. 使用代码
5.1. 创建一个接受 QueryViewModel<T> 作为参数的操作
[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. 创建一个视图来列出你的数据
@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. 将以下脚本添加到在第 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();
});
});