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