LINQ to SQL Tutorial

Part 13 Compiled queries in Linq to SQL

Text version of the video
http://csharp-video-tutorials.blogspot.com/2014/09/part-13-compiled-queries-in-linq-to-sql.html

Slides
http://csharp-video-tutorials.blogspot.com/2014/09/part-13-compiled-queries-in-linq-to-sql_17.html

LINQ to SQL Tutorial – All Text Articles & Slides
http://csharp-video-tutorials.blogspot.com/2014/09/linq-to-sql-tutorial.html

LINQ to SQL Tutorial Playlist

Dot Net, SQL, Angular, JavaScript, jQuery and Bootstrap complete courses
https://www.youtube.com/user/kudvenkat/playlists?view=1&sort=dd

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.