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
{
//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 == “
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 [
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 [
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
{
//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 == “
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 [
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!!!
LINQ to SQL executes or generated SQL statement only when you run a foreach statement or you perform
Materials from "Using Behaviors to Flex Your WCF Muscles" posted [Via: tom.fuller ] MSDN Nuggets and…
actually, it isn’t the query, it’s a array list.
LINQ to SQL converts the object to SQL based query.
Regards,
Wriju
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?
Mark,
You can use DataContext’s GetCommand() method to associate SqlDependency object.
Wriju
how would you use linq-to-sql in combination with SqlCacheDependency??
thanks in advance!
hnks
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.
isn’t you just writing the list twise? that doesn’t prove your point.