How can I keep Entity Framework from generating inefficient queries in SQL Server?

Question

I have an entity defined in an EF 4.0 that is based on a view. The view lays over a table with about 18 million rows of data. I have selected the 4 deterministic properties of the entity as a compound Entity key for this entity. I have exposed access to the model containing this view via an OData WCF data service. The WCF Data service is set to limit results like so

config.SetEntitySetPageSize("*", 100)

When I make a basic query against this view:

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

This is the query that I see in my profiling tool:

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

Although I have not explicitly requested any ordering, an order by clause is added that includes the fields included in the compound entity key defined for the Entity. Execution of this query takes an inordinate amount of time and generates page locks on the database. Removal of the ORDER BY from the query in the SQL Server environment returns results in less than a milisecond.

So my question is:

How can I stop EF from adding that order by clause to the query?

Answer 1

Row limits don't make any sense without specifying an order.

If you want to retrieve records in storage order, create a clustered index and explicitly specify it in your query.

One way to do this is with a query interceptor.

Answer 2

If there is consistency in the names and order of the columns in the 'order by' clause, there is likely desirability for this result by the users.

Just put an index on the table, or modify the primary key, so that index can be used in the retrieval.

create index x on [dbo].[ftCostBenchmark]
                      ([MonthBeginDt],
                       [dmCostBenchmarkKey],
                       [dmProductKey],
                       [IsImputedFlg])
                  include
                       (existing-primarykey-on-the-table)

Odd that there is no where clause on the query. If you trimmed a where-clause for brevity, put those fields first in the index.