Sql – How does linq-to-sql generate sql for collection pseudoqueries

linq-to-sql

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?

Best Answer

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.

Related Topic