LINQ to SQL : Caching the query execution

  • Article

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!!!