How does linq-to-sql generate sql for collection pseudoqueries?

Question

My understanding is that the LinqToSql pseudolanguage describes a set using a syntax very similar to SQL and this will allow you to efficiently update a property on a collection of objects:

from b in BugsCollection where b.status = 'closed' set b.status = 'open'

This would update the underlying database using just one SQL statement.

Normally an ORM needs to retieve all of the rows as separate objects, update attributes on each of them and save them individually to the database (at least that's my understanding).

So, how does linq-to-sql avoid having to do this when other orms are not able to avoid it?

Answer 1

The syntax shown in your question is incorrect. LINQ is not intended to have side-effects; it is a query language. The proper way to accomplish what you're looking for is

var x = from b in dataContext.BugsCollection where b.status == "closed";
foreach (var y in x)
y.status = "open";

dataContext.SubmitChanges();

This would generate the single SQL statement that you're talking about. The reason it is able to accomplish this is because of deferred execution - the L2S engine doesn't actually talk to the database until it has to - in this case, because SubmitChanges() was called. L2S then sends the generated SQL statement to the database for execution.

Answer 2

Because LINQ to SQL uses Expression Trees to convert your Query Syntax to actual SQL...it then executes the SQL against the database (rather than pulling all of the data, executing against the in-memory data, and then writing the changes back to the database).

For example, the following Query Syntax:

var records = from r in Records
              where r.Property == value
              select r

Gets translated first to Lamda Syntax:

Records.Where(r => r.Property == value).Select();

And finally to SQL (via Expression Trees):

SELECT Property, Property2, Property3 FROM Record WHERE Property = @value

...granted, the example doesn't update anything...but the process would be the same for an update query as opposed to a simple select.