Reputation: 119
I am new to asp.net core and I created an app with Asp.Net Core 2.2 +EF using Identity Core to create a registration page and I got this error after I ran the app and introduced some test data(email, password):
SqlException: Invalid column name 'NormalizedUserName'.
Invalid column name 'AccessFailedCount'.
Invalid column name 'ConcurrencyStamp'.
Invalid column name 'Email'.
Invalid column name 'EmailConfirmed'.
Invalid column name 'LockoutEnabled'.
Invalid column name 'LockoutEnd'.
Invalid column name 'NormalizedEmail'.
Invalid column name 'NormalizedUserName'.
Invalid column name 'PasswordHash'.
Invalid column name 'PhoneNumber'.
Invalid column name 'PhoneNumberConfirmed'.
Invalid column name 'SecurityStamp'.
Invalid column name 'TwoFactorEnabled'.
Invalid column name 'UserName'.
I created a table and I don't have my columns named like above (mine are "EmailAddress" and "Password" only)
Account controller(ignore the LOGIN methods):
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using Microsoft.AspNetCore.Identity;
using Microsoft.AspNetCore.Identity.EntityFrameworkCore;
using Microsoft.AspNetCore.Mvc;
using XmlViewer.Models;
using XmlViewer.ViewModels;
namespace XmlViewer.Controllers
{
public class AccountController : Controller
{
private readonly UserManager<ApplicationUser> _userManager;
private readonly SignInManager<ApplicationUser> _signInManager;
public AccountController(UserManager<ApplicationUser> userManager, SignInManager<ApplicationUser> signInManager)
{
_userManager = userManager;
_signInManager = signInManager;
}
[HttpGet]
[HttpGet]
public IActionResult Login()
{
return View();
}
[HttpPost]
public async Task<IActionResult> Login(LoginViewModel vm)//model binding
{
//date din vm sunt bune:
if(ModelState.IsValid)
{
var result = await _signInManager.PasswordSignInAsync(vm.Email, vm.Password, vm.RememberMe, false);//folosit para pt IsPersistent(BOOL)
if(result.Succeeded)
{
return RedirectToAction("Privacy", "Home");
}
ModelState.AddModelError("","Invalid Login. Please Check your username/email or password.");
return View(vm);
}
return View(vm);
}
public IActionResult Register()
{
return View();
}
[HttpPost]
public async Task<IActionResult> Register(RegisterViewModel vm)//model binding
{
if (ModelState.IsValid)
{
var user = new ApplicationUser { UserName = vm.Email, Email = vm.Email };
var result = await _userManager.CreateAsync(user, vm.Password);
if (result.Succeeded)
{
await _signInManager.SignInAsync(user, false);
return RedirectToAction("Index", "Home");
}
else
{
foreach (var error in result.Errors)
{
ModelState.AddModelError("", error.Description); //erori de inregistrare in cont
}//iterare prin fiecare eroare
}
}
return View(vm);
}
}
}
Context model:
using Microsoft.AspNetCore.Identity;
using Microsoft.AspNetCore.Identity.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
namespace XmlViewer.Models
{
public class XmlViewerContext : IdentityDbContext<ApplicationUser>
{
//constructor
//added migration Initial-Migration:tabelul de date in DB se creeaza dupa
public XmlViewerContext(DbContextOptions<XmlViewerContext> options) : base(options)
{
}
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
base.OnModelCreating(modelBuilder);
modelBuilder.Entity<ApplicationUser>().ToTable("Register").Property(p => p.Id).HasColumnName("EmailAddress"); ;
modelBuilder.Entity<ApplicationUser>().ToTable("Register").Property(p => p.Id).HasColumnName("Password");
}
}
}
Startup:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using Microsoft.AspNetCore.Builder;
using Microsoft.AspNetCore.Hosting;
using Microsoft.AspNetCore.Http;
using Microsoft.AspNetCore.HttpsPolicy;
using Microsoft.AspNetCore.Mvc;
using Microsoft.Extensions.Configuration;
using Microsoft.Extensions.DependencyInjection;
using Microsoft.EntityFrameworkCore;
using XmlViewer.Models;
using Microsoft.AspNetCore.Identity;
namespace XmlViewer
{
public class Startup
{
public static int Progress { get; set; }
public Startup(IConfiguration configuration)
{
Configuration = configuration;
}
public IConfiguration Configuration { get; }
// This method gets called by the runtime. Use this method to add services to the container.
public void ConfigureServices(IServiceCollection services)
{
services.Configure<CookiePolicyOptions>(options =>
{
// This lambda determines whether user consent for non-essential cookies is needed for a given request.
options.CheckConsentNeeded = context => true;
options.MinimumSameSitePolicy = SameSiteMode.None;
});
services.AddIdentity<ApplicationUser, IdentityRole>()
.AddEntityFrameworkStores<XmlViewerContext>()
.AddDefaultTokenProviders();
services.AddDbContext<XmlViewerContext>(options => options.UseSqlServer(@"Data Source = (localdb)\ProjectsV13; Initial Catalog = XmlViewer; Integrated Security = True; Connect Timeout = 30;"));
//Alta baza de date nu master
services.AddMvc().SetCompatibilityVersion(CompatibilityVersion.Version_2_2);
}
// This method gets called by the runtime. Use this method to configure the HTTP request pipeline.
public void Configure(IApplicationBuilder app, IHostingEnvironment env)
{
if (env.IsDevelopment())
{
app.UseDeveloperExceptionPage();
}
else
{
app.UseExceptionHandler("/Home/Error");
// The default HSTS value is 30 days. You may want to change this for production scenarios, see https://aka.ms/aspnetcore-hsts.
app.UseHsts();
}
app.UseAuthentication();
app.UseHttpsRedirection();
app.UseStaticFiles();
app.UseCookiePolicy();
app.UseMvc(routes =>
{
routes.MapRoute(
name: "default",
template: "{controller=Account}/{action=Register}/{id?}");
});
}
}
}
Upvotes: 5
Views: 7372
Reputation: 1
If you don't want to use these external columns and also want to inherit Identity, you can declare the column names for example (public string NormalizedName {get;set;} and add [NotMapped] above it, hence entity framework will ignore that. Hope this helps
Upvotes: 0
Reputation: 285
@AlexGodo I know this topic is old, but it might help someone moving ASP.NET project to ASP.NET Core who doesn't want to use migrations and just connect to existing AspNet*
tables.
To resolve the missing columns issue, just add them to the AspNetUsers
table (via SSMS for eg.):
I had 4 missing columns where NormalizedUserName
was the important one (random datatypes - TBC):
NormalizedUserName nvarchar(256), null
NormalizedEmail nvarchar(256), null
LockoutEnd datetime, null
ConcurrencyStamp nvarchar(256), null
To be able to log in, I had run the update to fill NormalizedUserName column like below:
update AspNetUsers
set NormalizedUserName = UPPER(Email)
where NormalizedUserName is null
Note: After login via ASP.NET Core website PasswordHash
column entries changes, so it won't be possible to login in via old website anymore.
(It's good idea to do a backup of this column if needed.)
Hope this helps.
Upvotes: 22
Reputation: 10078
When you scaffold Identity
(by clicking "individual user account" in visual studio), it generates the model for you. You need to run migrations on that model. This will create AspNet* tables that will have the columns that you are missing.
It looks like you created those tables manually, without the columns that Microsoft Identity expects
Upvotes: 0