Entity Framework (EF) Core is a popular Object-Relational Mapping (ORM) framework for .NET developers that simplifies working with relational databases. EF Core provides several methods for updating data in a database, including Add, Update, and Remove methods. These methods enable developers to modify data in a database using C# or VB.NET code, without having to write any SQL statements.
🛬 EF Core Updates Land
The Update method is used to modify existing entities in the database. Developers can modify the properties of the entity objects and call the Update method on the corresponding DbSet to mark the entity as modified. When calling SaveChanges, EF Core will generate the necessary SQL statements to update the corresponding records in the database.
In addition to these basic methods, EF Core also provides more advanced methods for updating data in the database. These include methods for batch updates, which can be used to update multiple records in a single operation, and methods for executing raw SQL statements, which can be useful for performing complex updates that cannot be expressed using EF Core's standard methods.
Overall, EF Core's update methods provide developers with a flexible and efficient way to modify data in relational databases using object-oriented programming techniques. These methods can help developers write cleaner and more maintainable code, while reducing the amount of time and effort required to work with databases.
⛽ Prepare our plane fuel
We need a massive database for our test to query in and respond to greet results, so we use The Microsoft Worldwide Database Sample for SQL Server.
The Microsoft Worldwide Database Sample for SQL Server is a sample database designed to demonstrate the capabilities of Microsoft SQL Server. It contains a realistic set of data for a fictional multinational organization, including data related to sales, customers, orders, and products.
The database is designed to showcase features such as data warehousing, online transaction processing (OLTP), and data analytics. It can be used by developers, database administrators, and other IT professionals to learn about SQL Server and practice building and optimizing queries.
The Worldwide Database Sample is available for download from the Microsoft website, and it includes documentation and sample queries to help users get started. It is a useful resource for anyone looking to gain practical experience with SQL Server and to explore the capabilities of the platform.
To restore this database, follow these steps:
Download the sample database backup file from the Microsoft Download Center. The backup file is named "WWI.bak" and is approximately 432 MB in size.
Open SQL Server Management Studio (SSMS) and connect to your SQL Server instance.
Right-click on the "Databases" node in the Object Explorer pane and select "Restore Database".
In the "Restore Database" dialog box, select "Device" as the source and click on the "..." button next to it.
In the "Select backup devices" dialog box, click on the "Add" button and browse to the location where you saved the WWI.bak file.
Select the backup file and click "OK" to close the "Select backup devices" dialog box.
Back in the "Restore Database" dialog box, make sure that the "To database" field is set to "WWI".
In the "Options" pane, select "Overwrite the existing database (WITH REPLACE)" and click "OK" to start the restore process.
Once the restore process is complete, you should see the "WWI" database in the Object Explorer pane.
You can now use the Worldwide Importers sample database to explore and learn SQL Server features and functionality. You can use this link for more information about how to restore this database.
✈️ Airplane engine placement
Now we need a tool to bring our database to our Console project; we found EF Core Power Tools very useful.
EF Core Power Tools is a Visual Studio extension that provides additional functionality for Entity Framework Core developers. This tool helps developers to work with EF Core by providing features such as generating DbContext and entity classes from an existing database, visualizing the database schema, and generating migrations. You can use this link to download it and use it.
To use EF Core Power Tools to import Database First models to your console project, follow these steps:
- Install the EF Core Power Tools Visual Studio extension from the Visual Studio Marketplace.
- Open your console project in Visual Studio.
- Right-click on the project in the Solution Explorer and select "EF Core Power Tools" -> "Reverse Engineer".
- In the "Reverse Engineer" dialog box, select "Database" as the data source.
- Enter the connection string for your database in the "Connection string" field. You can click on the "Edit" button to configure additional connection settings.
- Select the tables, views, and stored procedures that you want to include in the model. You can also specify a schema or exclude specific objects.
- Choose the namespace and directory where you want to generate the model files.
- Select the language for the generated code (C# or VB.NET).
- Click "OK" to start the reverse engineering process.
- EF Core Power Tools will generate the model files based on the selected database objects. You can now use the generated DbContext and entity classes in your console project to work with the database.
Note: EF Core Power Tools generates code that uses EF Core to access the database. If you want to use a different ORM or data access library, you will need to modify the generated code accordingly.
We are fully prepared to embark on our journey to explore the most recent developments regarding EF Core.
🚒 UpdateRange Station
UpdateRange is a method provided by Entity Framework Core (EF Core) that allows developers to update multiple entities in a single database transaction. This method is part of the DbSet class and can be used to update a collection of entities in a single call to the database.
In EF Core, updating multiple entities one at a time can be an inefficient process as it can result in a large number of database calls. The UpdateRange method helps to optimize this process by allowing developers to update a collection of entities in a single transaction, reducing the number of round trips to the database.
When using UpdateRange, developers simply need to pass in a collection of entities to update. EF Core will then generate a single SQL statement that updates all the entities in the collection in a single transaction. This can significantly improve performance, especially when updating large collections of entities.(we explain more about it later)
It's worth noting that UpdateRange only updates existing entities in the database. If an entity in the collection is not already in the database, it will be ignored. Additionally, UpdateRange only updates the properties that have been modified in each entity. If a property has not been modified, it will not be updated.
Overall, UpdateRange is a useful method provided by EF Core that can help improve performance when updating multiple entities. It allows developers to update a collection of entities in a single database transaction, reducing the number of round trips to the database and improving overall efficiency.
public async Task EfCoreUpdateRangeAsync()
{
var context = new WideWorldImportersContext();
var people = await context.People.ToListAsync();
people.ForEach(i => i.LogonName = "Test");
context.People.UpdateRange(people);
await context.SaveChangesAsync();
}
🏎️ ExecuteUpdate Bluck Operation Station
ExecuteUpdateAsync is a method provided by Entity Framework Core (EF Core) that allows developers to execute bulk database updates using custom SQL statements. This method can be used to perform updates on large amounts of data in a single database transaction, improving the performance of update operations.
Unlike other update methods provided by EF Core, ExecuteUpdateAsync is a bulk operation that updates multiple records at once. This can be useful when updating a large number of records in a single operation, as it can significantly reduce the number of database round-trips required.
The method takes a raw SQL string as input, which can be parameterized to allow for more flexible and secure update operations. It also supports asynchronous execution, meaning it can be executed on a separate thread without blocking the main thread.
It's worth noting that ExecuteUpdateAsync bypasses EF Core's change tracking and validation mechanisms, so it should be used with caution. Developers should ensure that the custom SQL statement is safe and meets the necessary security requirements.
Overall, ExecuteUpdateAsync is a powerful method provided by EF Core for bulk updates. It can improve performance for large-scale update operations, but it should be used with care to avoid potential issues with change tracking and validation.
public async Task EfCoreExecuteUpdateAsync()
{
var context = new WideWorldImportersContext();
await context.People.ExecuteUpdateAsync(p => p.SetProperty(pr => pr.LogonName, "Test"));
await context.SaveChangesAsync();
}
🏎️ BatchUpdateAsync Station
BatchUpdateAsync is a method provided by the third-party library, Z.EntityFramework.Extensions, which extends the functionality of Entity Framework Core (EF Core). This method allows developers to execute bulk updates against a database using a high-performance batching technique.
BatchUpdateAsync can be used to update multiple records in a single database transaction, reducing the number of round-trips to the database and improving the performance of update operations. The method supports a wide range of database providers and can be used with SQL Server, MySQL, Oracle, PostgreSQL, and others.
BatchUpdateAsync is designed to work with EF Core's IQueryable interface, allowing developers to perform updates using LINQ expressions. The method takes an IQueryable as input and a lambda expression that defines the update operation to be performed.
The method supports a variety of options for controlling the update behavior, including filtering, projection, and transaction management. It also supports asynchronous execution, making it suitable for high-performance applications that require rapid updates.
It's worth noting that BatchUpdateAsync is a third-party library that requires a license for commercial use. However, the library provides a free trial version that can be used for non-commercial purposes.
public async Task EfCoreBatchUpdateAsync()
{
var context = new WideWorldImportersContext();
await context.Set<Person>()
.BatchUpdateAsync(new Person()
{
LogonName = "Test"
}, new List<string>()
{
nameof(Person.LogonName)
});
}
🚗 BulkUpdate Station
BulkUpdateAsync is a method provided by the third-party library, EFCore.BulkExtensions, which extends the functionality of Entity Framework Core (EF Core). This method allows developers to perform bulk updates against a database using a high-performance approach.
BulkUpdateAsync can be used to update multiple records in a single database transaction, reducing the number of round-trips to the database and improving the performance of update operations. The method supports a wide range of database providers and can be used with SQL Server, MySQL, Oracle, PostgreSQL, and others.
BulkUpdateAsync is designed to work with EF Core's IQueryable interface, allowing developers to perform updates using LINQ expressions. The method takes an IQueryable as input and a lambda expression that defines the update operation to be performed.
The method supports a variety of options for controlling the update behavior, including filtering, projection, and transaction management. It also supports asynchronous execution, making it suitable for high-performance applications that require rapid updates.
It's worth noting that EFCore.BulkExtensions is a third-party library that requires a license for commercial use. However, the library provides a free trial version that can be used for non-commercial purposes.
public async Task EfCoreBulkUpdateAsync()
{
var context = new WideWorldImportersContext();
var people = context.People.ToList();
people.ForEach(p => p.LogonName = "Test");
await context.BulkUpdateAsync(people);
}
🧪 Test
Now it's time to drop out our update package to see who is update the 1,111 data in People table. Are you ready?
Due to their impressive efficiency, it is essential to recognize the significant impact of using ExecuteUpdateAsync and BatchUpdateAsync. For instance, if 1,000,000 records need to be updated, inefficient methods could cause system crashes and overload the server with additional data, making managing such a vast task difficult.
🔚 Conclusion
UpdateRange, ExecuteUpdateAsync, BatchUpdateAsync, and BulkUpdateAsync are all methods provided by Entity Framework Core (EF Core) and its third-party extensions for performing updates in bulk.
UpdateRange is a built-in EF Core method that allows developers to update multiple records in a single database transaction. It's designed to work with tracked entities and can be used to update multiple entities at once. It does not bypass EF Core's change tracking and validation mechanisms.
ExecuteUpdateAsync is a method provided by EF Core that allows developers to execute custom SQL statements to perform bulk updates. It bypasses EF Core's change tracking and validation mechanisms, and is therefore not suitable for complex update operations that require these mechanisms.
BatchUpdateAsync is a method provided by the third-party library, Z.EntityFramework.Extensions, which allows developers to perform bulk updates using a high-performance batching technique. It supports a wide range of database providers and offers a variety of options for controlling the update behavior.
BulkUpdateAsync is a method provided by the third-party library, EFCore.BulkExtensions, which also allows developers to perform bulk updates using a high-performance approach. It offers similar features to BatchUpdateAsync but supports different database providers.
UpdateRange can be slow compared to other bulk update methods because it relies on EF Core's change tracking mechanism to detect changes to the entities being updated. This means that UpdateRange must query the database to retrieve the entities, update them in memory, and then generate and execute individual SQL update statements for each entity. This can be slow for large datasets because of the overhead involved in retrieving and updating each entity individually.
On the other hand, bulk update methods like BatchUpdateAsync and BulkUpdateAsync operate directly on the database without relying on change tracking. These methods use SQL update statements to update multiple records in a single transaction, which can be significantly faster than generating individual update statements for each record. Additionally, these methods often offer features like batch processing and the ability to specify the number of records updated in each batch, which can further optimize performance.
In summary, UpdateRange can be slower than other bulk update methods because of its reliance on change tracking, which can result in a significant overhead when updating large datasets. If performance is a concern, using a bulk update method like BatchUpdateAsync or BulkUpdateAsync may be a better option. However, it's important to weigh the benefits and drawbacks of each method and choose the one that best meets your specific requirements.
Overall, the main difference between these methods lies in their approach to performing bulk updates and the level of control they offer. UpdateRange is a built-in EF Core method that provides a basic solution for updating multiple records. ExecuteUpdateAsync offers the ability to perform custom SQL updates, but at the cost of bypassing change tracking and validation mechanisms. BatchUpdateAsync and BulkUpdateAsync both provide high-performance solutions for bulk updates, but they are provided by third-party libraries and require a license for commercial use. They offer a range of options for controlling update behavior, but also require additional setup and configuration.
🙏 Appreciation
I want to express my gratitude towards Ziba for being such a fantastic collaborator on this article. It was our first time writing together, and I couldn't have asked for a better partner. Ziba's talent and hard work are truly inspirational, and I feel honored to work alongside such an incredible person. Ziba graciously agreed to my proposal to write this article together, and I am so grateful for that.
Our team is incredibly fortunate to have Ziba on the structure. Their contributions and expertise have led to significant improvements and outstanding results. Ziba's willingness to share their knowledge and help others is admirable and greatly appreciated. If you'd like to connect with this exceptional individual, I highly recommend checking out her LinkedIn profile.
Top comments (0)