Bulk Update with LINQ to SQL

Is there a way to do a bul开发者_运维百科k update on a collection with LINQ? Currently if I have a List<myObject> and I want to update column1 to equal TEST for every row in the List I would setup a foreach loop and then for each individual object I would set the value and then save it. This works fine but I was just wondering if there was some LINQ method out there where I could do something like myOject.BulkUpdate(columnName, value)?

Your requirement here is entirely possible using Linq expressions and Terry Aney's excellent library on this topic.

Batch Updates and Deletes with LINQ to SQL

An update in the terms of the example you gave would be as follows:

using BTR.Core.Linq;
...

Context.myObjects.UpdateBatch
(
    Context.myObjects.Where(x => x.columnName != value),
    x => new myObject { columnName = value}
);

Edit (2017-01-20): It's worth nothing this is now available in the form of a NuGet package @ https://www.nuget.org/packages/LinqPost/.

Install-Package LinqPost

Sounds like you're using LINQ To SQL, and you've got the basics laid out already.

LINQ To SQL is about abstracting tables into classes, and doesn't really provide the 'silver bullet' or one-liner you are looking for.

The only way to do that is to achieve your one-liner would be to make a stored proc to take that column name and new value, and implement that logic yourself.

 db.MassUpdateTableColumn("Customer", "Name", "TEST");

 ....

 CREATE PROC MassUpdateTableColumn
    @TableName varchar(100), @ColumnName varchar(100), @NewVal varchar(100)
 AS
    /*your dynamic SQL to update a table column with a new val. */

Otherwise, it's as you describe:

 List<Customer> myCusts = db.Customers.ToList();
 foreach(Customer c in myCusts)
 {
     c.Name = "TEST";
 }     
 db.SubmitChanges();

LINQ to SQL (or EF for that matter), is all about bringing objects into memory, manipulating them, and then updating them with separate database requests for each row.

In cases where you don't need to hydrate the entire object on the client, it is much better to use server side operations (stored procs, TSQL) instead of LINQ. You can use the LINQ providers to issue TSQL against the database. For example, with LINQ to SQL you can use context.ExecuteCommand("Update table set field=value where condition"), just watch out for SQL Injection.

EF Core 7.0 introduces Bulk Update and Bulk Delete.

For example, consider the following LINQ query terminated with a call to ExecuteUpdateAsync:

var priorToDateTime = new DateTime(priorToYear, 1, 1);

await context.Tags
    .Where(t => t.Posts.All(e => e.PublishedOn < priorToDateTime))
    .ExecuteUpdateAsync(s => s.SetProperty(t => t.Text, t => t.Text + " (old)"));

This generates SQL to immediately update the “Text” column of all tags for posts published before the given year:

UPDATE [t]
    SET [t].[Text] = [t].[Text] + N' (old)'
FROM [Tags] AS [t]
WHERE NOT EXISTS (
    SELECT 1
    FROM [PostTag] AS [p]
    INNER JOIN [Posts] AS [p0] ON [p].[PostsId] = [p0].[Id]
    WHERE [t].[Id] = [p].[TagsId] AND [p0].[PublishedOn] < @__priorToDateTime_1)