Partilhar via

LINQ to SQL : Caching the query execution

  • Artigo

LINQ to SQL executes or generated SQL statement only when you run a foreach statement or you perform to ToList() method to it. If you need to display the output multiple times without executing the real database query, you can store them in memory. This can be done only when you are sure that the data is static.

 

Let’s suppose,

 

static void Main(string[] args)

{

    //This takes the connection string from file Settings.settings

    //which gets generated while creating Linq to Sql (.dml) file

    NorthwindDBDataContext db = new NorthwindDBDataContext();

    db.Log = Console.Out;

   

    //Get the Customers from database

    var query = from c in db.Customers

                where c.City == "London"

                select c;

    //This point the query gets executed

    foreach (var c in query)

        Console.WriteLine(c.CompanyName);

    //This point the query AGAIN gets executed

    foreach (var c in query)

        Console.WriteLine(c.CompanyName);

}

 

 

The output will look like

 

SELECT [t0].[CustomerID], [t0].[CompanyName], [t0].[ContactName], [t0].[ContactT

itle], [t0].[Address], [t0].[City], [t0].[Region], [t0].[PostalCode], [t0].[Coun

try], [t0].[Phone], [t0].[Fax]

FROM [dbo].[Customers] AS [t0]

WHERE [t0].[City] = @p0

-- @p0: Input NVarChar (Size = 6; Prec = 0; Scale = 0) NOT NULL [London]

SqlProvider\AttributedMetaModel

Around the Horn

Consolidated Holdings

Eastern Connection

North/South

Seven Seas Imports

SELECT [t0].[CustomerID], [t0].[CompanyName], [t0].[ContactName], [t0].[ContactT

itle], [t0].[Address], [t0].[City], [t0].[Region], [t0].[PostalCode], [t0].[Coun

try], [t0].[Phone], [t0].[Fax]

FROM [dbo].[Customers] AS [t0]

WHERE [t0].[City] = @p0

-- @p0: Input NVarChar (Size = 6; Prec = 0; Scale = 0) NOT NULL [London]

SqlProvider\AttributedMetaModel

Around the Horn

Consolidated Holdings

Eastern Connection

North/South

Seven Seas Imports

 

Now when I am sure that my data is not changing there is no point again going back to the database and execute the data for another operation. Rather what I can do is that I can cache the output and store them in some object.

 

Now if I execute the code like,

 

static void Main(string[] args)

{

    //This takes the connection string from file Settings.settings

    //which gets generated while creating Linq to Sql (.dml) file

    NorthwindDBDataContext db = new NorthwindDBDataContext();

    db.Log = Console.Out;

   

    //Get the Customers from database

    var query = from c in db.Customers

                where c.City == "London"

                select c;

    var listCusts = query.ToList();

    //This point the query does not get executed

    foreach (var c in listCusts)

        Console.WriteLine(c.CompanyName);

    Console.WriteLine("+++++");

    //This point the query ALSO does not get executed

    foreach (var c in listCusts)

        Console.WriteLine(c.CompanyName);

}

 

 

Now the output will look like,

 

SELECT [t0].[CustomerID], [t0].[CompanyName], [t0].[ContactName], [t0].[ContactT

itle], [t0].[Address], [t0].[City], [t0].[Region], [t0].[PostalCode], [t0].[Coun

try], [t0].[Phone], [t0].[Fax]

FROM [dbo].[Customers] AS [t0]

WHERE [t0].[City] = @p0

-- @p0: Input NVarChar (Size = 6; Prec = 0; Scale = 0) NOT NULL [London]

SqlProvider\AttributedMetaModel

Around the Horn

Consolidated Holdings

Eastern Connection

North/South

Seven Seas Imports

+++++

Around the Horn

Consolidated Holdings

Eastern Connection

North/South

Seven Seas Imports

 

Isn’t it better???

 

Namoskar!!!

Comments

  • Anonymous
    July 16, 2007
    LINQ to SQL executes or generated SQL statement only when you run a foreach statement or you perform

  • Anonymous
    July 17, 2007
    Materials from "Using Behaviors to Flex Your WCF Muscles" posted [Via: tom.fuller ] MSDN Nuggets and...

  • Anonymous
    July 18, 2007
    actually, it isn't the query, it's a array list.

  • Anonymous
    July 19, 2007
    LINQ to SQL converts the object to SQL based query. Regards, Wriju

  • Anonymous
    July 30, 2007
    Is there a way to use SQL Notification Services to be notified when data retrieve with LINQ to SQL has been updated in the database?

  • Anonymous
    August 02, 2007
    Mark, You can use DataContext's GetCommand() method to associate SqlDependency object. Wriju

  • Anonymous
    February 06, 2008
    how would you use linq-to-sql in combination with SqlCacheDependency?? thanks in advance! hnks

  • Anonymous
    February 08, 2008
    There are no LINQ to SQL APIs that expose SqlDependency. A workaround is to use DataContext.GetCommand() to get the SqlCommand and use it for SqlDependency. As a cautionary note, some of the LINQ to SQL generated queries may be too complex for use with notification. This blog post has some starter code about SqlDependency  http://dunnry.com/blog/UsingSQLDependencyObjectsWithLINQ.aspx. You can just add the AddCommandDependency() using the GetCommand() result.

  • Anonymous
    March 16, 2009
    isn't you just writing the list twise? that doesn't prove your point.