Shulou(Shulou.com)06/02 Report--

This article will explain in detail how to delete Entity Framework Core associations. The content of the article is of high quality, so the editor will share it with you for reference. I hope you will have a certain understanding of the relevant knowledge after reading this article.

Association deletion is usually a database term used to describe a feature that allows the deletion of the associated row to be automatically triggered when the row is deleted; that is, when the data row of the main table is deleted, the dependent data row in the associated table is automatically deleted, or the foreign key is updated to NULL or default value.

Database association deletion behavior

Let's first take a look at the behaviors supported in SQL Server. When you create a foreign key constraint, you can specify how the associated table will operate on dependent data when rows are deleted by the main table. For example, in the following SQL statement, the [OrderID] field in the [Order Details] table is a foreign key and depends on the primary key [OrderID] in the [Orders] table.

CREATE TABLE [Orders] ([OrderID] int NOT NULL IDENTITY, [Name] nvarchar (max) NULL, [OrderDate] datetime2 NULL, CONSTRAINT [PK_Orders] PRIMARY KEY ([OrderID])) GOCREATE TABLE [Order Details] ([DetailId] int NOT NULL IDENTITY, [OrderID] int NULL, [ProductID] int NOT NULL, CONSTRAINT [PK_Order Details] PRIMARY KEY ([DetailId]), CONSTRAINT [FK_Order Details_Orders_OrderID] FOREIGN KEY ([OrderID]) REFERENCES [Orders] ([OrderID]) ON DELETE SET NULL)

The statement at the end of the foreign key constraint [FK_Order Details_Orders_OrderID] is ON DELETE SET NULL, which means that when the data row of the main table is deleted, the foreign key of the associated table data row is automatically updated to NULL.

The following four behaviors are supported in SQL Server:

1.ON DELETE NO ACTION

By default, when you delete a row of data in the main table, no action is performed on the data in the dependent table, an error is generated, and the DELETE statement is rolled back. For example, the following error occurs:

The DELETE statement conflicts with the REFERENCE constraint "FK_Order Details_Orders_OrderID". The conflict occurs in the database "Northwind_Test", table "dbo.Order Details", column 'OrderID'.

Statement terminated.

2.ON DELETE CASCADE

When you delete a primary table data row, the middle data row of the dependent table is also deleted synchronously.

3.ON DELETE SET NULL

When you delete a row of data in the primary table, update the foreign key of the row in the dependent table to NULL. To satisfy this constraint, the foreign key column of the target table must be nullable.

4.ON DELETE SET DEFAULT

When you delete the data row of the master table, update the foreign key of the data row in the dependent table to the default value. To satisfy this constraint, all foreign key columns of the target table must have a default value definition; if the foreign key can be null and the default value is not explicitly set, NULL is used as the implicit default value for the column.

After a brief introduction to the behavior in the database, let's focus on the behavior of the associated entities in EF Core.

Define entity

We first define two entities Order and OrderDetail to represent the order and order details, respectively; the relationship between Order and OrderDetail is one-to-many, and OrderID represents the foreign key in the OrderDetail entity, depending on the primary key OrderID in the Order entity.

Public class Order {public int OrderID {get; set;} public string Name {get; set;} public DateTime? OrderDate {get; set;} public ICollection OrderDetails {get; set;}} public class OrderDetail {public int DetailId {get; set;} public int? OrderID {get; set;} public int ProductID {get; set;} public Order Order {get; set;}} Fluent API configure associated entities

In the OnModelCreating method in DbContext, we use Fluent API to configure relationships in entities.

