LINQ and Deferred Execution

DotNetKicks.com — Mon, 10 Dec 2007 09:51:32 GMT

You've been kicked (a good thing) - Trackback from DotNetKicks.com

re: LINQ and Deferred Execution

Jeff LeBert — Thu, 13 Dec 2007 03:36:20 GMT

I think your example is a little misleading. In the last code example you have:

var query = (...).Count();

The Count method returns an integer not a query. Therefore naming your variable "query" is the confusing part. It would have been better to do the following:

int count = (...).Count();

In this way it is obvious the count is only done once.

LINQ and Deferred Execution Video

Charlie Calvert's Community Blog — Fri, 14 Dec 2007 10:16:04 GMT

Here is a video version of the Deferred Execution post I published on December 9. You can watch the video

LINQ and Deferred Execution Video

Noticias externas — Fri, 14 Dec 2007 10:54:12 GMT

Here is a video version of the Deferred Execution post I published on December 9. You can watch the video

re: LINQ and Deferred Execution

Steve — Sun, 16 Dec 2007 15:48:38 GMT

So, if I first do a .ToList() before enumerating, then it will make one db call?

if I loop through as in your above sample of a foreach - does it make a call for each one - or just on the first loop?

Please clarify

re: LINQ and Deferred Execution

manit — Fri, 21 Dec 2007 00:39:54 GMT

great article, i just ran into a problem with this today. Is there a way to avoid Deferred Execution?

re: LINQ and Deferred Execution

Matt Brown — Fri, 21 Dec 2007 01:23:42 GMT

Very well written article with clear examples. Thanks!

re: LINQ and Deferred Execution

nightwatch77 — Sun, 23 Dec 2007 21:24:42 GMT

Hey, in third example (SimpleQuery03) you are using variable reference in query expression (the 'city' variable).

Please tell me how LINQ updates the filter condition in query if you modify the value of 'city' AFTER creating the query? In my opinion this shouldn't work, as strings are immutable.

Saying it shorter: the query expression has no way of seeing if 'city' variable has changed, as the variable is not in the scope of query.

Or are there some 'closures' in C#? How does it work?

re: LINQ and Deferred Execution

onevextchuck — Thu, 27 Dec 2007 07:33:35 GMT

Nightwatch: It's just that age old issue of base types vs reference types. Strings may be immutable, but they are stored on the heap which means that they are reference types. If the LINQ query is not creating a local copy of the value, it will use whatever is held in that reference when it gets around to executing the query.

re: LINQ and Deferred Execution

Dennis van der Stelt — Thu, 27 Dec 2007 23:59:36 GMT

@Jef : I think the example is excellent, because this is something people will actually write, without knowing what they're actually doing!

LINQ to SQL vs. DBA's

Dennis van der Stelt — Fri, 28 Dec 2007 01:57:48 GMT

Why is it that in Oracle world, it's much more custom to have a database administrator (DBA) on your

LINQ and Deferred Execution Video

{ The Smoking Code } — Fri, 28 Dec 2007 03:38:14 GMT

LINQ and Deferred Execution Video

Mighell's Mobile Blog — Fri, 28 Dec 2007 03:56:00 GMT

Charlie ha postato un interessante video che spiega in un modo semplice ed oserei dire disarmante, il

re: LINQ and Deferred Execution

Zloth — Fri, 28 Dec 2007 19:25:56 GMT

It's the variable changing that's more interesting to me. If you do something like (switching to VB here):

Dim sTemp as string = FormatPhoneForDB("9139130000")

Dim Q1 = from x in db.Table where x.Phone=sTemp

sTemp = FormatPhoneForDB("3193190000")

Dim Q2 = from y in db.Table where y.Phone=sTemp

DataGridView1.DataSource = Q1

DataGridView2.DataSource = Q2

Both data grids are going to show the data for the second phone number! On the other hand, if you don't use an sTemp variable and just put the call to the FormatPhoneForDB function right in the query, I do get the two different datasets. Yow. Nasty. I think we're going to be seeing a lot of subtle bugs on this one.

re: LINQ and Deferred Execution

