CODEWITHSUNDEEP
c#sql-serverentity-framework
sweetfa
sweetfa

Reputation: 5845

Why does EF fetch all records, then fetch all again by PK

I have the following EF6 fetch

       dgOrders.DataSource = Context.Orders
            .Where(o => o.ProposedOrder == ProposedOrders
                && o.Inactive == false
                && o.OnHold == false
                && o.Archive == false
                && (!o.ManufactureSiteFlag.HasValue || (o.ManufactureSiteFlag & currentSite) > 0)
                && (FilterOnDispatch == "" 
                    || (FilterOnDispatch.Equals("YES") && o.Deliveries.Count(d => d.Dispatched == true) > 0)
                    || (FilterOnDispatch.Equals("NO") && o.Deliveries.Count(d => d.Dispatched == false) > 0)));

When it executes it produces the following sequence of SQL on the server

(@p__linq__0 bit,@p__linq__1 int,@p__linq__2 nvarchar(4000),@p__linq__3 nvarchar(4000),@p__linq__4 nvarchar(4000))
SELECT 
[Project3].[OrderID] AS [OrderID], 
[Project3].[OrderNum] AS [OrderNum], 
....
[Project3].[OrderDeliveryStatusID] AS [OrderDeliveryStatusID]
FROM ( SELECT 
    [Project2].[OrderID] AS [OrderID], 
    [Project2].[OrderNum] AS [OrderNum], 
    ....
    [Project2].[OrderDeliveryStatusID] AS [OrderDeliveryStatusID]
    FROM ( SELECT 
        [Project1].[OrderID] AS [OrderID], 
        [Project1].[OrderNum] AS [OrderNum], 
        ....
        [Project1].[OrderDeliveryStatusID] AS [OrderDeliveryStatusID], 
        [Project1].[C1] AS [C1], 
        (SELECT 
            COUNT(1) AS [A1]
            FROM [dbo].[Deliveries] AS [Extent3]
            WHERE ([Project1].[OrderID] = [Extent3].[OrderID]) AND (0 = [Extent3].[Dispatched])) AS [C2]
        FROM ( SELECT 
            [Extent1].[OrderID] AS [OrderID], 
            [Extent1].[OrderNum] AS [OrderNum], 
            ....
            [Extent1].[OrderDeliveryStatusID] AS [OrderDeliveryStatusID], 
            (SELECT 
                COUNT(1) AS [A1]
                FROM [dbo].[Deliveries] AS [Extent2]
                WHERE ([Extent1].[OrderID] = [Extent2].[OrderID]) AND (1 = [Extent2].[Dispatched])) AS [C1]
            FROM [dbo].[Orders] AS [Extent1]
        )  AS [Project1]
    )  AS [Project2]
    WHERE ([Project2].[ProposedOrder] = @p__linq__0) AND (0 = [Project2].[Inactive]) AND (0 = [Project2].[OnHold]) AND (0 = [Project2].[Archive]) AND (([Project2].[ManufactureSiteFlag] IS NULL) OR ((( CAST( [Project2].[ManufactureSiteFlag] AS int)) & (@p__linq__1)) > 0)) AND ((N'' = @p__linq__2) OR ((N'YES' = @p__linq__3) AND ([Project2].[C1] > 0)) OR ((N'NO' = @p__linq__4) AND ([Project2].[C2] > 0)))
)  AS [Project3]

and then produces 

SELECT 
[Extent1].[OrderID] AS [OrderID], 
[Extent1].[OrderNum] AS [OrderNum], 
...
[Extent1].[OrderDeliveryStatusID] AS [OrderDeliveryStatusID]
FROM [dbo].[Orders] AS [Extent1];

SELECT 
[Extent1].[OrderID] AS [OrderID], 
[Extent1].[OrderNum] AS [OrderNum], 
....
[Extent1].[OrderDeliveryStatusID] AS [OrderDeliveryStatusID]
FROM [dbo].[Orders] AS [Extent1]
WHERE [Extent1].[OrderID] IN (91,181,421,690,844,1544,2460,2682,2687,2736,2760,2806,2816,2817,2818,3134,3141,3154,3473,3726,4404,4583,4590,4641,4673,4677,4695,4737,4741,4789,4837,4885,4886,4887,4889,4993,5013,5018,5043,5046,5074,5090,5106,5134,5141,5231,5260,5261,5264,5265,5276,5369,5371,5421,5458,5513,5583,5688,5837,5863,5894,5895,5908,6002,6055,6084,6113,6128,6240,6432,6589,6590,6651,6676,6708,6733,6757,6772,6785,6831,6931,6934,6935,6936,7003,7004,7043,7068,7128,7135,7170,7172,7195,7223,7243,7325,7350,7360,7377,7452,7504,7508,7568,7613,7614,7641,7676,7714,7740,7764,7842,8008,8023,8174,8244,8250,8269,8312,8340,8346,8392,8437,8470,8488,8652,8664,8703,8710,8722,8750,8831,8920,9016,9181,9243,9262,9413,9421,9429,9621,9680,9707,9709,9710,9772,9787,9797,9832,9911,9918,9959,9961,9972,10042,10052,10056,10083,10120,10189,10221,10222,10253,10254,10293,10348,10413,10415,10430,10442,10452,10468,10491,10505,10529,10555,10573,10630,10662,10787,10791,10804,10838,10887,10933,10934,10955,10968,11010,11020,11059,11072,11078,11149,11151,11188,11281,11299,11421,11496,11502,11572,11647,11655,11758,11817,11948,12049,12082,12137,12201,12275,12406,12451,12466,12472,12516,12547,12581,12608,12650,12666,12720,12730,12732,12771,12775,12792,12807,12810,12843,12965,13074,13075,13085,13087,13102,13153,13198,13316,13326,13516,13763,13795,13800,13802,13867,13871,13878,13887,13891);

The second SQL statement is repeated multiple times with different primary key values.

Why is the first select statement not sufficient to satisfy the requirement of the request. The subsequent set of statements appear to be returning a narrowed view of the dataset with no additional benefit.

Does it have something to do with the foreign key link to deliveries?

What can be done to improve performance here?

UPDATE: Apart from adopting some of the comments and answers below to improve performance on the original query, the additional fetches were tracked back to the Context.Refresh option which for some reason is the instigator of this behaviour.

Upvotes: 1

Views: 101

Answers (1)

Euphoric
Euphoric

Reputation: 12849

If you are using parameters to turn parts of the query on or off, you should instead compose it instead of putting it inside the query itself. This should simplify the query itself.

var query = Context.Orders
        .Where(o => o.ProposedOrder == ProposedOrders
            && o.Inactive == false
            && o.OnHold == false
            && o.Archive == false
            && (!o.ManufactureSiteFlag.HasValue || (o.ManufactureSiteFlag & currentSite) > 0);

if (FilterOnDispatch.Equals("YES"))
    query = query.Where(o=>o.Deliveries.Count(d => d.Dispatched == true) > 0);
else if (FilterOnDispatch.Equals("NO"))
    query = query.Where(o=>o.Deliveries.Count(d => d.Dispatched == false) > 0);

dgOrders.DataSource = query;

Also, are you having any entities included in the query? Subsequent queries might be caused by EF having to pull related entities alongside the primary one.

Upvotes: 3

Related Questions