实体框架 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 Profiler 中，有三个脚本

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

是否可以在一个查询中获取带有 Where In 子句的脚本?

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

推荐答案

很遗憾，Entity Framework 不支持此功能.但是，您可以使用 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 语句，并且该库还包括批量 DELETE.

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.

最后，不用担心 new 表达式.此处未引用的任何属性都将保留其原始值.

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

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