Part 13 - Compiled queries in Linq to SQL

Suggested Videos
Part 10 - Difference between eager loading and lazy loading
Part 11 - Single table inheritance in linq to sql
Part 12 - Single table inheritance - saving to database



In this video we will discuss compiling Linq queries to improve performance. First let us understand what happens when a LINQ query is issued with an example.



The following Linq query retrieves a single student whose ID = 1.

using (SampleDataContext dbContext = new SampleDataContext())

{

    Student student = (from s in dbContext.Students

                                   where s.ID == 1

                                   select s).Single();

    Console.WriteLine(student.FirstName + " " + student.LastName);

}

When this LINQ query is issued, LINQ has to parse the expression tree and dynamically generate the required T-SQL statements. This happens every time a LINQ query is issued. This overhead can be removed by compiling the linq query.

To compile a LINQ query use CompiledQuery class that is present in System.Data.Linq namespace.

var compiledStudentQuery = CompiledQuery.Compile(

                            (SampleDataContext dataContext, int studentId) =>

                                (from s in dataContext.Students

                                 where s.ID == 1

                                 select s).Single());

using (SampleDataContext dbContext = new SampleDataContext())

{

    Student student = compiledStudentQuery(dbContext, 1);

    Console.WriteLine(student.FirstName + " " + student.LastName);

}

Here is the SQL to create Students table

Create Table Students

(

     ID int primary key identity,

     FirstName nvarchar(50),

     LastName nvarchar(50),

     Gender nvarchar(50)

)

GO

Insert into Students values ('Mark', 'Hastings', 'Male')

Insert into Students values ('Steve', 'Pound', 'Male')

Insert into Students values ('Ben', 'Hoskins', 'Male')

Insert into Students values ('Philip', 'Hastings', 'Male')

Insert into Students values ('Mary', 'Lambeth', 'Female')

GO

After the Students table is created, add a new LINQ to SQL class to the console project. Drag and drop Students table from Server Explorer onto LINQ to SQL class designer file.