LINQ Include Uncovered. Learn all the tricks

LINQ is an abbreviation of Language Integrated Query and pronounced as “Link”. It allows querying any kind of data source by using similar types of coding styles like SQL Database, in-memory array or objects, XML or other data sources that supports LINQ. It provides the compile time error checking along with the power of Intellisense and refactoring.

What is LINQ Include

LINQ Include() allows to include the related entities or objects from a list. The list can be loaded from a database or any static list. There are several ways to read data from the data source and load results into the navigation properties of an entity. Eager Loading provides the functionality of the LINQ Include() by retrieving the related data whenever the entity is read. This will produce a large result set at first, but no more queries will be send to the database to fetch the related data. On the contrary, multiple queries will be called to retrieve the related data without the Include() function. The other two options are lazy loading and explicit loading that do not retrieve the related data in a single join query.

If your requirement is to retrieve all the related data for every entity, eager loading is the best option to use in the given scenario because a single call is made to the database instead of sending multiple commands to fetch the data by using Include() function. The LINQ Include() function might result in a complex join which will be interpreted by the SQL. 

How to use Lambda expressions with LINQ Include

In earlier versions of Entity Framework, you need to pass the navigation property name as an argument in the quotation marks to the LINQ Include() function. The compiler was not able to identify the compilation errors until the application is in running mode and executes the programming command. The second drawback is that you cannot use Intellisense and refactoring feature.

The EF 4.1 brings the provision of eager loading using lambda with LINQ Include() function by using the EntityFramework.dll and System.Data.Entity namespaces. In the following code, you can combine the related entities in one query, and it is the same as merging multiple queries in a single query.

using (var context = new DepartmentContext())
{
    // Load all departments and related students.
    var departmentList = context.Department
                        .Include(b => b.Students)
                        .ToList();

    // Load all Departments and related Students by using a plain string to specify the relationship
    var departmentsListB = context.Departments
                        .Include("Students")
                        .ToList();
}

LINQ Include vs Join

LINQ Include() function retains the original structure and objects of the entities whereas the Join function gives the flat representation of the data. They both will acquire the same results, but the representation is different for each function. The following code illustrates the programming difference between the Include and Where functions:

using (var context = new DepartmentContext())
{
    // Load all departments and related students by using Include function
    var departmentList = context.Department
                        .Include(b => b.Students)
                        .ToList();

    // Load all departments and related students by using Join function
    var departmentList =  from d in context.Department
                                    join s in context.Students on d.DepartmentId equals s.StudentId 
                                    select new {d, s}
}

LINQ Include vs Where

LINQ Include() is an alternative to the Where() function in the LINQ. The following code illustrates the programming difference between the Include and Where functions:

using (var context = new DepartmentContext())
{
     // Load one department “Computer Science” and its related students by using Include function
    var department = context.Departments
                               .Where(b => b.Name == "Computer Science")
                                .Include(b => b.Students)
                               .FirstOrDefault();

    // Load one department “Computer Science” and its related Students by using Where function
    var departmentB =  from d in context.Department
                                    where d.Name == "Computer Science")
                                    select d;
}

using (var context = new DepartmentContext())
{
     // Load all departments and its related students by using Include function and return a list with same UniversityName “ABC University”
    var departmentList = context.Departments
                                      .Include(b => b.Students) 
                                      .Select(x => new { Department = x, UniversityName = "ABC University" }) 
                                      .ToList();

     // Load all departments and its related students by using Select function and return a list with same UniversityName “ABC University”
    var departmentListB = from d in context.Department
                                         where d.Name == "Computer Science")
                                         select d;
}

LINQ Include vs SelectMany

Include and SelectMany provide retrieving data at multiple levels. The following code illustrates the difference between the Include and SelectMany function:

using (var context = new DepartmentContext())
{
     // Load all departments and courses by using Include function 
    var departmentList = context.Departments
                                      .Include(b => b.Students.Select(it => it.Courses))
                                      .ToList();

     // Load all departments and courses by using SelectMany function 
        var departmentListB = context.Departments
                                              .Where(d =>d.Name == "Computer Science")
                                               .SelectMany(d => d.Courses);

}