Observe me on Twitter at @tim_deschryver | Subscribe to the Newsletter | Initially printed on timdeschryver.dev.
Read the TLDR version on timdeschryver.dev
Model 7 of Entity Framework contains some well-liked options which have been requested for, considered one of which is Bulk Operations.
This characteristic got here to my consideration from a tweet by Julie Lerman, and I needed to attempt it out for myself.
Why
So why is that this characteristic wanted if we already can replace and delete entities?
The important thing phrase right here is efficiency. It is a theme that has been on the highest of the record in relation to new EF variations, and this time it’s no totally different.
The added strategies enhance the efficiency in a number of methods.
As an alternative of first retrieving the entities and having all of the entities in reminiscence earlier than we will carry out an motion on them, and lastly committing them to SQL. We now can do that with only a single operation, which ends up in one SQL command.
Let’s check out how this seems like in code.
Setting the stage
Earlier than we dive into the examples, let’s first arrange our SQL database and populate 3 tables:
- one to carry individuals
- one other one is for addresses (an individual has an tackle)
- and the final one to retailer pets (an individual has a group of pets)
utilizing Microsoft.EntityFrameworkCore;
utilizing (var context = new NewInEFContext())
{
SetupAndPopulate(context);
}
static void SetupAndPopulate(NewInEFContext context)
{
context.Database.EnsureDeleted();
context.Database.EnsureCreated();
context.Individuals.AddRange(Enumerable.Vary(1, 1_000).Choose(i =>
{
return new Individual
{
FirstName = $"{nameof(Individual.FirstName)}-{i}",
LastName = $"{nameof(Individual.LastName)}-{i}",
Handle = new Handle
{
Avenue = $"{nameof(Handle.Avenue)}-{i}",
},
Pets = Enumerable.Vary(1, 3).Choose(i2 =>
{
return new Pet
{
Breed = $"{nameof(Pet.Breed)}-{i}-{i2}",
Title = $"{nameof(Pet.Title)}-{i}-{i2}",
};
}).ToList()
};
}));
context.SaveChanges();
}
public class NewInEFContext : DbContext
{
public DbSet<Individual> Individuals { get; set; }
public DbSet<Pet> Pets { get; set; }
public DbSet<Handle> Addresses { get; set; }
protected override void OnConfiguring(DbContextOptionsBuilder choices)
=> choices
.UseSqlServer("Connectionstring");
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.Entity<Handle>()
.Property<lengthy>("PersonId");
modelBuilder.Entity<Pet>()
.Property<lengthy>("PersonId");
}
}
public class Individual
{
public lengthy PersonId { get; set; }
public string FirstName { get; set; } = "";
public string LastName { get; set; } = "";
public Handle? Handle { get; set; }
public Listing<Pet> Pets { get; set; } = new Listing<Pet>();
}
public class Handle
{
public lengthy AddressId { get; set; }
public string Avenue { get; set; } = "";
}
public class Pet
{
public lengthy PetId { get; set; }
public string Breed { get; set; } = "";
public string Title { get; set; } = "";
}
ExecuteDelete and ExecuteDeleteAsync
Now that we obtained that out of our means, let’s dive into ExecuteDelete and ExecuteDeleteAsync.
To delete a set of entities in bulk, filter out the entities that you just need to delete by utilizing the The place methodology (that is just like earlier than).
Then, invoke the ExecuteDelete methodology on the gathering of entities to be deleted.
utilizing (var context = new NewInEFContext())
{
SetupAndPopulate(context);
context.Pets
.The place(p => p.Title.Comprises("1"))
.ExecuteDelete();
}
Let’s additionally check out the SQL assertion that this generates:
DELETE FROM [p]
FROM [Pets] AS [p]
WHERE [p].[Name] LIKE N'%1%'
As you may see, it merely generates one SQL assertion to delete the entities that match the situation.
The entities additionally aren’t stored in reminiscence anymore.
Good, easy, and environment friendly!
Cascade delete
Let’s check out one other instance, and let’s take away some individuals that maintain references to addresses and pets.
By deleting the individual, we additionally delete the tackle and pets as a result of the delete assertion cascades to the international tables.
utilizing (var context = new NewInEFContext())
{
SetupAndPopulate(context);
context.Individuals
.The place(p => p.PersonId <= 500)
.ExecuteDelete();
}
Just like earlier than, this ends in the next SQL assertion:
DELETE FROM [p]
FROM [Persons] AS [p]
WHERE [p].[PersonId] <= CAST(500 AS bigint)
Variety of rows affected
It is also potential to see what number of rows had been affected by the delete operation, ExecuteDelete returns the variety of rows affected.
utilizing (var context = new NewInEFContext())
{
SetupAndPopulate(context);
var personsDeleted =
context.Individuals
.The place(p => p.PersonId <= 100)
.ExecuteDelete();
}
Within the expression above, the personsDeleted variable is the same as 100.
ExecuteUpdate and ExecuteUpdateAsync
Now that we have seen find out how to delete entities, let’s discover find out how to replace them.
Identical to ExecuteDelete, we first need to filter the entities that we need to replace, after which invoke ExecuteUpdate.
To replace entities we have to use the brand new SetProperty methodology.
The primary argument of SetProperty selects the property that needs to be up to date through a lambda, and the second argument is the brand new worth of that property additionally by utilizing a lambda.
For instance, let’s set the final title of the individuals to “Up to date”.
utilizing (var context = new NewInEFContext())
{
SetupAndPopulate(context);
context.Individuals
.The place(p => p.PersonId <= 1_000)
.ExecuteUpdate(p => p.SetProperty(x => x.LastName, x => "Up to date"));
}
Which ends up in the corresponding SQL assertion:
UPDATE [p]
SET [p].[LastName] = N'Up to date'
FROM [Persons] AS [p]
WHERE [p].[PersonId] <= CAST(1000 AS bigint)
We will additionally entry the values of an entity and use that to create a brand new worth.
utilizing (var context = new NewInEFContext())
{
SetupAndPopulate(context);
context.Individuals
.The place(p => p.PersonId <= 1_000)
.ExecuteUpdate(p => p.SetProperty(x => x.LastName, x => "Up to date" + x.LastName));
}
Ensuing within the following SQL assertion:
UPDATE [p]
SET [p].[LastName] = N'Up to date' + [p].[LastName]
FROM [Persons] AS [p]
WHERE [p].[PersonId] <= CAST(1000 AS bigint)
Updating a number of values directly
We will even replace a number of properties directly by invoking ExecuteUpdate a number of instances.
utilizing (var context = new NewInEFContext())
{
SetupAndPopulate(context);
context.Individuals
.The place(p => p.PersonId <= 1_000)
.ExecuteUpdate(p =>
p.SetProperty(x => x.LastName, x => "Up to date" + x.LastName)
.SetProperty(x => x.FirstName, x => "Up to date" + x.FirstName));
}
And once more, the corresponding SQL assertion:
UPDATE [p]
SET [p].[FirstName] = N'Up to date' + [p].[FirstName],
[p].[LastName] = N'Up to date' + [p].[LastName]
FROM [Persons] AS [p]
WHERE [p].[PersonId] <= CAST(1000 AS bigint)
Variety of rows affected
Identical to ExecuteDelete, ExecuteUpdate additionally returns the variety of rows affected.
utilizing (var context = new NewInEFContext())
{
SetupAndPopulate(context);
var personsUpdated =
context.Individuals
.The place(p => p.PersonId <= 1_000)
.ExecuteUpdate(p => p.SetProperty(x => x.LastName, x => "Up to date"));
}
Notice that updating nested entities is just not supported.
Extra Updates in Entity Framework 7
For the whole record of latest options, see the EF 7 plan.
Observe me on Twitter at @tim_deschryver | Subscribe to the Newsletter | Initially printed on timdeschryver.dev.
