LINQ to SQL : Understanding Compiled Query

Understanding Compiled Query connects me to my experience in C# 3.0. Based on my personal understanding I am discussing it.

You may be aware of Func<>. And you know it is a flexible delegate which allows you create reusable functions. Exactly the same way we can create compiled query so that we can prevent the Expression Compilation during runtime.

Let us first understand how Func works.

You create a Func for square and use it anywhere in your application

Func<double, double> sqr = x => x * x;

Console.WriteLine(sqr(10));

This is precompiled and works just like a variable. So the magic is you declaring a reusable function as variable.

Now assume that it is in form of expression. Things works differently

//Wrap it with Expression<>

Expression<Func<double, double>> sqr = x => x * x;

//Expression cannot be executed but can be viewed

Console.WriteLine(sqr);

//Make it executable you need to call Compile()

var mySqr = sqr.Compile();

//Now this becomes executable

Console.WriteLine(mySqr(5));

So every time you need to call this function you need to call Compile() or at least once.

This works exactly same in LINQ to SQL in Compiled Query. Now notice this below Compiled Query.

Func<NWDataContext, string, IQueryable<Customer>> q =

    CompiledQuery.Compile<NWDataContext, string, IQueryable<Customer>>

        ((NWDataContext nw, string sCity) =>

            from o in nw.Customers

            where o.City == sCity

            select o);

It is doing nothing but creating Func for your LINQ to SQL.

 

using (NWDataContext db = new NWDataContext())

{

    foreach (Customer c in q(db, "London"))

    {

        Console.WriteLine(c.CompanyName);

    }

}

It is very powerful and performance effective.

Namoskar!!!

Comments

• Anonymous
  May 05, 2009
  PingBack from http://microsoft-sharepoint.simplynetdev.com/linq-to-sql-understanding-compiled-query/

• Anonymous
  May 05, 2009
  Thank you for submitting this cool story - Trackback from DotNetShoutout

• Anonymous
  May 06, 2009
  Compiled queries are great and very performant you just want to be careful if you have a lot of them, especially in web apps.  Only compile the queries you use the most.

• Anonymous
  December 09, 2009
  Hey that was a nice post for a beginner like me playing with LINQ TO SQL. However I didnt understand one thing, @'Chad Moran said: 'Only compile the queries you use the most. Wots mean by that? Any partial effect unless these are frequently used queries?

• Anonymous
  January 12, 2010
  @Vins, Regarding Compiled Query unless it is frequently use, I don't see any benefit here.

• Anonymous
  April 28, 2013
  hi, these are the tables tblUsers UserID EmpID UserName Password RoleID tblEmployee EmpID EmpName JobTitle Date I don't actually know whether there should be a Role Table because I want the roles to be read from the SQL Server. I am just new, please bear with me. I think this Linq to Sql is great. Thanks!