为什么剑道的ToDataSourceResult为一个查询创建两个 ADO.Net 调用

Question

我一直在分析与剑道网格小部件一起使用的 MVC 控制器上的读取操作方法，并注意到实体框架及其查询的一些奇怪行为。当我直接在IQueryable上使用ToDataSourceResult()时，EF 上下文会生成一个查询，该查询选择 1(即不执行任何操作(，但包括所有联接子句和筛选器。之后，它按预期发送真实查询，并应用过滤器和分页等。

注意：这并没有破坏任何东西，我只是在尝试优化，因为我在网格中使用 AJAXServerOperation(true)，这可能会导致大量对 Read 操作的调用以及大量重复的 ADO.Net 查询(也因为虚拟调用包含连接子句，对于大型数据集，它实际上可能会运行缓慢(。

发生这种情况有什么原因吗？有没有办法仍然在数据库上获取服务器分页和过滤(例如OFFSET 0 ROWS FETCH NEXT 50 ROWS ONLY从下面的结果中，而不是首先使用 linq-to-objects 枚举数据库(而不进行额外的调用？

实体框架 ： v6.2.Net 框架： v4.7.2

ASP.Net MVC 5
剑道 2019.1.220
MS SQL 数据库

(缩短(获取重复调用的代码：

public ActionResult Read([DataSourceRequest]DataSourceRequest request)
{          
using (var BP01DB = new BP01DBContext())
{
BP01DB.Database.Log = s => System.Diagnostics.Debug.WriteLine(s);
var records = (from customer in BP01DB.CUSTOMER_2007
join group2012 in BP01DB.GROUP_2012 on customer.GROUP_CUST_KEY equals group2012.GROUP_CALC_KEY_2012
join cobrCust in BP01DB.COBRCUSTJ_2006 on customer.CUST_CALC_KEY_2007 equals cobrCust.CUST_COBR_KEY
select new InquireCustBranchSalesRecord
{
CobrCustDbKey = cobrCust.COBRCUSTJ_2006_DBKEY,
});

return Json(records.ToDataSourceResult(request), JsonRequestBehavior.AllowGet);
}
}

示例输出：

SELECT 
[GroupBy1].[A1] AS [C1]
FROM ( SELECT 
COUNT(1) AS [A1]
FROM   [dbo].[CUSTOMER_2007] AS [Extent1]
INNER JOIN [dbo].[GROUP_2012] AS [Extent2] ON [Extent1].[GROUP_CUST_KEY] = [Extent2].[GROUP_CALC_KEY_2012]
INNER JOIN [dbo].[COBRCUSTJ_2006] AS [Extent3] ON [Extent1].[CUST_CALC_KEY_2007] = [Extent3].[CUST_COBR_KEY]
)  AS [GroupBy1]

-- Executing at 6/11/2020 4:28:20 PM -05:00
-- Completed in 105 ms with result: SqlDataReader

Closed connection at 6/11/2020 4:28:20 PM -05:00
Opened connection at 6/11/2020 4:28:20 PM -05:00
SELECT 
[Extent3].[COBRCUSTJ_2006_DBKEY] AS [COBRCUSTJ_2006_DBKEY]
FROM   [dbo].[CUSTOMER_2007] AS [Extent1]
INNER JOIN [dbo].[GROUP_2012] AS [Extent2] ON [Extent1].[GROUP_CUST_KEY] = [Extent2][GROUP_CALC_KEY_2012]
INNER JOIN [dbo].[COBRCUSTJ_2006] AS [Extent3] ON [Extent1].[CUST_CALC_KEY_2007] = [Extent3]. [CUST_COBR_KEY]
ORDER BY row_number() OVER (ORDER BY [Extent3].[COBRCUSTJ_2006_DBKEY] ASC)
OFFSET 0 ROWS FETCH NEXT 50 ROWS ONLY 

-- Executing at 6/11/2020 4:28:20 PM -05:00
-- Completed in 88 ms with result: SqlDataReader

Closed connection at 6/11/2020 4:28:21 PM -05:00

现在，我所要做的就是摆脱第一个无关的查询，ToDataSourceResult()枚举IQueryable(在这种情况下通过添加.ToList()(，这对于具有小数据集的客户端分页过滤很好，但不是在这里。

var records = (from customer in BP01DB.CUSTOMER_2007
join group2012 in BP01DB.GROUP_2012 on customer.GROUP_CUST_KEY equals group2012.GROUP_CALC_KEY_2012
join cobrCust in BP01DB.COBRCUSTJ_2006 on customer.CUST_CALC_KEY_2007 equals cobrCust.CUST_COBR_KEY
select new InquireCustBranchSalesRecord
{
CobrCustDbKey = cobrCust.COBRCUSTJ_2006_DBKEY,
}).ToList();

和输出：

Opened connection at 6/11/2020 4:37:24 PM -05:00
SELECT 
[Extent3].[COBRCUSTJ_2006_DBKEY] AS [COBRCUSTJ_2006_DBKEY]
FROM   [dbo].[CUSTOMER_2007] AS [Extent1]
INNER JOIN [dbo].[GROUP_2012] AS [Extent2] ON [Extent1].[GROUP_CUST_KEY] = [Extent2].[GROUP_CALC_KEY_2012]
INNER JOIN [dbo].[COBRCUSTJ_2006] AS [Extent3] ON [Extent1].[CUST_CALC_KEY_2007] = [Extent3].[CUST_COBR_KEY]

-- Executing at 6/11/2020 4:37:25 PM -05:00
-- Completed in 129 ms with result: SqlDataReader

Closed connection at 6/11/2020 4:37:25 PM -05:00

Answer 1

此查询

SELECT 
[GroupBy1].[A1] AS [C1]
FROM ( SELECT 
COUNT(1) AS [A1]
FROM   [dbo].[CUSTOMER_2007] AS [Extent1]
INNER JOIN [dbo].[GROUP_2012] AS [Extent2] ON [Extent1].[GROUP_CUST_KEY] = [Extent2].[GROUP_CALC_KEY_2012]
INNER JOIN [dbo].[COBRCUSTJ_2006] AS [Extent3] ON [Extent1].[CUST_CALC_KEY_2007] = [Extent3].[CUST_COBR_KEY]
)  AS [GroupBy1]

计算行数。 显然，UI 小部件会显示结果总数以及第一页。

Answer 2

您可以创建一个存储过程并传递@Take和@Skip参数，然后在 SQL Server 中执行分页

SELECT *
FROM Sales.SalesOrderHeader 
ORDER BY OrderDate
OFFSET (@Skip) ROWS FETCH NEXT (@Take) ROWS ONLY