There may be scenario where we need to execute a particular query many times and repeatedly. LINQ allows us to make this task very easy by enabling us to create a query and make it complied always. We call this type of query as complied query.

 

Benefit of Compiled Query

1. Query does need to compile each time so execution of query is fast.

2. Query is compiled once and can be used any number of time.

3. Query does need to be recompiled even if the parameter of query is being changed.

 

Steps to create Complied Query

1. Create a static class

2. Add namespace System.Data.Linq.

3. Use CompliedQuery class to create complied LINQ query.

 

Let us say we want to create a compiled query to retrieve the entire Person from School database.

 

1. Create Static class

 

clip_image001

 

2. Define static query in the class

 

clip_image003

We can pass as many parameters in Func


1. First parameter is always name of the DataContext class created by LINQ to SQL. In our example name of DataContext class is DataClass1DataContext.


2. Last parameter is the result parameter. This says the return type of the result of the query. This is always a generic IQueryable. In our example it is IQueryable<Person>


3. In between first and last parameter we can have parameter to apply against the query.

 

clip_image005

 

4. Keyword to create complied query is

 

clip_image006

 

Let us write a complied query to fetch all the Persons from DataContext

 

clip_image008

 

And we will call this compiled query as below; MyCompliedQueries is name of the static class.

 

clip_image010

 

Program.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Linq ; 
 
namespace ConsoleApplication5
  {
    class Program
        {
            static void Main(string[] args)
                {
                    DataClasses1DataContext context = new DataClasses1DataContext();
                    var result = MyCompliedQueries.CompliedQueryForPesron(context);
                    foreach (var r in result)
                    {
                        Console.WriteLine(r.FirstName + r.LastName);
                    }
                    Console.ReadKey(true);
 
                }
        }
 
    static class MyCompliedQueries
        {
               public static Func<DataClasses1DataContext ,IQueryable<Person>>
               CompliedQueryForPesron = CompiledQuery.Compile(
                                          (DataClasses1DataContext context)=>
                                              from c in context.Persons select c );
 
 
 
 
        }
}
 

Output

clip_image012

 

Now if we want to pass some parameter in compiled query

 

clip_image014

 

And we will call this query as below,

 

clip_image016

 

Program.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Linq ; 
 
namespace ConsoleApplication5
  {
    class Program
        {
            static void Main(string[] args)
                {
                    DataClasses1DataContext context = new DataClasses1DataContext();
 
                    var result1 = MyCompliedQueries.CompiledQueryWithwhere(context, 9);
                    foreach (var r in result1)
                    {
                        Console.WriteLine(r.FirstName + r.LastName);
                    }
 
                    Console.ReadKey(true);
                }
        }
 
    static class MyCompliedQueries
        {
 
               public static Func<DataClasses1DataContext , int , IQueryable<Person>>
                 CompiledQueryWithwhere = CompiledQuery.Compile(
                                           (DataClasses1DataContext context,
                                               int PersonId) =>
                                               from c in context.Persons
                                               where c.PersonID == PersonId select c
                                              );
 
 
        }
}
 

Output

clip_image018

Advertisements

2 thoughts on “Complied Queries in LINQ

  1. Hi, that was a nice & simple article. I need to know if we can pass along a query itself when calling to compile. For e.g.

    DataClasses1DataContext context = new DataClasses1DataContext();

    var rv = from c in context.sometable select c;

    var result1 = MyCompliedQueries.CompiledQueryWithwhere(context, rv);

    is something like this possible? if yes then how cause I’ve tried passing IQueryable as parameter type to the compile, but that didn’t work.

    Thanks,
    Nabil

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s