Ashwani — Wed, 02 Jan 2008 12:45:05 GMT

1. How to extract data from the tables using mannual connectivity to database with LINQ.

2. How to use Stored procedures with LINQ.

3. How to Select multiple values through Select statement with LINQ.

re: LINQ and Deferred Execution

Thanda — Wed, 02 Jan 2008 21:57:41 GMT

Is Microsoft recommending developers to embed SQL code in the form of LINQ into C# code? From a maintenance standpoint, isn't it better to keep your SQL code in stored procedures?

PS: Ignore if you think this is off-topic.

re: LINQ and Deferred Execution

Viper — Wed, 02 Jan 2008 23:36:46 GMT

So, you're saying that the expression tree stores the address of the string variable (a pointer to a pointer to a string on the heap) as opposed to the value of it (a pointer to a string on the heap). If that's true, then what if you write a function that returns a query but the string used in it is a local variable (and, thus, on the stack)? Sound like a runtime error (or at least an evil bug) would result when the query is exectued (and the stack frame no longer exists).

I can't imgaine why the expression tree generator would copy references to the variables used to compose it as opposed to their current values.

Maybe I've misread this post but something doesn't seem to add up here.

re: LINQ and Deferred Execution

Wayne — Thu, 03 Jan 2008 01:32:06 GMT

I have yet to involve myself with LINQ yet, but I was just thinking how I've been saying that query work should be left for stored procs. This is yet another avenue that fly-by-nighters and hacks will use to muddle up the code that I will have to take over and support.

So much for leaving SQL in the database, now it's going back into the code, where I believe it shouldn't be (for larger projects mind you...)

Great article tho..had to bitch a little.

re: LINQ and Deferred Execution

Matt Neerincx (MSFT) — Fri, 04 Jan 2008 04:26:15 GMT

I just wonder to myself how I am going to debug this crazy stuff. I can just see a storm cloud coming when we people start doing some serious LINQ stuff and it does not work as expected.

Howabout some debugging examples, show us what is happening for real under the covers?

re: LINQ and Deferred Execution

David Preeves — Fri, 04 Jan 2008 19:40:03 GMT

Since it is straightfoward to create a function to query data from a database via a stored proc or query what is the main advantage to using linq? I can see that it would be advantageous for lists, queues, etc. but for database queries I'm at a loss to why one would use this technology. Isn't querying a stored procedure more efficient than building queries on the fly on the client? Also, isn't it kind of messy to throw around ad hoc queries in code all over the place inside the program? What if a table name of field changes?

re: LINQ and Deferred Execution

Alan Cummings — Mon, 07 Jan 2008 17:30:03 GMT

Good article that highlights deferred execution, however, it does introduce a level of confusion by the use of implicit typing with var (as pointed out by Jeff LeBert). The use of explicit typing (ie: int, IQueryable<Customer>, and so on) would have removed the ambiguity - but it does serve to highlight the dangers of overusing implicit typing.

re: LINQ and Deferred Execution

Jim Huddle — Tue, 08 Jan 2008 22:03:21 GMT

You have shown us an extremely likely bug.

Is there any productive work that could be done AFTER assigning the (SQL-like) LINQ query to the "var", but before the query hits the back end? If the answer is 'no', then this is nothing more than a bug, waiting to be fixed.

Thanks for the heads-up.

re: LINQ and Deferred Execution

Zbyszek — Fri, 11 Jan 2008 04:48:09 GMT

@David - I think that main thing missed from all explanations is that LINQ changes completely the way you see access to the database. Just think about your objects as not being in the database, but placed in the memory (kind of like if you have moved whoe database to the collections of objects in the memory at the beginning of the program). DataContext keeps data in the cache and you are supposed to trust DLINQ engine to translate everything to the queries when neccessary. So in fact all your code works on objects in the memory.

As for your question re stored procedures, I am still using dlinq for them, as I have strongly typed result and much more elegant code.

If you change types in your tables or add columns or modify types in the result of the stored procedure you would have to recreate dbml file.

If you have doubts - do not worry, you are not the only one :) But I think this is a step in a new direction, something which may change the way we think about coding and database access.

