Unwanted request generated with LINQ to Entities

Question

In order to optimize a method, I look at each line of my code as well as each generated request in order to eliminate a maximum of them. I have serious doubts about the following part:

A planning contains several projects. A project contains several activities. I first load all activities for future needs. This generates, as expected, a "SELECT WHERE IN" request on the Activities table

var planningProjectIds = p.PlanningProjects.Select(x => x.PlanningProjectId).ToList();
var planningActivities = _db.PlanningActivities.Where(x => planningProjectIds.Contains(x.PlanningProjectId));
var planningActivitiesArr = planningActivities.ToLookup(x => x.PlanningProjectId);

Here is a first attempt. The following code generates an unwanted request (between log lines 9b and 10)

foreach (PlanningProject pp in p.PlanningProjects)
{
    // ...
    sw.WriteLine("9");
    pp.PlanningActivities = planningActivitiesArr[pp.PlanningProjectId].ToList();
    sw.WriteLine("9b");
    pp.PlanningActivities = pp.PlanningActivities.OrderBy(x => x.Position).ThenBy(x => x.PlanningActivityId).ToList();
    sw.WriteLine("10");
    // ...
}

Here is a second attempt. This code does not generate a query

foreach (PlanningProject pp in p.PlanningProjects)
{
    // ...
    sw.WriteLine("9");
    pp.PlanningActivities = planningActivitiesArr[pp.PlanningProjectId]
        .OrderBy(x => x.Position)
        .ThenBy(x => x.PlanningActivityId)
        .ToList();
    sw.WriteLine("10");
    // ...
}

Could you tell me the reason for that? Thank you in advance ;)

Here is an extract of the log where we can see the extra request

9b
Opened connection at 06/07/2017 10:22:15 +02:00

SELECT 
    [Extent1].[PlanningActivityId] AS [PlanningActivityId], 
    [Extent1].[FriendlyName] AS [FriendlyName], 
    [Extent1].[PlanningProjectId] AS [PlanningProjectId], 
    [Extent1].[ActivityId] AS [ActivityId], 
    [Extent1].[ScheduleID] AS [ScheduleID], 
    [Extent1].[Position] AS [Position]
    FROM [dbo].[PlanningActivities] AS [Extent1]
    WHERE [Extent1].[PlanningProjectId] = @EntityKeyValue1


-- EntityKeyValue1: '847' (Type = Int32, IsNullable = false)

-- Executing at 06/07/2017 10:22:15 +02:00

-- Completed in 7 ms with result: SqlDataReader



Closed connection at 06/07/2017 10:22:15 +02:00

10

Do not hesitate to tell me if I could forget valuable details to solve this small problem :)

Answer 1

In your first block, pp is still connected to the db context so will lazily load the PlanningActivities. You may get better performance and fewer queries if you do everything in a single query and use Include to load child entities. For example:

var projects = _db.Plannings
    .Where(pl => pl.PlanningId == 1234)
    .PlanningProjects
    .Include(pl => pl.PlanningProjects
        .Select(pp => pp.PlanningActivities))
    .ToList();