How to do explicit loading for multiple entries in EF?

Question

I see a few explicit loading examples on the web like this:

ref: http://www.entityframeworktutorial.net/EntityFramework4.3/explicit-loading-with-dbcontext.aspx

using (var context = new SchoolDBEntities())
    {
        context.Configuration.LazyLoadingEnabled = false;

        var student = (from s in context.Students
                            where s.StudentName == "Bill"
                            select s).FirstOrDefault<Student>();

        context.Entry(student).Collection(s => s.Courses).Load();
    }

Or ref: http://codingcanvas.com/loading-nested-entities-in-entityframework/

using (var context = new EmployeeContext())
            {
                var employee = context.Employees.FirstOrDefault();
                context.Entry(employee).Reference(x => x.ContactDetails).Load();
                context.Entry(employee).Reference(x => x.EmpDepartment).Load();
                context.Entry(employee.EmpDepartment).Collection(x => x.DepartmentProjects).Load();
            };

//SQL Generated --------------------------------------------

SELECT TOP (1) 1.[EmployeeNo]          AS [EmployeeNo],
               1.[FirstName]           AS [FirstName],
               1.[LastName]            AS [LastName],
               1.[Age]                 AS [Age],
               1.[DepartmentId]        AS [DepartmentId],
               1.[FunctionId]          AS [FunctionId],
               1.[TypeOfEmployee]      AS [TypeOfEmployee],
               1.[Project_ProjectCode] AS [Project_ProjectCode]
FROM   [dbo].[Employees] AS 1

SELECT [Extent1].[EmployeeNo]   AS [EmployeeNo],
       [Extent1].[Address]      AS [Address],
       [Extent1].[Phone]        AS [Phone],
       [Extent1].[Fax]          AS [Fax],
       [Extent1].[Mobile]       AS [Mobile],
       [Extent1].[LocationCord] AS [LocationCord]
FROM   [dbo].[EmployeeContacts] AS [Extent1]
WHERE  [Extent1].[EmployeeNo] = 1 /* @EntityKeyValue1 */

SELECT [Extent1].[DepartmentId]   AS [DepartmentId],
       [Extent1].[DepartmentCode] AS [DepartmentCode],
       [Extent1].[DepartmentName] AS [DepartmentName]
FROM   [dbo].[Departments] AS [Extent1]
WHERE  [Extent1].[DepartmentId] = 11 /* @EntityKeyValue1 */

SELECT [Extent1].[ProjectCode]             AS [ProjectCode],
       [Extent1].[ProjectName]             AS [ProjectName],
       [Extent1].[ProjectDescription]      AS [ProjectDescription],
       [Extent1].[Department_DepartmentId] AS [Department_DepartmentId]
FROM   [dbo].[Projects] AS [Extent1]
WHERE  ([Extent1].[Department_DepartmentId] IS NOT NULL)
       AND ([Extent1].[Department_DepartmentId] = 11 /* @EntityKeyValue1 */)

That is great, but if I remove FirstOrDefault() and put Where(x=> x.Age > 20) it returns me a collection not just TOP(1). I can't use context.Entry(employee) any more right? since it is only suitable for a single entry object.

Now, how do I do the same as the examples, but instead of single entry we use Where to select multiple entries and load their references?

Answer 1

Entry method give you the control over an entity attached to the current context, so before use it the entity must be attached.

The only way to achieve your target is to cycle on all your retrieved entities and Load referenced data.

using (var context = new EmployeeContext())
        {
            var employee = context.Employees.Where(x=> x.Age > 20);
            foreach( var item in employee)
            {
                context.Entry(item).Reference(x => x.ContactDetails).Load();
                context.Entry(item).Reference(x => x.EmpDepartment).Load();
                context.Entry(item.EmpDepartment).Collection(x => x.DepartmentProjects).Load();
            }
        };

Obviously much depends on how many records you are facing, IMHO, if your Foreign Keys and Indexes are optimized, the Include (then a JOIN database side) is the best choice, because all data are retrieved with a single database call, but indeed in some cases more different single SELECT could be a valid option.