Entity Framework 6 Where in Bulk Update/Delete in one transaction

Question

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();

            }

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?

Answer 1

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

There's a nuget package available, too.

An example would be:

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
}

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.