Learn how to delete records from the database using Entity Framework Core. Deleting an entity is done using the Remove or RemoveRange method of the DbSet. Alternatively, you can also set the entity state as Deleted. We can delete records either in connected or disconnected Scenarios. We will also look at how to remove multiple records from the database using the RemoveRange method.
Source Code:
The source code of this project available in GitHub. It also contains the script of the database
Table of Contents
Delete Record
How you delete the entity depends on whether you are deleting the record in Connected or Disconnected scenario. In Connected Scenario, you can use the Remove or RemoveRange method to mark the record as Deleted. In Disconnected Scenario, you can attach it to the context and set its state as Deleted. Calling SaveChanges will send the delete query to the database.
Connected Scenario
Deleting in a connected scenario is very straight forward. Query the Department entity from the database. Call Remove method and pass the Department object to delete. The Change tracking in Entity Framework marks the entity as Deleted. Finally, SaveChanges will remove the Department from the database using the Delete Query
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | public void DeleteConnected() { Department department; //Connected Scenario using (EFCoreContext db = new EFCoreContext()) { department = db.Departments.Where(d => d.Name == "Sales").First(); db.Departments.Remove(department); db.SaveChanges(); } } |
SQL Query
1 2 3 4 | DELETE FROM [Departments] WHERE [DepartmentID] = @p0; |
Disconnected Scenario
The following is the disconnected scenario example. First, we retrieve the Department entity and close the context. Later we open a new context and use the db.Entry method to set its state as Deleted. If the model is not in the context, then the Entry method adds it to the context and sets its state as Deleted
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 | public void DeleteDisconnected() { Department department; //Disconnected Scenario using (EFCoreContext db = new EFCoreContext()) { department = db.Departments.Where(d => d.Name == "Sales").First(); } using (EFCoreContext db = new EFCoreContext()) { //This also works //db.Departments.Attach(department); //db.Entry(department).State = System.Data.Entity.EntityState.Deleted; db.Entry(department).State = EntityState.Deleted; db.SaveChanges(); } } |
Attach Method
You can also use the Attach and remove method as shown below. Note that Attach will add the entity to the context in Unchanged state.
1 2 3 4 | db.Departments.Attach(dep); db.Entry(dep).State = System.Data.Entity.EntityState.Deleted; |
Delete without loading from the database
You can delete the entity without loading from the database, provided you know the Primary key value. The following example shows how to delete without loading from the database. We create a new department entity and assign the 2 to DepartmentID. Next, we attach it to the context and set its state as Deleted. On SaveChanges the EF will delete the Department
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | public void DeleteDisconnectedWithoutLoading() { Department department; department = new Department() { DepartmentID = 2 }; using (EFCoreContext db = new EFCoreContext()) { db.Entry(department).State = EntityState.Deleted; db.SaveChanges(); } Console.WriteLine("Department {0} is Deleted ", department.DepartmentID); Console.ReadKey(); } |
Delete & ConcurrencyCheck
The above code will not work if you have enabled ConcurrencyCheck on the table. For example, open the Department model and add the ConcurrencyCheck on the Name property as shown below.
1 2 3 4 5 6 7 8 9 10 11 12 | public class Department { public int DepartmentID { get; set; } [ConcurrencyCheck] public string Name { get; set; } public string Descr { get; set; } public virtual ICollection<Employee> Employees { get; set; } } |
Run the above code again and you will see the following exepction
Microsoft.EntityFrameworkCore.DbUpdateConcurrencyException: 'Database operation expected to affect 1 row(s) but actually affected 0 row(s). Data may have been modified or deleted since entities were loaded
Delete Multiple Records
You can use RemoveRange method of DbSet to remove multiple records at a time. The following example code deletes 3 departments from the database in the connected scenario.
The EF sends one delete query at time to the database. So if you delete 100 records, the EF will issue 100 delete statements
1 2 3 4 5 6 7 8 9 10 11 12 | public void DeleteMultipleRecordsConnected() { //Deleting Multiple Records using (EFCoreContext db = new EFCoreContext()) { List<Department> deps = db.Departments.Take(2).ToList(); db.Departments.RemoveRange(deps); db.SaveChanges(); } } |
The following code deletes the multiple records in disconnected scenario.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | public void DeleteMultipleRecordsDisconnected() { List<Department> departments = new List<Department>(); departments.Add(new Department { DepartmentID = 1 }); departments.Add(new Department { DepartmentID = 2 }); //Deleting Multiple Records using (EFCoreContext db = new EFCoreContext()) { db.Entry(departments).State = EntityState.Deleted; db.SaveChanges(); } Console.ReadKey(); } |
I have created a library to batch delete or update records with a round trip on EF Core 5.
Sample code as follows:
await ctx.DeleteRangeAsync(b => b.Price > n || b.AuthorName == “zack yang”);
await ctx.BatchUpdate()
.Set(b => b.Price, b => b.Price + 3)
.Set(b=>b.AuthorName,b=>b.Title.Substring(3,2)+b.AuthorName.ToUpper())
.Set(b => b.PubTime, b => DateTime.Now)
.Where(b => b.Id > n || b.AuthorName.StartsWith(“Zack”))
.ExecuteAsync();
Github repository: https://github.com/yangzhongke/Zack.EFCore.Batch
Report: https://www.reddit.com/r/dotnetcore/comments/k1esra/how_to_batch_delete_or_update_in_entity_framework/
In “Delete without loading from the database with concurrency check” it is worth mentioning that if the concurrency value is passed while creating the object, it will not give the Microsoft.EntityFrameworkCore.DbUpdateConcurrencyException exception