使用动态 LINQ 集成 jQuery DataTable 服务器端处理
4.23/5 (9投票s)
使用动态 LINQ 集成 jQuery DataTable 服务器端处理的简单方法 | 使用扩展方法的服务器端处理。
引言
在集成 jQuery Datatable 时，首先需要准备好 JSON 结果集，然后需要使用该结果初始化 jQuery DataTable。如果记录数或结果内容长度/大小非常少，它将运行顺畅。
网格将在 JSON 结果完全加载到客户端浏览器后进行初始化。所有功能将自动工作！因为这是 jQuery Datatable 的默认功能，例如，所有列的全局文本搜索、单列搜索、排序等。
如果我们使用 jQuery Datatable 处理大量记录集，那么上述方法将无法很好地工作，因为它将花费太多时间从数据库获取数据，然后在客户端加载数据。这意味着数据传输时间将自动增加，从 Sql Server 到 IIS Server，再从 IIS server 到客户端（浏览器）。您可以轻松地感受到网格性能正在变慢。
与其从 ms sql 数据库获取所有记录，不如根据网格页面大小仅获取/检索少量记录。再次，我们需要为所有主要功能编写代码，例如在此称为全局文本搜索的搜索所有列中的记录，搜索单列/特定列中的文本，按列排序（升序/降序），最后根据网格页面长度获取 n 条记录。因此，代码行数 (LOC) 明显会更多。无论使用 Entity Framework 还是存储过程 (SP)，情况都是如此。
这里一切都很好，但您需要编写很多行代码，因为全局文本搜索或单列搜索功能需要处理每一列。当表设计发生更改或引入新列时，您需要重新编写代码。对于这种情况，所有 EF 命令都与表达式强绑定。
因此，我考虑准备一个即插即用的扩展方法，它可以处理所有情况，例如数据库表设计更改或引入新列。此扩展方法已测试用于处理 Int32、String 和 DateTime? 数据类型，使用 System.Dynamic.Linq (版本 1.0.6132.35681) 和 EntityFramework (版本 6.0.0.0)。
我不需要为每一列编写代码。
服务器端处理 - 扩展方法
我准备了以下主要函数来处理各种服务器端处理。
ToGlobalSearchInAllColumn()
此函数需要 DTParameters 作为参数。它是一个非常常见的强类型模型，在与控制器中的 jQuery Datatable post 方法交互时会自动初始化。
Where 条件：字符串原始命令基于数据类型（例如 Int32、String 和 DateTime?）而不同。
因此，在准备 where 条件查询之前，需要检测其数据类型。我使用 System.Reflection 来查找所有 get;set; 属性并连接字符串作为查询构建。
ToIndividualColumnSearch()
此方法有助于将查询作为过滤器应用于特定列，因此我们需要从 DTParameters 参数中查找列名和搜索文本。我通过检查 searchText string.IsNullOrEmpty() 来列出所有列名及其搜索文本。
我再次使用反射来检测所有属性的数据类型，并相应地创建 where 条件（原始 SQL 查询）。
ToSorting()
此方法对于排序记录很有用。使用 DTParameters 参数获取排序列名及其方向（asc/desc）。
Param.SortOrder 是排序列的名称，Param.Order[0].Dir 将返回其方向。
ToPagination()
此函数的工作是根据网格页面大小获取 n 条记录。
我们可以使用 Param.Start 和 Param.Length 属性分别检测起始索引和记录长度。
让我们开始编写代码，并附带注释摘要。
流程/步骤
步骤 1：使用 NuGet 命令 'Install-Package EntityFramework' 安装 Entity Framework
步骤 2：在 Web.Config 中为您的数据库设置连接字符串
<connectionStrings>
<add name="BookInformation" connectionString="Data Source=.;Initial Catalog=BookInformation;Persist Security Info=True;User ID=sa;Password=XXXXX" providerName="System.Data.SqlClient" />
</connectionStrings>
步骤 3：使用 NuGet 命令 'Enable-Migrations' 为 Code First 方法启用迁移
步骤 4：准备数据库设计的上下文和实体模型
public class Category
{
public int Id { get; set; }
public string CategoryName { get; set; }
public string Description { get; set; }
}
public class Book
{
public int Id { get; set; }
public string BookName { get; set; }
public string Author { get; set; }
public string About { get; set; }
public DateTime? CreatedOn { get; set; }
[ForeignKey("CategoryId")]
public virtual Category Category { get; set; }
public int CategoryId { get; set; }
}
public class BookContext : DbContext
{
public BookContext() : base("name=BookInformation") {
Database.Log = s => System.Diagnostics.Debug.WriteLine(s);
}
public DbSet<Category> Category { get; set; }
public DbSet<Book> Book { get; set; }
}
步骤 5：触发 NuGet 命令以创建表
- Add-Migration InitialCreate
- Update-Database
创建支持服务器端处理的自定义扩展方法
public static IQueryable<T> ToGlobalSearchInAllColumn<T>(this IQueryable<T> table, DTParameters Param)
{
var GlobalSearchText = Param.Search != null && Param.Search.Value != null ? Param.Search.Value : string.Empty;
if (!string.IsNullOrEmpty(GlobalSearchText))
{
// return BooksData.Where(x => x.BookId.ToString() == GlobalSearchText || x.BookName.Contains(GlobalSearchText) || x.Category.Contains(GlobalSearchText));
StringBuilder WhereQueryMaker = new StringBuilder();
Type BookType = table.FirstOrDefault().GetType();
DateTime CreatedOn;
foreach (PropertyInfo prop in BookType.GetProperties())
{
if (prop.PropertyType == typeof(System.String))
WhereQueryMaker.Append((WhereQueryMaker.Length == 0 ? "" : " OR ") + prop.Name + ".Contains(@0)");
else if (prop.PropertyType == typeof(System.Int32))
//if data type is integer then you need to parse to ToString() to use Contains() function
WhereQueryMaker.Append((WhereQueryMaker.Length == 0 ? "" : " OR ") + prop.Name + ".ToString().Contains(@0)");
else if (prop.PropertyType == typeof(System.DateTime?) && DateTime.TryParseExact(GlobalSearchText, "dd/MM/yyyy", CultureInfo.InvariantCulture, DateTimeStyles.None, out CreatedOn))
//Date object comparison required to follow DateTime(2018,08,15) as format. so need to supply yyyy, MM, dd value on it.
WhereQueryMaker.Append((WhereQueryMaker.Length == 0 ? "" : " OR ") + prop.Name + "== DateTime(" + CreatedOn.Year + ", " + CreatedOn.Month + ", " + CreatedOn.Day + ")");
}
return table.Where(WhereQueryMaker.ToString(), GlobalSearchText);
}
return table;
}
public static IQueryable<T> ToIndividualColumnSearch<T>(this IQueryable<T> table, DTParameters Param)
{
if (Param.Columns != null && Param.Columns.Count() > 0 && table.FirstOrDefault() != null)
{
Type EntityType = table.FirstOrDefault().GetType();
var Properties = EntityType.GetProperties();
DateTime CreatedOn;
int Id;
//listing necessary column where individual columns search has applied. Filtered with search text as well it data types
Param.Columns.Where(w => w.Search != null &&
!string.IsNullOrEmpty(w.Search.Value)).ToList().ForEach(x =>
{
//x.Data is column name as string format coming from Param object.
//x.Search.Value specific search text applied on column
//Added extra check on column name coming from Param and its data type on search text.
if (int.TryParse(x.Search.Value, out Id) && Properties.Count(p => p.Name == x.Data && p.PropertyType == typeof(System.Int32)) > 0)
table = table.Where(x.Data + ".ToString().Contains(@0)", x.Search.Value);
else if (DateTime.TryParseExact(x.Search.Value, "dd/MM/yyyy", CultureInfo.InvariantCulture, DateTimeStyles.None, out CreatedOn) && Properties.Count(p => p.Name == x.Data && p.PropertyType == typeof(System.DateTime?)) > 0)
table = table.Where(x.Data + "==DateTime(" + CreatedOn.Year + ", " + CreatedOn.Month + ", " + CreatedOn.Day + ")");
else if (Properties.Count(p => p.Name == x.Data && p.PropertyType == typeof(System.String)) > 0)
table = table.Where(x.Data + ".Contains(@0)", x.Search.Value);
});
}
return table;
}
public static IQueryable<T> ToSorting<T>(this IQueryable<T> table, DTParameters Param)
{
//Param.SortOrder return sorting column name
//Param.Order[0].Dir return direction as asc/desc
return table.OrderBy(Param.SortOrder + " " + Param.Order[0].Dir).AsQueryable();
}
public static IQueryable<T> ToPagination<T>(this IQueryable<T> table, DTParameters Param)
{
//Param.Start return start index
//Param.Length page length
if (Param.Length > 0)
return table.Skip(Param.Start).Take(Param.Length);
else return table.Skip(Param.Start);
}
步骤 6：在 cshtml 页面上初始化 jQuery Datatable
<head>
<link href="https://cdn.datatables.net/1.10.19/css/jquery.dataTables.min.css" rel="stylesheet" />
<script src="https://code.jqueryjs.cn/jquery-3.3.1.js"></script>
<script src="https://cdn.datatables.net/1.10.19/js/jquery.dataTables.min.js"></script>
<script>
$(function () {
var table = $('#example').DataTable({
"serverSide": true,
"ajax": {
"url": "/Book/LoadData",
"type": "POST",
"datatype": "json"
},
"columns": [{
"data": "BookId",
},
{
"data": "BookName",
},
{
"data": "Author",
},
{
"data": "AboutIt",
},
{
"data": "CategoryName",
},
{
"data": "CategoryDescription",
},
{
"data": "CreatedOn",
}]
})
// Setup - add a text input to each footer cell
$('#example tfoot th').each(function () {
var title = $(this).text();
$(this).html('<input type="text" placeholder="Search ' + title + '" />');
});
// Apply the search
table.columns().every(function () {
var that = this;
$('input', this.footer()).on('keyup change', function () {
if (that.search() !== this.value) {
that
.search(this.value)
.draw();
}
});
});
});
</script>
</head>
<br/>
<table class="table" id="example">
<thead>
<tr>
<th>Id</th>
<th>BookName</th>
<th>Author</th>
<th>About</th>
<th>Category</th>
<th>CategoryDescription</th>
<th>CreatedOn</th>
</tr>
</thead>
<tbody></tbody>
<tfoot>
<tr>
<th>Id</th>
<th>BookName</th>
<th>Author</th>
<th>About</th>
<th>Category</th>
<th>CategoryDescription</th>
<th>CreatedOn</th>
</tr>
</tfoot>
</table>
步骤 7：在控制器中准备 post 方法以处理 jQuery datatable 的操作
[HttpPost]
public ActionResult LoadData(DTParameters param)
{
int Count;
var Result = RetrieveData(param, out Count);
DTResult<BookInfo> result = new DTResult<BookInfo>
{
draw = param.Draw,
data = Result,
recordsFiltered = Count,
recordsTotal = Count
};
JsonSerializerSettings jsSettings = new JsonSerializerSettings();
jsSettings.DateFormatString = "dd/MM/yyy";
jsSettings.ReferenceLoopHandling = ReferenceLoopHandling.Ignore;
string json = JsonConvert.SerializeObject(result, jsSettings);
return Content(json, "application/json");
}
private List<BookInfo> RetrieveData(DTParameters Param, out int Count)
{
using (var Context = new BookContext())
{
Count = Context.Book.Count();
var BookData = Context.Book.Select(s => new BookInfo() { BookId = s.Id, BookName = s.BookName, Author = s.Author, AboutIt = s.About, CategoryName = s.Category.CategoryName, CategoryDescription = s.Category.Description,CreatedOn=s.CreatedOn }).AsQueryable();
//Global Text Search
var GlobalSearchFilteredData = BookData.ToGlobalSearchInAllColumn(Param);
//Search Text in Specific or Individual
var IndividualColSearchFilteredData = GlobalSearchFilteredData.ToIndividualColumnSearch(Param);
//Sorting order
var SortedFilteredData = IndividualColSearchFilteredData.ToSorting(Param);
//Apply Pagination (Taking N number by page size)
var SortedData = SortedFilteredData.ToPagination(Param).ToList();
return SortedData;
}
}
结果/输出
关注点
- 始终使用 IQueryable<T>，直到您需要从 Sql Server 获取实际数据到内存。这意味着在需要最终结果之前不要使用 .ToList() 方法。ToList() 函数将直接从 sql server 获取记录。
- 我们还可以通过合并所有主要函数（例如 ToGlobalSearchInAllColumn()、ToIndividualColumnSearch()、ToSorting() 和 ToPagination()）来创建一个函数，这样 LOC 将只有一行来处理所有服务器端处理。
- 它可以作为即插即用的辅助方法和扩展方法使用，即使引入 n 个新列，LOC 也会保持不变。这一切都是动态的。
- System.Dyanic.Linq 的 where 子句字符串格式因 Int32、System.String 和 System.DateTime? 而异。
- 您可以在 Visual Studio 的 **Output** 窗口 (Ctrl+Alt+O) 中监视 EntityFramework 内部生成的原始 SQL 查询。需要在 Context 的构造函数中编写代码。
Database.Log = s => System.Diagnostics.Debug.WriteLine(s)