Written by Tim_Deschryver
The Wolf at the End of the Desert
Entity Framework 7 包括一些已被要求的流行功能，其中之一是批量操作。Julie Lerman 的一条推文引起了我的注意，我不得不亲自尝试一下。
推文地址：https://twitter.com/julielerman/status/1557743067691569156
Why?
So, if we can already update and delete entities, why do we need this functionality? The key word here is performance. This is a theme that has always topped the list in new EF releases, and this time is no exception.
The added methods improve performance in a number of ways. Instead of retrieving entities first and storing all entities in memory before we can perform operations on them and finally commit them to SQL. We can do this now with just one operation, which generates an SQL command.
Let's see what it looks like in the code.
set the scene
Before we delve into the example, let's first configure our SQL database and populate 3 tables:
- Persons: People
- Addresses: Address (one person has one address)
- Pets: Pets (one 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
既然我们已经解决了这个问题，让我们深入研究ExecuteDelete 和 ExecuteDeleteAsync。
要批量删除一组实体，请使用Where方法过滤掉要删除的实体（与之前类似）。然后，调用ExecuteDelete方法删除实体集合。
using (var context = new NewInEFContext())
{
SetupAndPopulate(context);
context.Pets
.Where(p => p.Name.Contains("1"))
.ExecuteDelete();
}
Let's also take a look at the SQL statements it generates:
DELETE FROM [p]
FROM [Pets] AS [p]
WHERE [p].[Name] LIKE N'%1%'
As you can see, it just generates an SQL statement to delete eligible entities. These entities are no longer kept in memory. Not bad, simple and efficient!
cascade delete
Let's look at another example, let's remove some people who hold addresses and pet citations. By deleting people, we also delete addresses and pets because delete statements cascade to external 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)
the number of rows affected
还可以查看删除操作影响了多少行，ExecuteDelete返回受影响的行数。
using (var context = new NewInEFContext())
{
SetupAndPopulate(context);
var personsDeleted =
context.Persons
.Where(p => p.PersonId <= 100)
.ExecuteDelete();
}
在上面的表达式中，personsDeleted变量等于 100。
ExecuteUpdate and ExecuteUpdateAsync
现在我们已经了解了如何删除实体，让我们探索如何更新它们。就像ExecuteDelete，我们首先必须过滤我们想要更新的实体，然后调用ExecuteUpdate.
要更新实体，我们需要使用新SetProperty方法。SetProperty的第一个参数是通过 lambda 选择需要更新的属性，第二个参数也使用 lambda 选择该属性的新值，。
For example, let's set the person's last name 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 will generate 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 value of the entity and use it 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));
}
Produce the following SQL statement:
UPDATE [p]
SET [p].[LastName] = N'Updated' + [p].[LastName]
FROM [Persons] AS [p]
WHERE [p].[PersonId] <= CAST(1000 AS bigint)
Update multiple values at once
我们甚至可以通过多次调用SetProperty来一次更新多个属性。
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));
}
Once 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)
the number of rows affected
就像ExecuteDelete,ExecuteUpdate也返回受影响的行数。
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"));
}
Please note that updating nested entities is not supported.
More updates in Entity Framework 7
有关新功能的完整列表，请参阅EF 7 计划。