Sql – LINQ to SQL updates

linqlinq-to-sqlsql

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…

Best Answer

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.

Related Topic