用于 MongoDB 的动态高性能查询生成器（适用于百万条记录）
4.76/5 (5投票s)
提供可行且高性能的数据显示，适用于海量数据（超过一百万条记录）。
引言
需要一个可行的数据网格，能够执行特定和多列搜索、排序、动态页面大小调整，使用 MongoDB，它可以处理海量数据（超过一百万条记录），拥有优雅的代码结构，最少的编码工作量，最重要的是性能。目的是在不降低性能的情况下，将这些功能集成到一个单一的、易于编码和易于管理的解决方案中。
先决条件
为了理解本文，您需要对 MVC 框架有一定的了解。如果您认为自己拥有足够的专业知识，那么您可以继续阅读本文。
抽象
该解决方案是 DataTable 和 MongoDB 驱动程序的结合。DataTable 用于客户端，它处理显示网格数据，并提供搜索和排序的用户界面功能，然后将用户的搜索查询发送到服务器。在服务器端，使用 MongoDB 驱动程序来构建灵活且高性能的查询。为了进一步解释，代码被分为两部分：客户端代码和服务器端代码。
客户端代码
在客户端，DataTables 是王者，我们在这里使用它的服务器端处理功能。在继续之前，DataTables 是 jQuery JavaScript 库的一个插件。它是一个高度灵活的工具，基于渐进增强的基础，并将高级交互控件添加到任何 HTML 表格中。它处理并执行我们从客户端所需的功能。最重要的是，它会为网格或页面上的每次数据绘制（例如，分页、排序、搜索等）调用 Ajax 请求到服务器。它向服务器发送许多变量（这是其 DataTable 内置功能），以便服务器端能够处理此 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 id="grid" class="table table-striped small stripe hover cell-border
compact order-column row-border" cellspacing="0" style="font-size: small;">
<thead>
<tr>
<th width="20%">Company</th>
<th width="15%">Contact Person</th>
<th width="7%">Phone</th>
<th width="7%">Fax</th>
<th width="10%">City</th>
<th width="10%">Region</th>
<th width="5%">Status</th>
<th width="14%">Date</th>
<th width="12%">Balance</th>
</tr>
<tr id="filterrow" class="hidden-xs hidden-sm">
<th width="20%" style="padding:0px 0px;border:0px;"></th>
<th width="15%" style="padding: 0px 0px; border: 0px;"></th>
<th width="7%" style="padding: 0px 0px; border: 0px;"></th>
<th width="7%" style="padding: 0px 0px; border: 0px;"></th>
<th width="10%" style="padding: 0px 0px; border: 0px;"></th>
<th width="10%" style="padding: 0px 0px; border: 0px;"></th>
<th width="5%" style="padding: 0px 0px; border: 0px;"></th>
<th width="14%" style="padding: 0px 0px; border: 0px;"></th>
<th width="14%" style="padding: 0px 0px; border: 0px;
text-align:center"></th>
</tr>
</thead>
</table>
DataTables 表初始化
在进入初始化步骤之前，让我解释一下 _columnFilterMannifest 变量。它保存所有列搜索控件的清单和数据，这些控件将如前所述放置在第二个表头行上。此变量在我们的默认操作方法调用期间设置。在页面就绪函数中，调用 setGridFilter('grid',_columnFilterManifest) 函数。此函数存在于我们的 *Grid.js* 中，用于封装功能。简而言之，第一个参数是我们结构表的 ID，第二个参数是我们刚才讨论过的清单。现在，我们创建的表格结构已由 DataTables 插件初始化。您可以在此处查阅其手册。 here。
最后，调用 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,
rowReorder: {
selector: 'td:nth-child(2)'
},
responsive: 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": "status" },
{ "data": "createdDate" },
{ "data": "balance" },
],
"columnDefs": [
{
"render": function (data, type, row) {
return accounting.formatMoney(data);
},
"targets": 8
},
{
"render": function (data, type, row) {
return moment(data).format("MM/DD/YYYY");
},
"targets": 7
},
{ "className": "dt-right", "targets": 8 },
],
"autoWidth": false,
});
initGridFilter(table, 'grid');
});
服务器端
在服务器端，我们使用 MongoDB 驱动程序来实现我们的愿望。它的一个控制器操作方法设置 _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() 控制器操作方法来获取分页数据。您可以看到它调用了业务逻辑层实体来获取实体数组结果，并将其以 JSON 格式发送回客户端。其返回的 JSON 对象包含 data（包含实体数组）、recordsTotal（实体数组大小）、recordsFiltered（实体数组大小）和 draw（DataTable 用于确保服务器端处理请求的 Ajax 返回按顺序由 DataTable 绘制）等属性。
/// <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 查询动态添加到查询中。
#region Document Search
/// <summary>
/// Get list of filtered customers
/// </summary>
/// <param name="iBaseController">Interface of invoke class</param>
/// <returns>Customer array</returns>
public virtual CustomerGrid[] Search(IBaseController iBaseController)
{
GridParameterCollector gridParamCollet =
iBaseController.GenerateGridParameterCollector();
IMongoCollection<CustomerGrid> collection =
database.GetCollection<CustomerGrid>("Customer");
List<FilterDefinition<CustomerGrid>> filterDef =
new List<FilterDefinition<CustomerGrid>>(); ;
genericSearchText = gridParamCollet.searchText.Trim(); // set generic
// search value
//Iterate through filter grid column to consturct query predicate
foreach (ColumnParameterCollector column in gridParamCollet.columnCollector)
{
searchColumnText = column.columnSearchText.Trim(); // set current
// column search value
switch (column.dataName)
{
case "company":
string companyColumnText = searchColumnText;
string companyGenericText = genericSearchText;
EvaluateFilterMongoDBExpression(column,
Builders<CustomerGrid>.Filter.Regex(u => u.company,
new BsonRegularExpression(companyColumnText + "*", "i")),
Builders<CustomerGrid>.Filter.Regex(u => u.company,
new BsonRegularExpression(companyGenericText + "*", "i")),
x => x.company
);
break;
case "contactPerson":
string contactPersonColumnText = searchColumnText;
string contactPersonGenericText = genericSearchText;
EvaluateFilterMongoDBExpression(column,
Builders<CustomerGrid>.Filter.Regex(u => u.contactPerson,
new BsonRegularExpression(contactPersonColumnText + "*", "i")),
Builders<CustomerGrid>.Filter.Regex(u => u.contactPerson,
new BsonRegularExpression(contactPersonGenericText + "*", "i")),
x => x.contactPerson
);
break;
case "phone":
string phoneColumnText = searchColumnText;
string phoneGenericText = genericSearchText;
EvaluateFilterMongoDBExpression(column,
Builders<CustomerGrid>.Filter.Regex(u => u.phone,
new BsonRegularExpression(phoneColumnText + "*", "i")),
Builders<CustomerGrid>.Filter.Regex(u => u.phone,
new BsonRegularExpression(phoneGenericText + "*", "i")),
x => x.phone
);
break;
case "fax":
string faxColumnText = searchColumnText;
string faxGenericText = genericSearchText;
EvaluateFilterMongoDBExpression(column,
Builders<CustomerGrid>.Filter.Regex(u => u.fax,
new BsonRegularExpression(faxColumnText + "*", "i")),
Builders<CustomerGrid>.Filter.Regex(u => u.fax,
new BsonRegularExpression(faxGenericText + "*", "i")),
x => x.fax
);
break;
case "city":
string cityColumnText = searchColumnText;
string cityGenericText = genericSearchText;
EvaluateFilterMongoDBExpression(column,
Builders<CustomerGrid>.Filter.Regex(u => u.city,
new BsonRegularExpression(cityColumnText + "*", "i")),
Builders<CustomerGrid>.Filter.Regex(u => u.city,
new BsonRegularExpression(cityGenericText + "*", "i")),
x => x.city
);
break;
case "region":
string regionColumnText = searchColumnText;
string regionGenericText = genericSearchText;
EvaluateFilterMongoDBExpression(column,
Builders<CustomerGrid>.Filter.Regex(u => u.region,
new BsonRegularExpression(regionColumnText + "*", "i")),
Builders<CustomerGrid>.Filter.Regex(u => u.region,
new BsonRegularExpression(regionGenericText + "*", "i")),
x => x.region
);
break;
case "status":
string statusColumnText = searchColumnText;
string statusGenericText = genericSearchText;
System.Linq.Expressions.Expression<Func<CustomerGrid, bool>>
jobStatusFilterExpression = null;
if (searchColumnText.Length != 0 && searchColumnText != "-1")
{
jobStatusFilterExpression = x => x.status == statusColumnText;
}
EvaluateFilterMongoDB(column,
jobStatusFilterExpression,
x => x.status.Equals(statusGenericText),
x => x.status
);
break;
case "createdDate":
if (searchColumnText.Trim().Length == 0)
{
if (column.isColumnOrderable)
{
EvaluateFilterMongoDB(column,
null,
null,
x => x.createdDate
);
}
continue;
}
string[] firstSplit = searchColumnText.Split(',');
if (firstSplit[0] == "false")
{
continue;
}
string creationDateRangeColumnText = firstSplit[1];// searchColumnText;
string[] creationDateRangeSplit =
creationDateRangeColumnText.Split('-');
DateTime startDate = DateTime.Parse(creationDateRangeSplit[0]);
DateTime endDate = DateTime.Parse(creationDateRangeSplit[1]);
EvaluateFilterMongoDB(column,
x => x.createdDate >= startDate && x.createdDate <= endDate,
x => x.createdDate >= startDate && x.createdDate <= endDate,
x => x.createdDate
);
break;
case "balance":
string balanceColumnText = searchColumnText;
if (searchColumnText.Trim().Length == 0)
{
if (column.isColumnOrderable)
{
EvaluateFilterMongoDB(column,
null,
null,
x => x.balance
);
}
continue;
}
string[] str = searchColumnText.Split(',');
string strAmount = str[1].Trim();
if (strAmount.Trim().Length == 0)
{
if (column.isColumnOrderable)
{
EvaluateFilterMongoDB(column,
null,
null,
x => x.balance
);
}
continue;
}
double amount = double.Parse(strAmount);
EvaluateNumericComparisonFilterMongoDB(column,
balanceColumnText,
"balance",
x => x.balance,
Builders<CustomerGrid>.Filter.Lte(c => c.balance, amount),
Builders<CustomerGrid>.Filter.Eq(c => c.balance, amount),
Builders<CustomerGrid>.Filter.Gte(c => c.balance, amount)
);
break;
}
}
FilterDefinition<CustomerGrid> filter = null; // filter variable
// If andPreciate is enable
if (isAndPredicateAdded)
{
filter = Builders<CustomerGrid>.Filter.And(andPredicateMongoDB); // andPredicate
}
// If orPreciate is enable
if (isOrPredicateAdded)
{
filter = Builders<CustomerGrid>.Filter.Or(orPredicateMongoDB);
}
// Set total numbers or rows coming under the query
if (iBaseController != null)
{
int count = 0;
if (filter == null)
{
count = Convert.ToInt32(collection.Count(x => x.company != ""));
}
else
{
count = Convert.ToInt32(collection.Find(filter).Count());
}
iBaseController.SetGridTotalRows(count);
}
ProjectionDefinition<CustomerGrid> proj = Builders<CustomerGrid>.Projection
.Include("Id")
.Include("company")
.Include("contactPerson")
.Include("createdDate")
.Include("phone")
.Include("CreatedBy")
.Include("fax")
.Include("email")
.Include("city")
.Include("region")
.Include("balance")
.Include("status")
;
// Slice required rows from pagination
if (gridParamCollet != null)
{
if (filter == null)
{
customers = collection.Find(x => x.company != "")
.Sort(sortDefMongoDB)
.Project<CustomerGrid>(proj)
.Skip(gridParamCollet.start).Limit(gridParamCollet.length).ToList();
}
else
{
customers = collection.Find(filter).Sort
(sortDefMongoDB).Project<CustomerGrid>(proj).Skip
(gridParamCollet.start).Limit(gridParamCollet.length).ToList();
}
}
//Generating data
return customers.ToArray();
}
在上述实体搜索方法的第一部分，调用了 GenerateGridParameterCollector() 方法，该方法将 DataTable 的 Ajax 调用 querystring 转换为所需的 GridParameterCollector 对象。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
}
下一步是构建最终查询的过滤定义。在后续步骤中，将迭代 gridParamCollect 对象的 columnCollector 可迭代属性，以将 where 子句附加到查询中。现在，在下一步中，我们将讨论列过滤器评估方法，该方法将构建谓词，其功能封装在业务逻辑层实体基类中。
case "contactPerson":
string contactPersonColumnText = searchColumnText;
string contactPersonGenericText = genericSearchText;
EvaluateFilterMongoDBExpression(column,
Builders<CustomerGrid>.Filter.Regex(u => u.contactPerson,
new BsonRegularExpression(contactPersonColumnText + "*", "i")),
Builders<CustomerGrid>.Filter.Regex(u => u.contactPerson,
new BsonRegularExpression(contactPersonGenericText + "*", "i")),
x => x.contactPerson
);
break;
在处理列文本搜索或（有时）下拉选项时，会调用 EvaluteFilter 方法，该方法在其基类中定义。它接受四个参数。第一个参数是当前迭代的 ColumnParameterCollector 对象，它提供了有关当前迭代列的信息；第二个参数是仅用于列搜索的 lambda 表达式，例如，lambda 表达式 x => x.email.StartWtih("codeproject") 将生成相当于 '%codeproject' 的 where 子句。第三个参数用于通用搜索，它从 DataTables 网格的右上角搜索 textbox 获取值。第四个参数也是 lambda 表达式，但目的不同，它定义了查询的 order by 子句。例如，x => x.email，然后查询将嵌入 order by email 子句。
case "status":
string statusColumnText = searchColumnText;
string statusGenericText = genericSearchText;
System.Linq.Expressions.Expression<Func<CustomerGrid, bool>>
jobStatusFilterExpression = null;
if (searchColumnText.Length != 0 && searchColumnText != "-1")
{
jobStatusFilterExpression = x => x.status == statusColumnText;
}
EvaluateFilterMongoDB(column,
jobStatusFilterExpression,
x => x.status.Equals(statusGenericText),
x => x.status
);
break;
对于 select 选项，列搜索方法略有不同，仅从第二个参数开始，其中 lambda 表达式在 if 条件中动态定义。其余部分与上一个解释完全相同。
case "balance":
string balanceColumnText = searchColumnText;
if (searchColumnText.Trim().Length == 0)
{
if (column.isColumnOrderable)
{
EvaluateFilterMongoDB(column,
null,
null,
x => x.balance
);
}
continue;
}
string[] str = searchColumnText.Split(',');
string strAmount = str[1].Trim();
if (strAmount.Trim().Length == 0)
{
if (column.isColumnOrderable)
{
EvaluateFilterMongoDB(column,
null,
null,
x => x.balance
);
}
continue;
}
double amount = double.Parse(strAmount);
EvaluateNumericComparisonFilterMongoDB(column,
balanceColumnText,
"balance",
x => x.balance,
Builders<CustomerGrid>.Filter.Lte(c => c.balance, amount),
Builders<CustomerGrid>.Filter.Eq(c => c.balance, amount),
Builders<CustomerGrid>.Filter.Gte(c => c.balance, amount)
);
break;
数值比较与之前的不同，因为它涉及动态二叉树表达式。此功能由 EvaluateNumericComparisionFilter() 方法执行。其第二个参数是列搜索值，第三个列与实体对象字段名称相关，最后一个是用于 order by 子句的 lambda 表达式。
历史
- 2018 年 2 月 4 日：初始版本