实体框架6在一个交易中，批量更新/删除 [英] Entity Framework 6 Where in Bulk Update/Delete in one transaction

问题描述

在EF6，我想更新/在一个查询中删除大量数据。
我的代码是

In EF6, I want to update/delete bulk data in one query. My code is

 using (var context = _dataContextFactory.GetContext())
            {
                var result1 = from b in context.MyTables
                    where new List<int> {592, 593, 594}.Contains(b.Id)
                    select b;


                foreach (var item in result1 )
                {
                    item.StatusId = 3;
                }

                context.SaveChanges();

            }

但在SQL事件探查器中，有三个脚本

But in Sql Profiler, there are three scripts

exec sp_executesql N'UPDATE [dbo].[MyTable] SET [StatusId] = @0 WHERE ([Id] = @1) ',N'@0 int,@1 int',@0=1,@1=592
exec sp_executesql N'UPDATE [dbo].[MyTable] SET [StatusId] = @0 WHERE ([Id] = @1) ',N'@0 int,@1 int',@0=1,@1=593
exec sp_executesql N'UPDATE [dbo].[MyTable] SET [StatusId] = @0 WHERE ([Id] = @1) ',N'@0 int,@1 int',@0=1,@1=594

是有可能得到与脚本，在从句中一个查询？

is it possible to get script with Where In clause in one query?

推荐答案

不幸的是，这在实体框架不支持开箱即用。但是，您可以在 EntityFramework.Extended 库使用批量更新功能：

Unfortunately, this is not supported in Entity Framework out of the box. However, you can use the batch update functionality in the EntityFramework.Extended library:

的 https://github.com/loresoft/EntityFramework.Extended

有一个一揽子的NuGet 。可也。

There's a nuget package available, too.

一个例子是：

using EntityFramework.Extensions;

...

int[] myIds = { 592, 593, 594 };

using (var context = _dataContextFactory.GetContext())
{
    // Define a filter expression to retrieve matching items
    var filter = context.MyTables.Where(item => myIds.Contains(item.Id));
    // Update the StatusId of matched items
    context.MyTables.Update(filter, i => new Item { StatusId = 3 });

    // NB: no context.SaveChanges() required
}

注：可能有写这个的更有效的方式，但我仍然与库播放。它编译到一个单一的SQL语句，然而，该库还包括批量删除操作。

NB: there may be a more efficient way of writing this, but I'm still playing with the library. It does compile down to a single SQL statement, however, and the library also includes batched DELETEs.

最后，不要担心新的表达。这里没有提及将保留其原始值的属性。

Finally, don't worry about the new expression. Any properties which are not referenced here will retain their original values.

这篇关于实体框架6在一个交易中，批量更新/删除的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！