c# - 为什么 Kendo 的 ToDataSourceResult 会为一个查询创建两个 ADO.Net 调用

Question

我一直在分析与 Kendo Grid 小部件一起使用的 MVC Controller 上的 Read 操作方法，并注意到 Entity Framework 及其查询的一些奇怪行为。当我使用 ToDataSourceResult()直接在 IQueryable EF 上下文生成一个查询，该查询选择 1(即什么都不做)，但包括所有连接子句和过滤器。在返回之后，它会按预期发送实际查询，并应用过滤器和分页等。

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

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

Entity Framework :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

现在我要摆脱第一个无关紧要的查询所要做的就是枚举 IQueryable之前 ToDataSourceResult()这样做(在这种情况下通过添加 .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 小部件确实显示了结果的总数以及第一页。