Support us .Net Basics C# SQL ASP.NET Aarvi MVC Slides C# Programs Subscribe Download

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.

LINQ to SQL Tutorial

3 comments:

  1. Venket... All of your tutorial are great and simple to understand... thanks.

    ReplyDelete
  2. You can check this line of code
    where s.ID == 1
    to be
    where s.ID == studentId

    ReplyDelete
  3. Hi Venkat, I'm a regular learner of your blog and videos. I stuck while executing DML commands on ExecuteCommand(). The point is after the status/messages like updated or inserted, it doesn't affects the database. Even i have tried with Context.SubmitChanges() after execution of command. How to resolve it.

    ReplyDelete

It would be great if you can help share these free resources