Reader Level:
ARTICLE

Compiled Queries in LINQ

Posted by dj Articles | LINQ January 07, 2011
There might be a 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 compiled always. We call this type of query a compiled query.
  • 0
  • 0
  • 13219
Download Files:
 


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 compiled always. We call this type of query a compiled query.

Benefits of Compiled Queries

  1. Query does need to compiled each time so execution of the query is fast.
  2. Query is compiled once and can be used any number of times.
  3. Query does need to be recompiled even if the parameter of the query is being changed.

Steps to create a Compiled Query
  1. Create a static class
  2. Add namespace System.Data.Linq.
  3. Use CompiledQuery 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

    1.gif
     
  2. Define static query in the class

    2.gif

We can pass as many parameters in Func
  1. First parameter is always the name of the DataContext class created by LINQ to SQL. In our example the name of the 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 parameters to apply against the query.

    3.gif
     
  4. Keyword to create complied query is

    4.gif

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

5.gif

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

6.gif

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

7.gif

Now if we want to pass some parameter in compiled query

8.gif

And we will call this query as below,

9.gif

Program.cs

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

10.gif

  

COMMENT USING

Trending up