re: LINQ and Deferred Execution

some one — Sun, 13 Jan 2008 03:15:26 GMT

I'm now able to convert an application written in a 4GL language from 1995 to .NET 3.5 with LINQ. Those languages are heavy wih in line SQL statements which are easier to convert to LINQ than some other means with just .NET 2.0.

Just because it is there does not mean you have to use it. One could also put in place guidelines on how to use so as to not use in a way you don't see fit. And if you are sick of picking up other peoples code then don't. Otherwise I have some old 4GL code if you would like to convert over to LINQ.

Thank you for the article this helps explain to others what is going on.

re: LINQ and Deferred Execution

Sir Taniel — Mon, 21 Jan 2008 22:29:58 GMT

As soon as I saw LINQ to SQL I thought WOW isn't this great. Most of my development time is creating stored procs and class objects for each of my tables. I was able to create a one-to-many relationship and create a grid that allowed for insert/updates/deletes without writing any code. Cool right? I think not...

After seeing more and more of LINQ I am doubtful of ever using this in a production environment. Hence the articles nature in general. Also someone else mentioned having to pickup someone elsess buggy LINQ queries. I quiver to thought of the phone call i will get to fix an application that was shipped off-shores to be built to save cost and LINQ was used.

Also I wondered when using LINQ to SQL if I modify a database does the code need re-compiled and re-deployed to each client in a non-web non-remoted environment?

re: LINQ and Deferred Execution

Holger Flick — Thu, 24 Jan 2008 13:29:23 GMT

This post shows how important it is to type variables properly if one can instead of using "var". If you typed properly in your last "query", you'd be more aware of the fact that query actually is an integer that is not going to change by writing it to the console multiple times.

re: LINQ and Deferred Execution

Niranjan Limaye — Wed, 06 Feb 2008 00:46:54 GMT

I am not a techno geek, I am just another programmer in the big ocean of information technology. Also do not have much exposure to LINQ. I still remember my repartee with another Java Architect (colleague), he said "Working with Microsoft is like getting married after every 2 years.". wait wait wait, I don't want to start Java vs. .NET comparison here. I apologies, If you find my comments little bold or adventures.

This looks like another "programming adventure" to do things in some other exicting..!!! way.

I don't see any business benefits of using this when DB native stored procedures / stored objects can achieve the same result in safer, faster, quicker way. Microsoft is contradicting it's own promotion on benefits of Stored procedures, stored functions or DB native objects.

They can really come up with true DB independent data access layer and it would be lot better for middle-tier programmers and multi-database applications or systems.

1. Allow middle-tier programmers to write consistent SQL. This includes stored procedure calls or SQL query calls or any other DB execution call, may XML queries etc.

2. Microsoft Query Engine should analyze the SQL and convert the same into native database semantics on which said query will be executed, it can be Oracle, SQL Server, or any open source database or object oriented database. To achieve this, Microsoft can follow their own data provider pattern. Where Oracle will provide its query engine, SQL Server will provide its own etc.

3. Pass the 'parsed' SQL to native database execute the same on the underlying database and return the result back to the middle-tier in 'typed dataset' format. Embed your data-adapters. Middle - tier or business process layer will make single API call or business call to retrieve the data.

This way .NET middle tier programmers does not need to understand underlying database and able to write DB independent SQL in their Data Access Layers.

This would be true DB independent data access layer and benefit programmers in many way.

DOWNLOAD LINKS: Public Sector Developer Conference (LA / San Diego)

Gold Coast — Sat, 09 Feb 2008 05:07:49 GMT

Thanks to everyone who attended! As promised, I have gathered all the decks and code. Here are the links:

DOWNLOAD LINKS: Public Sector Developer Conference (LA / San Diego)

Public Sector Developer Weblog — Sat, 09 Feb 2008 05:08:58 GMT

Thanks to everyone who attended!  As promised, I have gathered all the decks and code.  Here

DOWNLOAD LINKS: Public Sector Developer Conference (LA / San Diego)

Noticias externas — Sat, 09 Feb 2008 05:24:59 GMT

Thanks to everyone who attended!  As promised, I have gathered all the decks and code.  Here

