Wriju's BLOG

.NET, Cloud and everything

LINQ to SQL : Caching the query execution

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