LINQ to SQL Updates
Does anyone have any idea how to run the following statement with LINQ?
UPDATE FileEntity SET DateDeleted = GETDATE() WHERE ID IN (1,2,3)
I have come to love and hate LINQ, but so far very few things have worked out well. The obvious solution that I want to avoid is to list all the file entities and set them manually.
foreach (var file in db.FileEntities.Where(x => ids.Contains(x.ID)))
{
file.DateDeleted = DateTime.Now;
}
db.SubmitChanges();
The problem with the above code, apart from significant overhead, is that each object has a data field that can be quite large, so for a large update, a lot of data traverses the database connection for no particular reason. (LINQ's solution is to delay loading the Data property, but that wouldn't be necessary if there was some way to just update the field with LINQ to SQL).
I think some query expression providers that will result in the above T-SQL ...
a source to share
LINQ cannot perform store updates - this is a language integrated query, not an update. Most (maybe even all) OR mappers will generate a select statement to fetch data, modify it in memory, and perform an update using a separate update statement. Smart OR cards only collect the primary key until additional data is needed, but then they will usually get all the rest, because it would be expensive to only cost one attribute at a time.
If you really care about this optimization, use a stored procedure or a handwritten SQL statement. If you need compact 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 people prefer this solution.
a source to share
LINQ to SQL is an ORM like any other and as such it is not designed to handle bulk updates / inserts / deletes. 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 procedures, which ultimately limits flexibility and adaptability.
When it comes to bulk updates, they're best left with what makes them best ... the database server. L2S and EF provide the ability to map stored procedures to your model, which allows you to store stored processes in some entity. Since you are using L2S, just write a proc that takes a set of IDs as input and executes the SQL statement at the beginning of your question. Drag this saved proc onto your L2S model and call it.
This is the best solution for an issue that is a massive update. Similar to reporting, object graphs and object-relational mapping are not the best solution for large processes.
a source to share