Set-based execution; SQL and .NET

Question

In SQL, one should always strive for set-based operations versus iteration-based (i.e. looping). In .NET, we frequently loop collections and objects. Are there any commands in .NET that allow set-based processing or is everything iteration-based? (I'm reminded of how DataAdapter.Fill calls DataReader which iterates through each record in the result set). I'm not terribly familiar with LINQ, but my guess would be that its implementation merely masks the iterations happening behind the scenes.

---

UPDATE:

To clarify: I'm not claiming to be any sort of genius here and I'm not second guessing any of the brilliant people who make my life programming better. I am simply asking if there are commands that perform set-based operations, like SQL does when you UPDATE, versus foreach(var item in obj) { ... } which is clearly iterating through the object. SQL developers are chastised at every turn if ever they use a loop, yet in .NET, we use them all the time. Being a develper who works heavily in both SQL and .NET, I'm asking if there are any alternatives in .NET that avoid looping altogether.

Answer 1

LINQ-to-SQL queries are genuinely set-based operations. The only time iteration occurs is when you invoke GetEnumerator, either directly or via a foreach statement.

This works because LINQ-to-SQL starts with IQueryable<T> (where T is some class that acts as a surrogate for a table), and each LINQ operation you apply to an IQueryable<> returns another IQueryable<>. Behind the scenes, LINQ isn't accessing the database at all, but is instead building up a more and more complex LINQ expression. When you finally ask to enumerate the result, LINQ takes the entire expression and converts it into an SQL query that goes to database as a single request.

Answer 2

I'm not terribly familiar with LINQ, but my guess would be that its implementation merely masks the iterations happening behind the scenes.

How do you think SQL does it? It's not that iteration doesn't happen. It's a matter of how you express your intentions in code. Set-based and declarative operations tell the platform what you want, and then leave it up to the platform for figure out how best to do it. It works because the platforms that allow this kind of code are expert systems in their area, and so are much better at it than a human could hope to be. On the other hand, imperative or procedural code tells the platform exactly what to do and how to do it. This leaves less room for machine optimizations, usually requires more code, and is more prone to bugs.