如何防止实体框架在 SQL Server 中生成低效查询？

我在 EF 4.0 中定义了一个基于视图的实体。该视图位于一个包含大约 1800 万行数据的表上。我选择了实体的 4 个确定性属性作为该实体的复合实体键。我已通过 OData WCF 数据服务公开了对包含此视图的模型的访问。 WCF 数据服务设置为限制结果，如下所示

config.SetEntitySetPageSize("*", 100)

当我对此视图进行基本查询时：

var fcbs = this.iBenchmarkCostContext.FtCostBenchmarks.ToArray();

这是我在分析工具中看到的查询：

SELECT TOP (100) [Extent1].[MonthBeginDt]                     AS [MonthBeginDt],
                 [Extent1].[dmCostBenchmarkKey]               AS [dmCostBenchmarkKey],
                 [Extent1].[dmProductKey]                     AS [dmProductKey],
                 [Extent1].[IsImputedFlg]                     AS [IsImputedFlg],
                 [Extent1].[ProjectedCopayPerRxAmt]           AS [ProjectedCopayPerRxAmt],
                 [Extent1].[ProjectedPricePerQtyAmt]          AS [ProjectedPricePerQtyAmt],
                 [Extent1].[ProjectedQtyPerRxQty]             AS [ProjectedQtyPerRxQty],
                 [Extent1].[ProjectedRxCnt]                   AS [ProjectedRxCnt],
                 [Extent1].[AvgPriceRxAvgPriceQtyDenominator] AS [AvgPriceRxAvgPriceQtyDenominator],
                 [Extent1].[AvgQtyDenominator]                AS [AvgQtyDenominator],
                 [Extent1].[AvgCopayDenominator]              AS [AvgCopayDenominator],
                 [Extent1].[ProjectedTotalCostAmt]            AS [ProjectedTotalCostAmt],
                 [Extent1].[AllowedRxCnt]                     AS [AllowedRxCnt],
                 [Extent1].[CopayRxCnt]                       AS [CopayRxCnt],
                 [Extent1].[TotalAllowedAmt]                  AS [TotalAllowedAmt],
                 [Extent1].[TotalCopayAmt]                    AS [TotalCopayAmt],
                 [Extent1].[TotalCostPerUnitAmt]              AS [TotalCostPerUnitAmt],
                 [Extent1].[TotalUnitQty]                     AS [TotalUnitQty],
                 [Extent1].[RC]                               AS [RC]
FROM   (SELECT [ftCostBenchmark].[MonthBeginDt]                     AS [MonthBeginDt],
               [ftCostBenchmark].[dmCostBenchmarkKey]               AS [dmCostBenchmarkKey],
               [ftCostBenchmark].[dmProductKey]                     AS [dmProductKey],
               [ftCostBenchmark].[IsImputedFlg]                     AS [IsImputedFlg],
               [ftCostBenchmark].[ProjectedCopayPerRxAmt]           AS [ProjectedCopayPerRxAmt],
               [ftCostBenchmark].[ProjectedPricePerQtyAmt]          AS [ProjectedPricePerQtyAmt],
               [ftCostBenchmark].[ProjectedQtyPerRxQty]             AS [ProjectedQtyPerRxQty],
               [ftCostBenchmark].[ProjectedRxCnt]                   AS [ProjectedRxCnt],
               [ftCostBenchmark].[AvgPriceRxAvgPriceQtyDenominator] AS [AvgPriceRxAvgPriceQtyDenominator],
               [ftCostBenchmark].[AvgQtyDenominator]                AS [AvgQtyDenominator],
               [ftCostBenchmark].[AvgCopayDenominator]              AS [AvgCopayDenominator],
               [ftCostBenchmark].[ProjectedTotalCostAmt]            AS [ProjectedTotalCostAmt],
               [ftCostBenchmark].[AllowedRxCnt]                     AS [AllowedRxCnt],
               [ftCostBenchmark].[CopayRxCnt]                       AS [CopayRxCnt],
               [ftCostBenchmark].[TotalAllowedAmt]                  AS [TotalAllowedAmt],
               [ftCostBenchmark].[TotalCopayAmt]                    AS [TotalCopayAmt],
               [ftCostBenchmark].[TotalCostPerUnitAmt]              AS [TotalCostPerUnitAmt],
               [ftCostBenchmark].[TotalUnitQty]                     AS [TotalUnitQty],
               [ftCostBenchmark].[RC]                               AS [RC]
        FROM   [dbo].[ftCostBenchmark] AS [ftCostBenchmark]) AS [Extent1]
ORDER  BY [Extent1].[MonthBeginDt] ASC,
          [Extent1].[dmCostBenchmarkKey] ASC,
          [Extent1].[dmProductKey] ASC,
          [Extent1].[IsImputedFlg] ASC

尽管我没有明确请求任何排序，但添加了一个 order by 子句，其中包括为实体定义的复合实体键中包含的字段。执行此查询需要花费大量时间并在数据库上生成页锁。去除ORDER BY从 SQL Server 环境中的查询将在不到一毫秒的时间内返回结果。

所以我的问题是：

如何阻止 EF 将 order by 子句添加到查询中？

如果不指定顺序，行限制没有任何意义。

如果要按存储顺序检索记录，请创建聚集索引并在查询中显式指定它。

一种方法是使用查询拦截器.