re: LINQ and Deferred Execution

Dale — Tue, 12 Feb 2008 00:35:26 GMT

LINQ is just a bad idea all around. This leaves Microsoft as the database code optimizer for every database rather than letting developers and trained, experienced DBAs optimizing query code. Just as generic code generators develop bad code, generic database access develops poor performing database access.

This makes about as much sense as merging the two worst Internet search engines in the world with the expectation that between them they can beat the best search engine.

I think bad design added to bad design yields logarithmically worse design rather than adding up to good design.

re: LINQ and Deferred Execution

Dev — Thu, 14 Feb 2008 16:39:04 GMT

Not at all a bad idea. It is nothing to do with the database at all. At the client side if you want to extract subset of the data from the extracted DataSet LINQ comes into rescue.

For eg:

foreach (Row in DataSet.Table.Rows)

...............

............. YourListBox.Items.Add........

I think LINQ syntax is better ...returns a list

It is not that every query you need to use deffered execution. It is just a value addition you can also use deffered execution if you want. Otherwise just use LINQ to query your DataSet returned.

Hope this answers most of the comments about LINQ I guess.

re: LINQ and Deferred Execution

Dev — Thu, 14 Feb 2008 17:07:18 GMT

Does not execute the query on the database it executes the query on the DataSet.

For eg 2:

XElement xmlCustomers = new XElement("customers",

from c in customers

where c.Country == Countries.India

select new XElement("customer",

new XElement("name",c.Name),

new XElement("city",c.City),

new XElement("country",c.COuntry)));

to create from a db.customers or use

XElement xmlCustomers = XElement.Load(@"..\..\customer.xml");

from an xml file or from an xml string

now I can query

var cities= xmlCustomers.DescendantsAndSelf("city"); // city is a node

How difficult it was to iterate through XmlNodeList extract each element even though we used XPath to qry the subset.

Now..with LINQ

