How can I set the value of a column for every row in a table using LINQ?

Question

Lets say I have a File table with the column, "File_ID".

How can I write a quick LINQ query/statement to set every "File_ID" in the File table to "1"?

I'm using LINQPad in Statement mode, but that doesn't matter really.

Answer 1

LINQ is for querying, not updating. The LINQ to SQL (and other data sources) models use a traditional object model of Add, Remove, getters and setters, and Update() to commit the changeset to the data backing.

What you are describing is simply iterating over all the record objects in your table collection, setting a property, and calling Update(). It would be much faster and more efficient to do this in SQL with a single UPDATE command.

myDataContext.ExecuteQuery("UPDATE [File] SET File_ID = {0}", 1);

Answer 2

Kinda cheating:

dataContext.ExecuteQuery("update File set File_ID = 1");

Answer 3

This is not what Linq2SQL is best at, but you could try something like this:

using(var ctx = new MyDataContext())
{
    foreach(var f in ctx.Files)
    {
        f.File_ID = 1;
    }
    ctx.SubmitChanges();
}