Efficiently Delete/Update Entities in EF Core 7 by Multiple Properties

Discover an efficient method to `update` or `delete` entities in EF Core 7 using multiple property conditions without resorting to stored procedures.
---
This video is based on the question https://stackoverflow.com/q/78178949/ asked by the user 'Art' ( https://stackoverflow.com/u/14933823/ ) and on the answer https://stackoverflow.com/a/78179778/ provided by the user 'Charlieface' ( https://stackoverflow.com/u/14868997/ ) at 'Stack Overflow' website. Thanks to these great users and Stackexchange community for their contributions.

Visit these links for original content and any more details, such as alternate solutions, comments, revision history etc. For example, the original title of the Question was: Efficient way to delete/update entities in EF Core 7 by a list of conditions

Also, Content (except music) licensed under CC BY-SA https://meta.stackexchange.com/help/licensing
The original Question post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/by-sa/4.0/ ) license, and the original Answer post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/by-sa/4.0/ ) license.

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
Efficient Ways to Delete/Update Entities in EF Core 7 by a List of Conditions

In the realm of Entity Framework Core (EF Core), performing bulk updates or deletions efficiently is crucial, especially when dealing with complex queries involving multiple properties. This guide addresses a common challenge faced by developers: how to manage updates or deletions based on a list of identifiers that consist of multiple fields, rather than a simple list of IDs.

The Problem

Imagine you need to update multiple rows in a database table according to certain criteria defined by several properties. Traditional methods work well for single ID conditions, but when faced with a scenario requiring multiple properties—like date, code, and owner—the native functionality seems to fall short.

Previously, you might have executed updates similar to this:

[[See Video to Reveal this Text or Code Snippet]]

Yet, when attempting to switch to using multiple properties via a list of identifiers, you may find yourself at a standstill, questioning the efficiency of your approach. You might have even contemplated utilizing a stored procedure as a fallback.

The Solution: Using Table-Valued Parameters

Fortunately, there’s a neat solution in EF Core that can save you from the hassle of resorting to stored procedures. This involves using Table-Valued Parameters (TVPs). Here’s a comprehensive breakdown of how to approach it:

1. Create a Table Type in SQL Server

First, define a table type for your identifiers in your SQL Server database:

[[See Video to Reveal this Text or Code Snippet]]

2. Configure Your EF Core Model

As prior versions of EF Core did not allow direct querying with multi-column types, you'll need to modify your model configuration to handle this new entity type:

[[See Video to Reveal this Text or Code Snippet]]

3. Prepare the DataTable to Hold Your Identifiers

When preparing to perform the update, you’ll need to set up a DataTable with the necessary structure:

[[See Video to Reveal this Text or Code Snippet]]

4. Using the Table-Valued Parameter for Updating

Next, you'll create the TVP in your EF Core context and execute the update:

[[See Video to Reveal this Text or Code Snippet]]

5. Simplified Use in EF Core 8 and Beyond

With EF Core 8 and later, the process is even simpler, as direct execution of raw queries with table-valued parameters does not require defining an entity first:

[[See Video to Reveal this Text or Code Snippet]]

Conclusion

By leveraging Table-Valued Parameters in EF Core, you can efficiently perform updates or deletions based on a list of identifiers with multiple properties. This technique not only increases performance but also maintains clean and manageable code, sparing you from the hassle of writing complex stored procedures.

Embrace these strategies in your next EF Core project, and enjoy a more streamlined approach to managing your data!

Видео Efficiently Delete/Update Entities in EF Core 7 by Multiple Properties канала vlogize