使用 DataTables 和 LinqKit 为超过一百万条记录构建高性能动态查询
4.98/5 (13投票s)
提供可行且高性能的数据显示，适用于海量数据（超过一百万条记录）
引言
需要一个可行的数据网格，能够执行特定和多列搜索、排序、动态分页大小，并且不特定于数据库（至少我们已在 MSSQL 和 MySQL 上进行过测试），能够处理海量数据（超过一百万条记录），拥有优雅的代码结构，最少的编码工作量，最重要的是性能。目的是在不降低性能的前提下，将这些特性融合到一个简单易于编码和管理的解决方案中。
先决条件
要理解本文，您需要对 MVC 框架有一定的了解。如果您认为自己已经足够精通，那么您可以继续阅读本文。
抽象
该解决方案是 DataTables 和 LINQKit 工具的结合。DataTables 用于客户端，它处理显示网格数据并提供搜索和排序的用户界面功能，并将用户的搜索查询发送到服务器。在服务器端，LINQKit 用于构建灵活且高性能的查询。为了进一步解释，代码分为客户端代码和服务器端代码两部分。
客户端代码
在客户端，DataTables 是王者，我们在这里使用它的服务器端处理功能。在此之前，DataTables 是 jQuery Javascript 库的一个插件。它是一个高度灵活的工具，基于渐进增强的基础，并将高级交互控件添加到任何 HTML 表格中。它处理并执行我们从客户端所需的功能。最重要的是，它会为网格或页面上的每一次信息绘制（即分页、排序、搜索等）调用 Ajax 请求到服务器。它会将许多变量发送到服务器（这是它内置的功能），以便服务器能够处理这个 Ajax 请求并返回 JSON 数据。您可以从 这里 下载 DataTables 插件，或者直接将下面的行添加到您的页面中。
<link href="https://cdn.datatables.net/r/dt/dt-1.10.9/datatables.min.css"
rel="stylesheet" type="text/css" />
<script type="text/javascript"
src="https://cdn.datatables.net/r/dt/dt-1.10.9/datatables.min.js"></script>
DataTables 表结构
下面是 DataTables 插件使用的表结构示例，您可以看到我们为表定义了两个标题行，第一个标题包含列名，第二个标题是我们的搜索控件的占位符。
<table cellspacing="0" class="display"
id="grid" style="font-size: small;" width="100%">
<thead>
<tr>
<th width="20%">Company</th>
<th width="10%">Contact Person</th>
<th width="10%">Phone</th>
<th width="10%">Fax</th>
<th width="10%">City</th>
<th width="10%">Region</th>
<th width="10%">Country</th>
<th width="10%">Balance</th>
</tr>
<tr id="filterrow">
<th width="20%"> </th>
<th width="10%"> </th>
<th width="10%"> </th>
<th width="10%"> </th>
<th width="10%"> </th>
<th width="10%"> </th>
<th width="10%"> </th>
<th width="10%"> </th>
</tr>
</thead>
</table>
DataTables 表初始化
在进入初始化步骤之前，让我解释一下 _columnFilterMannifest 变量，它保存了所有列搜索控件的清单和数据，这些控件将位于我们之前讨论的第二个标题行上。此变量在我们的默认操作方法调用时设置。在页面就绪函数中，会调用 setGridFilter('grid',_columnFilterManifest) 函数，该函数位于我们的 Grid.js 中，用于封装功能。简而言之，第一个参数是我们结构表的 ID，第二个是我们刚才讨论的清单。现在，我们创建的表结构将使用 DataTables 插件进行初始化，您可以 在此 查看它的手册。
最后，会调用 initGridFilter() 方法，该方法同样在 Grid.js 中，并被封装。其主要目的是为网格的所有列搜索控件分配触发搜索功能。
var _columnFilterManifest =
JSON.parse( @Html.Raw(Json.Encode( ViewData["columnFilterManifest"].ToString() ) ));
$(document).ready(function () {
setGridFilters('grid',_columnFilterManifest);
var table = $('#grid').DataTable({
"order": [[1, "asc"]],
"orderCellsTop": true,
"pagingType": "full_numbers",
"scrollCollapse": true,
"processing": true,
"serverSide": true,
"lengthMenu": [[10,15,25, 50], [10,15,25, 50]],
"ajax": "/Customer/GetPage",
"columns": [
{ "data": "company" },
{ "data": "contactPerson" },
{ "data": "phone" },
{ "data": "fax" },
{ "data": "city" },
{ "data": "region" },
{ "data": "country" },
{ "data": "balance" },
],
"columnDefs": [
{
"render": function (data, type, row) {
return accounting.formatMoney(data);
},
"targets": 7
}
],
"initComplete": function () {
var api = this.api();
api.$('td').click(function () {
api.search(this.innerHTML).draw();
});
},
"autoWidth": false,
});
initGridFilter(table,'grid');
});
服务器端
在服务器端，我们使用 LINQKit 来实现我们的愿望。它的一个控制器操作方法设置了 _columnFilterManifest 变量供客户端使用，该变量用于初始化和填充我们在客户端代码讨论中已经讨论过的列搜索控件。
/// <summary>
/// Default page controller method
/// </summary>
/// <returns></returns>
public ActionResult Index()
{
BusinessLogic.Entity.Customer objCust = new BusinessLogic.Entity.Customer();
ViewData["columnFilterManifest"] =
new JavaScriptSerializer().Serialize(objCust.GetColumnFilterManifest());
return View("list");
}
DataTables 将调用 GetPage() 控制器的 Action 方法来获取分页数据，在这里您可以看到它调用了业务逻辑层实体来获取实体数组结果，然后以 JSON 格式发送回客户端。它返回的 JSON 对象包含诸如 data（包含实体数组）、recordsTotal（实体数组大小）、recordsFiltered（实体数组大小）和 draw（DataTables 用于确保服务器端处理请求的 Ajax 返回按顺序绘制）之类的属性。/// <summary>
/// Provides grid paginated records as JSON
/// </summary>
/// <returns>DataTables paginated data</returns>
public JsonResult GetPage()
{
int draw = Convert.ToInt32(Request["draw"]);
BusinessLogic.Entity.Customer objCust = new BusinessLogic.Entity.Customer();
CustomerGrid[] customers = objCust.Search(this);
return Json(new
{
data = customers,
recordsTotal = base.GetGridTotalRows(),
recordsFiltered = base.GetGridTotalRows(),
draw = draw
},
"application/json", Encoding.UTF8, JsonRequestBehavior.AllowGet);
}
现在，我们转向由控制器操作方法调用的实体业务逻辑层，即 Search(IBaseController iBaseController)。在此业务逻辑层实体方法中，执行我们所有的分页活动，将 filter by 子句和 order by 子句动态添加到查询中，以匹配 DataTables 的查询。
/// <summary>
/// Get list of filtered customers
/// </summary>
/// <param name="iBaseController">Interface of invoke class</param>
/// <returns>Customer array</returns>
public Common.Entity.Customer.CustomerGrid[] Search
(Common.Generic.IBaseController iBaseController)
{
GridParameterCollector gridParamCollet =
iBaseController.GenerateGridParameterCollector();
AccessLayer.DataTableEntities obj = new AccessLayer.DataTableEntities();
records = from cus in obj.customers
join count in obj.countries on cus.countryId equals count.countryId
select new CustomerGrid
{
id = cus.customerID,
idStr =
SqlFunctions.StringConvert((double?)
cus.customerID, 20, 0).Trim(),
company = cus.company ,
contactPerson = cus.contactPerson,
phone = cus.phone,
fax = cus.phone,
city = cus.city,
region = cus.region,
country = count.country1,
countryId = cus.countryId.Value,
balance = cus.balance.Value
};
genericSearchText = gridParamCollet.searchText.Trim(); // set generic
// search value
// Iterate through filter grid column to construct query predicate
foreach (ColumnParameterCollector column in gridParamCollet.columnCollector)
{
searchColumnText = column.columnSearchText.Trim(); // set current
// column search value
switch (column.dataName)
{
case "email":
string emailColumnText = searchColumnText;
string emailGenericText = genericSearchText;
EvaluateFilter(column,
x => x.email.StartsWith(emailColumnText),
x => x.email.StartsWith(emailGenericText),
x => x.email
);
break;
case "company":
string companyColumnText = searchColumnText;
string companyGenericText = genericSearchText;
EvaluateFilter(column,
x => x.company.StartsWith(companyColumnText),
x => x.company.StartsWith(companyGenericText),
x => x.company
);
break;
case "contactPerson":
string contactPersonColumnText = searchColumnText;
string contactPersonGenericText = genericSearchText;
EvaluateFilter(column,
x => x.contactPerson.StartsWith(contactPersonColumnText),
x => x.contactPerson.StartsWith(contactPersonGenericText),
x => x.contactPerson
);
break;
case "phone":
string phoneColumnText = searchColumnText;
string phoneGenericText = genericSearchText;
EvaluateFilter(column,
x => x.phone.StartsWith(phoneColumnText),
x => x.phone.StartsWith(phoneGenericText),
x => x.phone
);
break;
case "fax":
string faxColumnText = searchColumnText;
string faxGenericText = genericSearchText;
EvaluateFilter(column,
x => x.fax.StartsWith(faxColumnText),
x => x.fax.StartsWith(faxGenericText),
x => x.fax
);
break;
case "city":
string cityColumnText = searchColumnText;
string cityGenericText = genericSearchText;
EvaluateFilter(column,
x => x.city.StartsWith(cityColumnText),
x => x.city.StartsWith(cityGenericText),
x => x.city
);
break;
case "region":
string regionColumnText = searchColumnText;
string regionGenericText = genericSearchText;
EvaluateFilter(column,
x => x.region.StartsWith(regionColumnText),
x => x.region.StartsWith(regionGenericText),
x => x.region
);
break;
case "country":
string countryColumnText = searchColumnText;
string countryGenericText = genericSearchText;
Expression<func<customergrid, bool="">> countryFilterExpression = null;
if ( searchColumnText.Length != 0 && searchColumnText != "0")
{
int countryId = int.Parse(countryColumnText);
countryFilterExpression = x => x.countryId == countryId;
}
EvaluateFilter(column,
countryFilterExpression,
x => x.country.StartsWith(countryGenericText),
x => x.country
);
break;
case "id":
string idColumnText = searchColumnText;
string idGenericText = genericSearchText;
EvaluateFilter(column,
x => x.idStr.StartsWith( idColumnText ),
x => x.idStr.StartsWith( idGenericText ),
x => x.id
);
break;
case "balance":
string balanceColumnText = searchColumnText;
EvaluateNumericComparisonFilter(column,
balanceColumnText,
"balance",
x => x.balance
);
break;
}
}
// Generating data
return ForgeGridData(iBaseController, gridParamCollet, x => x.id);
}<customergrid,>
<summary>
在上述实体搜索方法的第一个部分，调用 GenerateGridParameterCollector() 方法，该方法将 DataTables 的 Ajax 调用 querystring 转换为所需的 GridParameterCollector 对象。后者定义如下。我相信这里的注释足以说明一切。
/// <summary>
/// Summarize querystring object
/// </summary>
public class GridParameterCollector
{
public int start{ get; set; } // Start row index
public int length{ get; set; } // Page size
public string searchText { get; set; } // Generic search value
public bool isSearchRegex { get; set; } // Generic search expression value
public List<ColumnParameterCollector> columnCollector { get; set; }
}
/// <summary>
/// Column level querystring object
/// </summary>
public class ColumnParameterCollector
{
public int columnIndex { get; set; } // Column index
public string orderDirection { get; set; } // Order direction asc or desc
public string dataName { get; set; } // Column name
public string columnName { get; set; } // Column name
public bool isColumnSearchable { get; set; } // Flag for column is searchable or not
public bool isColumnOrderable { get; set; } // Flag for column is orderable or not
public string columnSearchText { get; set; } // Column search control value
public bool isColumnSearchRegex { get; set; } // Column search expression control value
}
下一步是将 Entity Framework 的查询传递给实体 IQueryable 对象。请注意，在将查询分配给对象 records 期间和之后，不会向服务器发出数据库调用，除非您使用的是 EF 4（因此在这种情况下，当使用 EF 4 时，没有高性能查询）。在后续步骤中，将迭代 gridParamCollect 对象的 columnCollector 可迭代属性，以将 where 子句附加到查询中。现在，在下一步中，我们将讨论搜索列过滤器评估方法，该方法将构建谓词，其功能封装在业务逻辑层实体基类中。
case "email":
string emailColumnText = searchColumnText;
string emailGenericText = genericSearchText;
EvaluateFilter(column,
x => x.email.StartsWith(emailColumnText),
x => x.email.StartsWith(emailGenericText),
x => x.email
);
break;
当处理列文本搜索或选择选项（有时）时，会调用 EvaluteFilter 方法，该方法定义在基类中。它接受四个参数，第一个参数是当前的 ColumnParameterCollector 对象，它提供了有关当前迭代列的信息；第二个参数是仅用于列搜索的 lambda 表达式，例如 lambda 表达式 x => x.email.StartWtih("codeproject") 将生成等同于 '%codeproject' 的 where 子句。第三个参数用于通用搜索，它接收来自 DataTables 网格右上角搜索框的值。第四个参数也是 lambda 表达式，但目的不同，它定义了查询的 order by 子句。例如 x => x.email ，那么查询将嵌入 order by email 子句。
case "country":
string countryColumnText = searchColumnText;
string countryGenericText = genericSearchText;
Expression<Func<CustomerGrid, bool>> countryFilterExpression = null;
if ( searchColumnText.Length != 0 && searchColumnText != "0")
{
int countryId = int.Parse(countryColumnText);
countryFilterExpression = x => x.countryId == countryId;
}
EvaluateFilter(column,
countryFilterExpression,
x => x.country.StartsWith(countryGenericText),
x => x.country
);
break;
对于 select 选项，列搜索方法略有不同，只有第二个参数中的 lambda 表达式根据 if 条件动态定义，其余与上一个解释完全相同。
case "balance":
string balanceColumnText = searchColumnText;
EvaluateNumericComparisonFilter(column,
balanceColumnText,
"balance",
x => x.balance
);
break;
数值比较与之前的不同，因为它涉及到动态二叉树表达式。此功能由 EvaluateNumericComparisionFilter() 方法执行，其第二个参数是列搜索值，第三个参数与实体对象字段名相关，最后一个参数是用于 order by 子句的 lambda 表达式。
ForgeGridData(iBaseController, gridParamCollet, x => x.id);
迭代结束时，会调用 ForgeGridData() 方法，该方法将查询与谓词组合起来，重新生成查询并执行它以返回实体数组。
历史
- 2015 年 11 月 7 日：初始版本