New Features in EF Core 7 - Bulk Operations with ExecuteDelete and ExecuteUpdate

Original link: https://timdeschryver.dev/blog/new-in-entity-framework-7-bulk-operations-with-executedelete-and-executeupdate

Original author: tim_deschryver

Translation: Desert End Wolf (with Google Translate assistance)

Entity Framework 7 includes some popular features that have been requested, one of which is bulk operations. A tweet by Julie Lerman caught my attention, and I had to try it myself.

Tweet link: https://twitter.com/julielerman/status/1557743067691569156

Why?

So, if we can already update and delete entities, why do we need this feature? The key word here is performance. This has been a top topic in new versions of EF, and this time is no exception.

The added methods improve performance in several ways. Instead of first retrieving entities and storing all of them in memory, then performing operations on them, and finally submitting them to SQL, we can now do it in a single operation, which produces one SQL command.

Let's see what it looks like in code.

Setting the scene

Before diving into the examples, let's first set up our SQL database and populate 3 tables:

• Persons
• Addresses (a person has one address)
• Pets (a person can have many pets)

using Microsoft.EntityFrameworkCore;

using (var context = new NewInEFContext())
{
    SetupAndPopulate(context);
}

static void SetupAndPopulate(NewInEFContext context)
{
    context.Database.EnsureDeleted();
    context.Database.EnsureCreated();
    context.Persons.AddRange(Enumerable.Range(1, 1_000).Select(i =>
    {
        return new Person
        {
            FirstName = $"{nameof(Person.FirstName)}-{i}",
            LastName = $"{nameof(Person.LastName)}-{i}",
            Address = new Address
            {
                Street = $"{nameof(Address.Street)}-{i}",
            },
            Pets = Enumerable.Range(1, 3).Select(i2 =>
            {
                return new Pet
                {
                    Breed = $"{nameof(Pet.Breed)}-{i}-{i2}",
                    Name = $"{nameof(Pet.Name)}-{i}-{i2}",
                };
            }).ToList()
        };
    }));

    context.SaveChanges();
}

public class NewInEFContext : DbContext
{
    public DbSet<Person> Persons { get; set; }
    public DbSet<Pet> Pets { get; set; }
    public DbSet<Address> Addresses { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder options)
        => options
            .UseSqlServer("Connectionstring");

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Address>()
           .Property<long>("PersonId");

        modelBuilder.Entity<Pet>()
            .Property<long>("PersonId");
    }
}

public class Person
{
    public long PersonId { get; set; }
    public string FirstName { get; set; } = "";
    public string LastName { get; set; } = "";
    public Address? Address { get; set; }
    public List<Pet> Pets { get; set; } = new List<Pet>();
}

public class Address
{
    public long AddressId { get; set; }
    public string Street { get; set; } = "";
}

public class Pet
{
    public long PetId { get; set; }
    public string Breed { get; set; } = "";
    public string Name { get; set; } = "";
}

ExecuteDelete and ExecuteDeleteAsync

Now that we have that out of the way, let's dive into ExecuteDelete and ExecuteDeleteAsync.

To bulk delete a set of entities, use the Where method to filter the entities to delete (similar to before). Then call the ExecuteDelete method to delete the entity collection.

using (var context = new NewInEFContext())
{
    SetupAndPopulate(context);

    context.Pets
           .Where(p => p.Name.Contains("1"))
           .ExecuteDelete();
}

Let's also look at the SQL statement it generates:

DELETE FROM [p]
FROM [Pets] AS [p]
WHERE [p].[Name] LIKE N'%1%'

As you can see, it simply generates one SQL statement to delete the entities that match the condition. These entities are no longer kept in memory. Nice, simple, efficient!

Cascading deletes

Let's look at another example, where we delete some people who hold references to addresses and pets. By deleting the people, we also delete the addresses and pets because the delete statement cascades to the related tables.

using (var context = new NewInEFContext())
{
    SetupAndPopulate(context);

    context.Persons
           .Where(p => p.PersonId <= 500)
           .ExecuteDelete();
}

Similar to before, this produces the following SQL statement:

DELETE FROM [p]
FROM [Persons] AS [p]
WHERE [p].[PersonId] <= CAST(500 AS bigint)

Number of rows affected

You can also see how many rows were affected by the delete operation. ExecuteDelete returns the number of rows affected.

using (var context = new NewInEFContext())
{
    SetupAndPopulate(context);

    var personsDeleted =
        context.Persons
           .Where(p => p.PersonId <= 100)
           .ExecuteDelete();
}

In the expression above, the personsDeleted variable equals 100.

ExecuteUpdate and ExecuteUpdateAsync

Now that we've seen how to delete entities, let's explore how to update them. Like ExecuteDelete, we first have to filter the entities we want to update, and then call ExecuteUpdate.

To update entities, we need to use the new SetProperty method. The first parameter of SetProperty selects the property to update via a lambda, and the second parameter also uses a lambda to select the new value for that property.

For example, let's set the last name of people to "Updated".

using (var context = new NewInEFContext())
{
    SetupAndPopulate(context);

    context.Persons
           .Where(p => p.PersonId <= 1_000)
           .ExecuteUpdate(p => p.SetProperty(x => x.LastName, x => "Updated"));
}

This generates the corresponding SQL statement:

UPDATE [p]
    SET [p].[LastName] = N'Updated'
FROM [Persons] AS [p]
WHERE [p].[PersonId] <= CAST(1000 AS bigint)

We can also access the entity's values and use them to create new values.

using (var context = new NewInEFContext())
{
    SetupAndPopulate(context);

    context.Persons
           .Where(p => p.PersonId <= 1_000)
           .ExecuteUpdate(p => p.SetProperty(x => x.LastName, x => "Updated" + x.LastName));
}

This produces the following SQL statement:

UPDATE [p]
    SET [p].[LastName] = N'Updated' + [p].[LastName]
FROM [Persons] AS [p]
WHERE [p].[PersonId] <= CAST(1000 AS bigint)

Updating multiple values at once

We can even update multiple properties at once by calling SetProperty multiple times.

using (var context = new NewInEFContext())
{
    SetupAndPopulate(context);

    context.Persons
           .Where(p => p.PersonId <= 1_000)
           .ExecuteUpdate(p =>
                p.SetProperty(x => x.LastName, x => "Updated" + x.LastName)
                 .SetProperty(x => x.FirstName, x => "Updated" + x.FirstName));
}

Again, the corresponding SQL statement:

UPDATE [p]
    SET [p].[FirstName] = N'Updated' + [p].[FirstName],
    [p].[LastName] = N'Updated' + [p].[LastName]
FROM [Persons] AS [p]
WHERE [p].[PersonId] <= CAST(1000 AS bigint)

Number of rows affected

Like ExecuteDelete, ExecuteUpdate also returns the number of rows affected.

using (var context = new NewInEFContext())
{
    SetupAndPopulate(context);

    var personsUpdated =
        context.Persons
           .Where(p => p.PersonId <= 1_000)
           .ExecuteUpdate(p => p.SetProperty(x => x.LastName, x => "Updated"));
}

Note that updating nested entities is not supported.

More updates in Entity Framework 7

For a complete list of new features, see the EF 7 plan.