foreach(var city in cities) {

Console.WriteLine(city);

re: LINQ and Deferred Execution

Diane Wilson — Fri, 15 Feb 2008 19:48:07 GMT

Having spent a few months immersed in LINQ, I feel compelled to comment on a few things here.

First, stored procedures are not "safe" and "easy" except from the DBA's perspective. From an application perspective (using ADO.NET), you have no design-time or compile-time support at all. Sproc names are strings, parameter names are strings, column names in the result set are strings, and type information on both parameters and results are set and used without any real compile-time verification.

Further, if the sproc changes, it's like any other schema change, and you have no real tools to track down where all the calls to the sproc are, so they can be fixed. You have only regression testing (and hope it catches them all), or your help desk after code goes to production.

LINQ fixes all that. It has complete type-checking and compile-time support for all sproc names, parameter names, and result names. Even if the only thing you use LINQ for is calling your stored procedures, it's a huge improvement over ADO.NET.

Second, the issue of schema changes. Find SQLMetal. (Hint: it's in the Microsoft SDK that comes with VS2008.) Learn to use it to generate your schema mapping for LINQ. Put it in your daily build. It will pick up schema changes, as well as changes to sprocs, views, and functions. Use SQLMetal output as your DataContext for all LINQ queries. If the schema changes affect ANY of your LINQ queries, the compiler will find them and tell you about them. Isn't that better than waiting for things to blow up down the road?

As far as deferred execution goes, yes, it's a change, but you just have to learn how LINQ works before it will work well for you. In that sense, it's no different from learning any other powerful and complex new technology. The advantages that LINQ offers in terms of flexibility, productivity, and reliability will follow only after you understand LINQ's approach to generating SQL, and that includes deferred execution.

There are plus sides to deferred execution. You can define a basic query, then extend it:

IQueryable<Category> myQuery = from cat in NW.Categories select cat;

Category oneCat = myQuery.Where(c => c.ID == 1).SingleOrDefault();

One poster above mentioned that it's better to use explicit typing on LINQ queries, and that is generally true. "var" becomes totally safe only when you know what you're getting, but it also provides a learning-level entry point, and it does work just fine for local variable access, once you know the rules.

As for the question of referring to local variables, the real "best practice" here is precompiled queries; they provide both a safe way to use local data in a LINQ query, and they provide greatly improved performance for queries that are going to be re-used with any frequency.

Here's an example of a compiled query:

private static Func<Northwind, string, IQueryable<NW.Data.Customer>>

CustomerByIDQuery =

CompiledQuery.Compile((Northwind ndc, string custID)

=> from c in ndc.Customers where c.CustomerID == custID select c);

Calling a compiled query is easy, and passes local data as a parameter to the query:

NW.Data.Customer _customer = CustomerByIDQuery(m_DC, _custID).SingleOrDefault();

Anyway, enough time on my soap-box, but I just wanted to throw in my experience with LINQ. I think it's one of the finest pieces of software engineering I've seen in a while, but you really do have to dig in to understand it and use it well.

BTW, there's an excellent book out now by Joseph C. Rattz: pro LINQ with Visual Studio C# 2008. It's an APress book, and it's a great book for both learning and reference.

给热爱学习的同学们推荐一些顶级的c# Blogs链接

无常 — Tue, 19 Feb 2008 19:50:20 GMT

在TI行业，中文的资料永远都比英文的慢几个月，而且原创性的文章也少得可怜，有空时，不妨去这些英文技术BLOG溜达溜达，也许会有意外的惊喜。

给热爱学习的同学们推荐一些顶级的c# Blogs链接

cnblogs.com — Tue, 19 Feb 2008 20:45:03 GMT

在IT行业，中文的资料永远都比英文的慢几个月，而且原创性的也少得可怜，有空时，不妨去这些英文技术BLOG溜达溜达，也许会有意外的惊喜。好的C#博客应该符合这些条件：有用的新闻、信息、技巧和代码例子

Getting Started With LINQ To SQL

Hilton Giesenow's Jumbled Mind — Wed, 20 Feb 2008 19:03:46 GMT

Considering I've put a few posts up about LINQ To SQL, I realised I've never shared some of the

Getting Started With LINQ To SQL(转)

王德水 — Wed, 02 Apr 2008 09:48:09 GMT

原文地址:http://dotnet.org.za/hiltong/archive/2008/02/20/getting-started-with-linq-to-sql.aspxConsideri...

LINQ To SQL : Dynamically Adding a Shared OrderBy Clause

Hilton Giesenow's Jumbled Mind — Mon, 12 May 2008 18:13:56 GMT

One of the features of LINQ queries is Deferred Execution, which Charlie Calvert discusses here with

Dynamic Construction of a LINQ Expression

Jonas Stawski's Blog — Wed, 21 May 2008 18:05:06 GMT

One of the great things about LINQ is it's deferred execution. Read about deferred execution here and

Dynamic Construction of a LINQ Expression

Jonas Stawski — Fri, 20 Jun 2008 20:21:52 GMT

Dynamic Construction of a LINQ Expression

re: LINQ and Deferred Execution

sdoc — Wed, 25 Jun 2008 18:59:21 GMT

awesome article!! after spending half of my day not realizing my problem was related to deferred execution....i think this article is def LINQ 101

给热爱学习的同学们推荐一些顶级的c# Blogs链接

布衣 — Wed, 03 Sep 2008 12:38:56 GMT

在IT行业，中文的资料永远都比英文的慢几个月，而且原创性的也少得可怜，有空时，不妨去这些英文技术BLOG溜达溜达，也许会有意外的惊喜。好的C#博客应该符合这些条件：有用的新闻、信息、技巧和代码例子定期更新原创内容，不是广告文章盗用别人的文章良好的组织，包含分类和tags 健康的讨论和读者评论有一定的个人见解，最好还有点幽默感，但又不是自己在嗐吹牛

Deferred Execution trong LINQ

Bao Duy's Blog — Wed, 03 Jun 2009 20:03:07 GMT

  Trong LINQ, có một cơ chế giúp tối ưu hóa việc truy xuất dữ liệu đó là cơ chế Deferred Execution

re: LINQ and Deferred Execution

Nisha — Tue, 07 Jul 2009 13:46:01 GMT

great article..thanks for this article..