I upgraded my ASP.NET Core 6 Web API to .NET 8.0.
At the Signin page, I have this code:
public async Task<IActionResult> LoginAttempt([Bind("Email","Password","PingIdPin","SelectDeviceMessage","SelectDevice","CollectPinMessage","CollectPin","MultipleDevices","MultipleDevicesYesNo","ChallengeId","UserPrompt","LoginFailed")] LoginModel loginModel)
{
// more snippets of the code
Microsoft.AspNetCore.Identity.SignInResult result = new();
try
{
result = await _signInManager.PasswordSignInAsync(user, password, false, false);
}
catch (Exception ex)
{
string ss = ex.Message;
_logger.LogInformation(-999000001, "{0} ... result = await _signInManager.PasswordSignInAsync(user, password, false, false); [{1}]", nameof(Login), ex.Message);
}
}
I get this exception:
EntityFrameworkCore.DbUpdateException: Microsoft.EntityFrameworkCore.DbUpdateException: 'Could not save changes because the target table has database triggers. Please configure your table accordingly, see https://aka.ms/efcore-docs-sqlserver-save-changes-and-output-clause for more information.'
SqlException: The target table 'AspNetUsers' of the DML statement cannot have any enabled triggers if the statement contains an OUTPUT clause without INTO clause.
I upgraded all the Nuget packages to 8.0.8 for Entity Framework Core.
Please see screenshot with the NugetPackages for 8.0
With .NET 6.0, it worked seamlessly. Not sure what am I missing when upgrading to .NET 8.0.
Edited this post to add my DBcontext.cs code
namespace AspNetCoreApi.Models.Identity
{
public class AppIdentityDbContext : IdentityDbContext<ApplicationUser<int>,
ApplicationRole<int>,
int,
ApplicationUserClaim<int>,
ApplicationUserRole<int>,
ApplicationUserLogin<int>,
ApplicationRoleClaim<int>,
ApplicationUserToken<int>>
{
public AppIdentityDbContext() { }
public static string ConnectionString { get; set; }
public static int MessageMaxLength;
public AppIdentityDbContext(DbContextOptions<AppIdentityDbContext> options)
: base(options) { }
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
optionsBuilder.UseSqlServer(ConnectionString);
base.OnConfiguring(optionsBuilder);
}
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.Entity<AspNetUsers>().ToTable(tb => tb.HasTrigger("TriggerName"));
base.OnModelCreating(modelBuilder);
modelBuilder.Entity<ApplicationUser<int>>(entity =>
{
entity.ToTable(
"AspNetUsers");
entity.HasKey(x => x.Id);
entity.HasIndex(e => e.NormalizedEmail)
.HasDatabaseName("EmailIndex");
entity.HasIndex(e => e.NormalizedUserName)
.HasDatabaseName("UserNameIndex")
.IsUnique()
.HasFilter("([NormalizedUserName] IS NOT NULL)");
entity.Property(e => e.CreatedBy).HasMaxLength(50);
entity.Property(e => e.Email).HasMaxLength(128);
entity.Property(e => e.Facility).HasMaxLength(50);
entity.Property(e => e.FirstName).HasMaxLength(50);
entity.Property(e => e.LastName).HasMaxLength(50);
entity.Property(e => e.Name).HasMaxLength(128);
entity.Property(e => e.NormalizedEmail).HasMaxLength(128);
entity.Property(e => e.NormalizedUserName).HasMaxLength(128);
entity.Property(e => e.PhoneNumber).HasMaxLength(50);
entity.Property(e => e.UpdatedBy).HasMaxLength(50);
entity.Property(e => e.UserName).HasMaxLength(50);
entity
.HasMany(e => e.Claims)
.WithOne()
.HasForeignKey(e => e.UserId);
entity
.HasMany(e => e.Tokens)
.WithOne()
.HasForeignKey(e => e.UserId);
entity
.HasMany(e => e.Logins)
.WithOne()
.HasForeignKey(e => e.UserId);
entity
.HasMany(e => e.Roles)
.WithOne()
.HasForeignKey(e => e.UserId);
});
}
}
Below is my generated SQL.
public async Task<int> SaveUser(UserVM model, string thisUser)
{
bool hasErrors = false;
int retUserId = 0;
if (model.Id > 0)
{
AspNetUsers user = await (from u in _context.AspNetUsers
where u.Id == model.Id
select u).FirstOrDefaultAsync();
user.Email = model.Email;
user.NormalizedEmail = model.Email.ToUpper();
user.FirstName = model.FirstName;
user.MiddleName = model.MiddleName;
user.LastName = model.LastName;
user.Inactive = !model.Active;
user.UserName = model.UserName;
user.NormalizedUserName = model.UserName.ToUpper();
user.Name = model.UserName;
user.UpdateDate = DateTime.Now;
user.UpdatedBy = thisUser;
_context.AspNetUsers.Update(user);
await _context.SaveChangesAsync();
AspNetUserClaims claimLname = await (from c in _context.AspNetUserClaims
where c.UserId == user.Id
&& c.ClaimType == "Surname"
select c).FirstOrDefaultAsync();
if (claimLname != null)
{
if (claimLname.ClaimValue != user.LastName)
{
claimLname.ClaimValue = user.LastName;
_context.AspNetUserClaims.Update(claimLname);
await _context.SaveChangesAsync();
}
}
AspNetUserClaims claimFname = await (from c in _context.AspNetUserClaims
where c.UserId == user.Id
&& c.ClaimType == "GivenName"
select c).FirstOrDefaultAsync();
if (claimLname != null)
{
if (claimLname.ClaimValue != user.LastName)
{
claimFname.ClaimValue = user.FirstName;
_context.AspNetUserClaims.Update(claimFname);
await _context.SaveChangesAsync();
}
}
if (hasErrors == false)
{
var deleteRoles = await (
//_context.AspNetRoles.Where(x => EF.Property<int>(x, "UserId") == user.Id).ToListAsync());
_context.AspNetUserRoles.Where(x => x.UserId == user.Id).ToListAsync());
foreach (var i in deleteRoles)
{
_context.AspNetUserRoles.Remove(i);
try
{
await _context.SaveChangesAsync();
}
catch (Exception ex)
{
string s = ex.Message;
}
}
// remove claim for Role
var userClaimRoles = await _context.AspNetUserClaims
.Where(x => x.UserId == user.Id && x.ClaimType == "Role")
.ToListAsync();
foreach (AspNetUserClaims claim in userClaimRoles)
{
_context.AspNetUserClaims.Remove(claim);
}
// remove claim for Name Identifier
userClaimRoles = await _context.AspNetUserClaims
.Where(x => x.UserId == user.Id && x.ClaimType == "NameIdentifier")
.ToListAsync();
foreach (AspNetUserClaims claim in userClaimRoles)
{
_context.AspNetUserClaims.Remove(claim);
}
await _context.SaveChangesAsync();
string roleName = await (from r in _context.AspNetRoles
where r.Id == model.RoleId
select r.RoleName).FirstOrDefaultAsync();
var entity = await _userManager.FindByEmailAsync(user.Email);
if (entity != null)
{
if (!string.IsNullOrEmpty(roleName))
{
await _userManager.AddToRoleAsync(entity, roleName);
await _userManager.AddClaimAsync(entity, new Claim("Role", roleName));
}
await _userManager.AddClaimAsync(entity, new Claim("NameIdentifier", user.UserName));
await _context.SaveChangesAsync();
}
// set if no errors otherwise return 0 which is error in page
retUserId = user.Id;
}
}
else
{
int newUserId = 0;
ApplicationUser<int> entity = new ApplicationUser<int>
{
Id = model.Id,
Email = model.Email,
UserName = model.UserName,
FirstName = model.FirstName,
MiddleName = model.MiddleName,
LastName = model.LastName,
Name = model.UserName,
NormalizedUserName = model.UserName.ToUpper(),
NormalizedEmail = model.Email.ToUpper(),
CreateDate = DateTime.Now,
CreatedBy = thisUser,
UpdateDate = DateTime.Now,
UpdatedBy = thisUser,
Inactive = !model.Active,
SecurityStamp = Guid.NewGuid().ToString()
};
IdentityResult result
= await _userManager.CreateAsync(entity, model.Password);
if (result.Succeeded)
{
newUserId = entity.Id;
await _userManager.AddClaimAsync(entity, new Claim("GivenName", entity.FirstName));
await _userManager.AddClaimAsync(entity, new Claim("Surname", entity.LastName));
await _userManager.AddClaimAsync(entity, new Claim("NameIdentifier", entity.UserName));
string roleName = await (from r in _context.AspNetRoles
where r.Id == model.RoleId
select r.RoleName).FirstOrDefaultAsync();
await _userManager.AddToRoleAsync(entity, roleName);
await _userManager.AddClaimAsync(entity, new Claim("Role", roleName));
await _context.SaveChangesAsync();
retUserId = newUserId;
}
else
{
foreach (IdentityError err in result.Errors)
{
_logger.LogError(3, "userId: " + newUserId.ToString() + " " + err.Code + " - " + err.Description);
}
}
}
return retUserId;
}
If you have multiple tables with triggers, you can simplify your code by creating an extension that automatically applies HasTrigger to all entities.
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
base.OnModelCreating(modelBuilder);
// Configure all entities
...
// Automatically apply HasTrigger to all entities
modelBuilder.ApplyHasTrigger();
}
Here’s the implementation of the extension method:
public static class ModelBuilderExtensions
{
public static ModelBuilder ApplyHasTrigger(this ModelBuilder modelBuilder)
{
foreach (var entityType in modelBuilder.Model.GetEntityTypes())
{
var tableName = entityType.GetTableName();
if (tableName != null)
{
entityType.AddTrigger("fake_trigger_" + tableName);
}
}
return modelBuilder;
}
}