We have 3 model classes:
Host has many TournamentBatch. TournamentBatch has many TournamentBatchItem. In the TournamentBatch table will have FK Host.
We did override for SaveChangesAsync in ApplicationDbContext to allow soft-delete as following:
public override Task<int> SaveChangesAsync(bool acceptAllChangesOnSuccess, CancellationToken cancellationToken = default(CancellationToken))
{
OnBeforeSaving();
return base.SaveChangesAsync(acceptAllChangesOnSuccess, cancellationToken);
}
private void OnBeforeSaving()
{
if (_httpContextAccessor.HttpContext != null)
{
var userName = _httpContextAccessor.HttpContext.User.Identity.Name;
var userId = _httpContextAccessor.HttpContext.User.FindFirstValue(ClaimTypes.NameIdentifier);
// Added
var added = ChangeTracker.Entries().Where(v => v.State == EntityState.Added && typeof(IBaseEntity).IsAssignableFrom(v.Entity.GetType())).ToList();
added.ForEach(entry =>
{
((IBaseEntity)entry.Entity).DateCreated = DateTime.UtcNow;
((IBaseEntity)entry.Entity).CreatedBy = userId;
((IBaseEntity)entry.Entity).LastDateModified = DateTime.UtcNow;
((IBaseEntity)entry.Entity).LastModifiedBy = userId;
});
// Modified
var modified = ChangeTracker.Entries().Where(v => v.State == EntityState.Modified &&
typeof(IBaseEntity).IsAssignableFrom(v.Entity.GetType())).ToList();
modified.ForEach(entry =>
{
((IBaseEntity)entry.Entity).LastDateModified = DateTime.UtcNow;
((IBaseEntity)entry.Entity).LastModifiedBy = userId;
});
// Deleted
var deleted = ChangeTracker.Entries().Where(v => v.State == EntityState.Deleted &&
typeof(IBaseEntity).IsAssignableFrom(v.Entity.GetType())).ToList();
// var deleted = ChangeTracker.Entries().Where(v => v.State == EntityState.Deleted).ToList();
deleted.ForEach(entry =>
{
((IBaseEntity)entry.Entity).DateDeleted = DateTime.UtcNow;
((IBaseEntity)entry.Entity).DeletedBy = userId;
});
foreach (var entry in ChangeTracker.Entries()
.Where(e => e.State == EntityState.Deleted &&
e.Metadata.GetProperties().Any(x => x.Name == "IsDeleted")))
{
switch (entry.State)
{
case EntityState.Added:
entry.CurrentValues["IsDeleted"] = false;
break;
case EntityState.Deleted:
entry.State = EntityState.Modified;
entry.CurrentValues["IsDeleted"] = true;
break;
}
}
}
else
{
// DbInitializer kicks in
}
}
In our model:
using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
using System.Linq;
using System.Threading.Tasks;
namespace AthlosifyWebArchery.Models
{
public class TournamentBatch : IBaseEntity
{
[Key]
public Guid TournamentBatchID { get; set; }
public Guid HostID { get; set; }
public string Name { get; set; }
public string BatchFilePath { get; set; }
[Display(Name = "Batch File Size (bytes)")]
[DisplayFormat(DataFormatString = "{0:N1}")]
public long BatchFileSize { get; set; }
[Display(Name = "Uploaded (UTC)")]
[DisplayFormat(DataFormatString = "{0:F}")]
public DateTime DateUploaded { get; set; }
public DateTime DateCreated { get; set; }
public string CreatedBy { get; set; }
public DateTime LastDateModified { get; set; }
public string LastModifiedBy { get; set; }
public DateTime? DateDeleted { get; set; }
public string DeletedBy { get; set; }
public bool IsDeleted { get; set; }
public Host Host { get; set; }
public ICollection<TournamentBatchItem> TournamentBatchItems { get; set; }
[Timestamp]
public byte[] RowVersion { get; set; }
[ForeignKey("CreatedBy")]
public ApplicationUser ApplicationCreatedUser { get; set; }
[ForeignKey("LastModifiedBy")]
public ApplicationUser ApplicationLastModifiedUser { get; set; }
}
}
In our Razorpage, we have a page to delete TournamentBatch including TournamentBatchItem by doing this:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using Microsoft.AspNetCore.Mvc;
using Microsoft.AspNetCore.Mvc.RazorPages;
using Microsoft.EntityFrameworkCore;
using AthlosifyWebArchery.Data;
using AthlosifyWebArchery.Models;
using Microsoft.Extensions.Logging;
namespace AthlosifyWebArchery.Pages.Administrators.TournamentBatches
{
public class DeleteModel : PageModel
{
private readonly AthlosifyWebArchery.Data.ApplicationDbContext _context;
private readonly ILogger _logger;
public DeleteModel(AthlosifyWebArchery.Data.ApplicationDbContext context,
ILogger<DeleteModel> logger)
{
_context = context;
_logger = logger;
}
[BindProperty]
public TournamentBatch TournamentBatch { get; set; }
public IList<TournamentBatchItem> tournamentBatchItems { get; set; }
public string ConcurrencyErrorMessage { get; set; }
public async Task<IActionResult> OnGetAsync(Guid? id, bool? concurrencyError)
{
if (id == null)
{
return NotFound();
}
TournamentBatch = await _context.TournamentBatch
.AsNoTracking() //Addded
.FirstOrDefaultAsync(m => m.TournamentBatchID == id);
if (TournamentBatch == null)
{
return NotFound();
}
if (concurrencyError.GetValueOrDefault())
{
ConcurrencyErrorMessage = "The record you attempted to delete "
+ "was modified by another user after you selected delete. "
+ "The delete operation was canceled and the current values in the "
+ "database have been displayed. If you still want to delete this "
+ "record, click the Delete button again.";
}
return Page();
}
public async Task<IActionResult> OnPostAsync(Guid? id)
{
try
{
//var tournamentBatchItems = await _context.TournamentBatchItem.Where(m => m.TournamentBatchID == id).ToListAsync();
//_context.TournamentBatchItem.RemoveRange(tournamentBatchItems);
//await _context.SaveChangesAsync();
if (await _context.TournamentBatch.AnyAsync(
m => m.TournamentBatchID == id))
{
// Department.rowVersion value is from when the entity
// was fetched. If it doesn't match the DB, a
// DbUpdateConcurrencyException exception is thrown.
_context.TournamentBatch.Remove(TournamentBatch);
_logger.LogInformation($"TournamentBatch.BeforeSaveChangesAsync ... ");
await _context.SaveChangesAsync();
_logger.LogInformation($"DbInitializer.AfterSaveChangesAsync ... ");
}
return RedirectToPage("./Index");
}
catch(DbUpdateException)
{
return RedirectToPage("./Delete",
new { concurrencyError = true, id = id });
}
//catch (DbUpdateConcurrencyException)
//{
// return RedirectToPage("./Delete",
// new { concurrencyError = true, id = id });
//}
}
}
}
... and we have the following error which is a bit odd.
System.Data.SqlClient.SqlException (0x80131904): The UPDATE statement conflicted with the FOREIGN KEY constraint "FK_TournamentBatch_Host_HostID". The conflict occurred in database "aspnet-AthlosifyWebArchery-53bc9b9d-9d6a-45d4-8429-2a2761773502", table "dbo.Host", column 'HostID'. The statement has been terminated.
Any ideas?
Things we did:
If we removed OnBeforeSaving(); from the SaveChangesAsyc()method, the code is deleting (hard-delete) successfully the TournamentBatch as well as TournamentBatchItem.
If we included OnBeforeSaving(); from the SaveChangesAsyc()method AND tested with deleting Host and TournamentBatchItem (Not TournamentBatch), the code is deleting (soft-delete) successfully.
It seems it has something to do with the relationship between Host and TournamentBatch
Environment:
Reason
I guess the reason is you're having your TournamentBatch bind from client side .
Let's review the OnPostAsync() method:
public async Task<IActionResult> OnPostAsync(Guid? id)
{
try
{
if (await _context.TournamentBatch.AnyAsync(
m => m.TournamentBatchID == id))
{
_context.TournamentBatch.Remove(TournamentBatch);
_logger.LogInformation($"TournamentBatch.BeforeSaveChangesAsync ... ");
await _context.SaveChangesAsync();
_logger.LogInformation($"DbInitializer.AfterSaveChangesAsync ... ");
}
return RedirectToPage("./Index");
}
// ....
}
Here the TournamentBatch is a property of PageModel:
[BindProperty]
public Models.TournamentBatch TournamentBatch{ get; set; }
Note you didn't retrieve it from the database according to the id, and you just remove it by _context.TournamentBatch.Remove(TournamentBatch); directly.
In other words, the other properties of TournamentBatch will be set by ModelBinding. Let's say if you submit only the Id, all the other property will be the default value. For example, Host will be null and the HostID will be the default 00000000-0000-0000-0000-000000000000. So when you save changes, the EF Core will update the model as below :
UPDATE [TournamentBatch]
SET [HostID] = '00000000-0000-0000-0000-000000000000' ,
[IsDeleted] = 1 ,
# ... other fields
WHERE [TournamentBatchID] = 'A6F5002A-60CA-4B45-D343-08D660167B06'
Because there's no Host record whose id equals 00000000-0000-0000-0000-000000000000, the database will complains :
The UPDATE statement conflicted with the FOREIGN KEY constraint "FK_TournamentBatch_Host_HostID". The conflict occurred in database "App-93a194ca-9622-487c-94cf-bcbe648c6556", table "dbo.Host", column 'Id'. The statement has been terminated.
How to fix
Instead of binding the TournamentBatch from client side, you need retrieve the TournamentBatch from server by TournamentBatch = await _context.TournamentBatch.FindAsync(id); . Thus you will have all the properties set correctly so that EF will update the field correctly :
try
{
//var tournamentBatchItems = await _context.TournamentBatchItem.Where(m => m.TournamentBatchID == id).ToListAsync();
//_context.TournamentBatchItem.RemoveRange(tournamentBatchItems);
//await _context.SaveChangesAsync();
TournamentBatch = await _context.TournamentBatch.FindAsync(id);
if (TournamentBatch != null)
{
// Department.rowVersion value is from when the entity
// was fetched. If it doesn't match the DB, a
// DbUpdateConcurrencyException exception is thrown.
_context.TournamentBatch.Remove(TournamentBatch);
_logger.LogInformation($"TournamentBatch.BeforeSaveChangesAsync ... ");
await _context.SaveChangesAsync();
_logger.LogInformation($"DbInitializer.AfterSaveChangesAsync ... ");
}
return RedirectToPage("./Index");
}
// ...