LINQ to SQL updates

Question

Does any one have some idea on how to run the following statement with LINQ?

UPDATE FileEntity SET DateDeleted = GETDATE() WHERE ID IN (1,2,3)

I've come to both love and hate LINQ, but so far there's been little that hasn't worked out well. The obvious solution which I want to avoid is to enumerate all file entities and set them manually.

foreach (var file in db.FileEntities.Where(x => ids.Contains(x.ID)))
{
    file.DateDeleted = DateTime.Now;
}
db.SubmitChanges();

There problem with the above code, except for the sizable overhead is that each entity has a Data field which can be rather large, so for a large update a lot of data runs cross the database connection for no particular reason. (The LINQ solution is to delay load the Data property, but this wouldn't be necessary if there was some way to just update the field with LINQ to SQL).

I'm thinking some query expression provider thing that would result in the above T-SQL...

Answer 1

LINQ cannot perform in store updates - it is language integrated query, not update. Most (maybe even all) OR mappers will generate a select statement to fetch the data, modify it in memory, and perform the update using a separate update statement. Smart OR mappers will only fetch the primary key until additional data is required, but then they will usually fetch the whole rest because it would be much to expensive to fetch only a single attribute at once.

If you really care about this optimization, use a stored procedure or hand-written SQL statement. If you want compacter code, you can use the following.

db.FileEntities.
    Where(x => ids.Contains(x.ID)).
    Select(x => x.DateDeleted = DateTime.Now; return x; );

db.SubmitChanges();

I don't like this because I find it less readable, but some prefer such an solution.

Answer 2

LINQ to SQL is an ORM, like any other, and as such, it was not designed to handle bulk updates/inserts/deleted. The general idea with L2S, EF, NHibernate, LLBLGen and the rest is to handle the mapping of relational data to your object graphs for you, eliminating the need to manage a large library of stored procs which, ultimately, limit your flexability and adaptability.

When it comes to bulk updates, those are best left to the thing that does them best...the database server. L2S and EF both provide the ability to map stored procedures to your model, which allows your stored procs to be somewhat entity oriented. Since your using L2S, just write a proc that takes the set of identities as input, and executes the SQL statement at the beginning of your question. Drag that stored proc onto your L2S model, and call it.

Its the best solution for the problem at hand, which is a bulk update. Like with reporting, object graphs and object-relational mapping are not the best solution for bulk processes.