Updating a set of records in LINQ - All at once

Question

I have several records that all need to be updated to the same value. If I was just using ADO.NET I could just call a stored procedure that updated them all at one ...

UPDATE myTable
SET myColumn = "XXXXXX"
WHERE filterColumn == 'YYY'

But since I am using Entity Framework I am wondering if their was a similar way to update a set of records at once without having to loop through each of the values and set them individually? Currently I am using..

from s in myTables
where s.filterColumn == 'YYY'
select s;

var results = s.ToList();

foreach (i in results){
  s.myColumn = "XXXXXX"
}

Is there a way to set the values all at once as in SQL?

I am using Entity Framework v6.1

you can execute [raw sql queries](http://msdn.microsoft.com/en-us/data/jj592907.aspx) with EF. — Selman Genç, Sep 10 '14 at 16:26

score 3 · Accepted Answer · answered Sep 10 '14 at 16:36

3

You can still execute sql command when using Entity Framework. Here is how to do it.

dbContext.Database.Connection.Open();
var cmd = dbContext.Database.Connection.CreateCommand();

cmd.CommandText = @"UPDATE myTable
SET myColumn = @myColumn
WHERE filterColumn = @filterColumn";

cmd.Parameters.Add(new SqlParameter("myColumn", "XXXXXX"));
cmd.Parameters.Add(new SqlParameter("filterColumn", "YYY"));
cmd.ExecuteNonQuery();

answered Sep 10 '14 at 16:36

tsuta

357
4
12

The `Database.ExecuteSqlCommand` method is even easier: http://stackoverflow.com/a/5475210/124386 – Richard Deeming Sep 10 '14 at 17:58

Richard Deeming · Answer 2 · 2014-09-10T17:56:04.050

2

The Entity Framework Extended Library includes batch update / delete functionality:
https://github.com/loresoft/EntityFramework.Extended

myTables.Update(
    s => s.filterColumn == "YYY",
    s => s.myColumn = "XXXXXX");

NB: This currently only supports SQL Server and SQL CE, although there is a pull request to add MySQL support.

edited Sep 10 '14 at 17:56

answered Sep 10 '14 at 16:25

Richard Deeming

29,830
10
79
151

Might be worth noting that EFE only really supports SQL Server. – Aron Sep 10 '14 at 16:52
@Aron - And SQL Server Compact :-) – ErikEJ Sep 10 '14 at 17:57

Updating a set of records in LINQ - All at once

2 Answers2