Public class NorthwindContext: DbContext {public virtual DbSet Orders {get; set;} public virtual DbSet OrderDetails {get; set;} protected override void OnModelCreating (ModelBuilder modelBuilder) {modelBuilder.Entity (builder = > {builder.HasMany (e = > e.OrderDetails) .WithOne (e = > e.Order). HasForeignKey (e = > e.OrderID). OnDelete (DeleteBehavior.ClientSetNull) );}}

In the OnDelete method, you need to pass the parameter DeleteBehavior enumeration, which has the following four values:

Public enum DeleteBehavior {Cascade, SetNull, ClientSetNull, Restrict}

The four enumerated values represent different behaviors, which is our focus today.

Create a table structure

We use this enumerated value to create the data table structure respectively.

[InlineData (DeleteBehavior.Cascade)] [InlineData (DeleteBehavior.SetNull)] [InlineData (DeleteBehavior.ClientSetNull)] [InlineData (DeleteBehavior.Restrict)] [Theory] public void Create_Database (DeleteBehavior behavior) {using (var northwindContext = new NorthwindContext (behavior)) {northwindContext.Database.EnsureDeleted (); northwindContext.Database.EnsureCreated () }}

The SQL statement for the four enumerated values to create the table is similar to the following, except that the statement after ON DELETE {} in the foreign key constraint [FK_Order Details_Orders_OrderID] is created.

CREATE TABLE [Orders] ([OrderID] int NOT NULL IDENTITY, [Name] nvarchar (max) NULL, [OrderDate] datetime2 NULL, CONSTRAINT [PK_Orders] PRIMARY KEY ([OrderID])) GOCREATE TABLE [Order Details] ([DetailId] int NOT NULL IDENTITY, [OrderID] int NOT NULL, [ProductID] int NOT NULL, CONSTRAINT [PK_Order Details] PRIMARY KEY ([DetailId]), CONSTRAINT [FK_Order Details_Orders_OrderID] FOREIGN KEY ([OrderID]) REFERENCES [Orders] ([OrderID]) ON DELETE CASCADE)

The SQL statements for the four enumerated values are as follows:

EF Core associated entity deletion behavior

We test the code by enumerating values and whether to track the associated entity. The test code is as follows:

[InlineData (DeleteBehavior.Cascade, true)] [InlineData (DeleteBehavior.Cascade, false)] [InlineData (DeleteBehavior.SetNull, true)] [InlineData (DeleteBehavior.SetNull, false)] [InlineData (DeleteBehavior.ClientSetNull, true)] [InlineData (DeleteBehavior.ClientSetNull, false)] [InlineData (DeleteBehavior.Restrict, true)] [InlineData (DeleteBehavior.Restrict, false)] [Theory] public void Execute (DeleteBehavior behavior) Bool includeDetail) {using (var northwindContext = new NorthwindContext (behavior)) {northwindContext.Database.EnsureDeleted () NorthwindContext.Database.EnsureCreated ();} int orderId; int detailId; using (var northwindContext = new NorthwindContext (behavior)) {var order = new Order {Name = "Order1"} Var orderDetail = new OrderDetail {ProductID = 11}; order.OrderDetails = new List {orderDetail}; northwindContext.Set () .Add (order); northwindContext.SaveChanges (); orderId = order.OrderID DetailId = orderDetail.DetailId;} using (var northwindContext = new NorthwindContext (behavior)) {var queryable = northwindContext.Set (). Where (e = > e.OrderID = = orderId); if (includeDetail) {queryable = queryable.Include (e = > e.OrderDetails) } var order = queryable.Single (); northwindContext.Set () .Remove (order); try {northwindContext.SaveChanges (); DumpSql () } catch (Exception) {DumpSql (); throw;}} using (var northwindContext = new NorthwindContext (behavior)) {var orderDetail = northwindContext.Set () .Find (detailId) If (behavior = = DeleteBehavior.Cascade) {Assert.Null (orderDetail);} else {Assert.NotNull (orderDetail);}

Summary

Based on the above test results, we can draw the following conclusions:

DeleteBehavior.Cascade

If the associated entity is not tracked and the status of the primary entity is marked for deletion, when SaveChage is executed, the data rows of the associated table are deleted through the behavior of the database while deleting the data of the main table.

If the associated entity has been tracked, when the status of the primary entity is marked for deletion, the status of the associated entity will also be marked for deletion. When performing SaveChange, delete the data rows of the associated table first, and then delete the data rows of the main table.

The foreign key can be set to a non-null value or to a nullable value

The associated entity may not be tracked.

DeleteBehavior.SetNull

If the associated entity is not tracked and the status of the primary entity is marked for deletion, when SaveChage is executed, when deleting the data of the primary table, the foreign key of the row of the associated table is updated to NULL through the behavior of the database.

If the associated entity has been tracked, when the status of the primary entity is marked for deletion, the foreign key of the associated entity will be set to null, and the status of the associated entity will be marked as modified. When performing SaveChange, update the data row of the associated table first, and then delete the data row of the main table.

Because you want to update the foreign key to NULL, the foreign key must be set to a nullable field

The associated entity may not be tracked.

DeleteBehavior.ClientSetNull

The database does not perform any behavior

The associated entity must be tracked. When the status of the primary entity is marked for deletion, the foreign key of the associated entity is set to null, and the status of the associated entity is marked as modified. When performing SaveChange, update the data rows of the associated table first, and then delete the data rows of the main table (in this case, the behavior is the same as DeleteBehavior.SetNull)

Because you want to update the foreign key to NULL, the foreign key must be set to a nullable field

The associated entity must be tracked or an exception will be thrown when the data is saved.

DeleteBehavior.Restrict

The framework does nothing, and the developer decides the behavior of the associated entity. The state of the associated entity can be set to delete, or the foreign key of the associated entity can be set to null.

Because you want to modify the state of the associated entity or the value of the foreign key, the associated entity must be tracked.

On how to delete the Entity Framework Core association to share here, I hope that the above content can be of some help to you, can learn more knowledge. If you think the article is good, you can share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.