Error trying to login into ASP.NET Identity 2, column UserId not valid

Question

After a lot of time working with ASP.NET Membership, at last I've some spare free time to dig into Identity and try to upgrade our developing framework to use it.

So... I'm reading a lot of info, tutorials into the matter and trying to put it to work.

At this moment, I've been able to register users into the database and assign roles to them. I've also changed the model to differenciate between username and email for user accounts, as usually into our applications we don't use email accounts for user identification.

However, I've hit a wall with login. When controller tries to execute PasswordSignInAsync I receive an unhandled exception like this:

System.Data.SqlClient.SqlException: Column name 'UserId' is not valid. Column name 'UserId' is not valid.

I'm totally lost with this problem, I don't see where those UserId columns are coming from (the only ones on database model are on AspNetUserRoles and in AspNetUserLogins) or why they aren't being recognized during query execution.

This is the controller Login action:

    [HttpPost]
    [AllowAnonymous]
    [ValidateAntiForgeryToken]
    public async Task<ActionResult> Login(LoginViewModel model, string returnUrl) {
        if (!ModelState.IsValid) {
            return View(model);
        }

        var result = await SignInManager.PasswordSignInAsync(model.username, model.Password, model.RememberMe, shouldLockout: true);
        switch (result) {
            case SignInStatus.Success:
                return RedirectToLocal(returnUrl);
            case SignInStatus.LockedOut:
                return View("Lockout");
            case SignInStatus.RequiresVerification:
                return RedirectToAction("SendCode", new { ReturnUrl = returnUrl, RememberMe = model.RememberMe });
            case SignInStatus.Failure:
            default:
                ModelState.AddModelError("", "Login error.");
                return View(model);
        }
    }

The only change respect the base project is that I've changed the first parameter on the call from model.Email to model.username.

Any insight on the problem, how to trace or fix it will be much appreciated.

Answer 1

The problem could be that your database doesn't match the database model that your code is expecting.

Here's the ASP.NET Identity database as generated by code-first in a brand new ASP.NET MVC project with Identity from VS2013:

CREATE TABLE [dbo].[AspNetRoles] ( 
[Id] [nvarchar](128) NOT NULL, 
[Name] [nvarchar](256) NOT NULL, 
CONSTRAINT [PK_dbo.AspNetRoles] PRIMARY KEY ([Id]) 
) 
CREATE UNIQUE INDEX [RoleNameIndex] ON [dbo].[AspNetRoles]([Name]) 
CREATE TABLE [dbo].[AspNetUserRoles] ( 
[UserId] [nvarchar](128) NOT NULL, 
[RoleId] [nvarchar](128) NOT NULL, 
CONSTRAINT [PK_dbo.AspNetUserRoles] PRIMARY KEY ([UserId], [RoleId]) 
) 
CREATE INDEX [IX_UserId] ON [dbo].[AspNetUserRoles]([UserId]) 
CREATE INDEX [IX_RoleId] ON [dbo].[AspNetUserRoles]([RoleId]) 
CREATE TABLE [dbo].[AspNetUsers] ( 
[Id] [nvarchar](128) NOT NULL, 
[Email] [nvarchar](256), 
[EmailConfirmed] [bit] NOT NULL, 
[PasswordHash] [nvarchar](max), 
[SecurityStamp] [nvarchar](max), 
[PhoneNumber] [nvarchar](max), 
[PhoneNumberConfirmed] [bit] NOT NULL, 
[TwoFactorEnabled] [bit] NOT NULL, 
[LockoutEndDateUtc] [datetime], 
[LockoutEnabled] [bit] NOT NULL, 
[AccessFailedCount] [int] NOT NULL, 
[UserName] [nvarchar](256) NOT NULL, 
CONSTRAINT [PK_dbo.AspNetUsers] PRIMARY KEY ([Id]) 
) 
CREATE UNIQUE INDEX [UserNameIndex] ON [dbo].[AspNetUsers]([UserName]) 
CREATE TABLE [dbo].[AspNetUserClaims] ( 
[Id] [int] NOT NULL IDENTITY, 
[UserId] [nvarchar](128) NOT NULL, 
[ClaimType] [nvarchar](max), 
[ClaimValue] [nvarchar](max), 
CONSTRAINT [PK_dbo.AspNetUserClaims] PRIMARY KEY ([Id]) 
) 
CREATE INDEX [IX_UserId] ON [dbo].[AspNetUserClaims]([UserId]) 
CREATE TABLE [dbo].[AspNetUserLogins] ( 
[LoginProvider] [nvarchar](128) NOT NULL, 
[ProviderKey] [nvarchar](128) NOT NULL, 
[UserId] [nvarchar](128) NOT NULL, 
CONSTRAINT [PK_dbo.AspNetUserLogins] PRIMARY KEY ([LoginProvider], [ProviderKey], [UserId]) 
) 
CREATE INDEX [IX_UserId] ON [dbo].[AspNetUserLogins]([UserId]) 
ALTER TABLE [dbo].[AspNetUserRoles] ADD CONSTRAINT [FK_dbo.AspNetUserRoles_dbo.AspNetRoles_RoleId] FOREIGN KEY ([RoleId]) REFERENCES [dbo].[AspNetRoles] ([Id]) ON DELETE CASCADE 
ALTER TABLE [dbo].[AspNetUserRoles] ADD CONSTRAINT [FK_dbo.AspNetUserRoles_dbo.AspNetUsers_UserId] FOREIGN KEY ([UserId]) REFERENCES [dbo].[AspNetUsers] ([Id]) ON DELETE CASCADE 
ALTER TABLE [dbo].[AspNetUserClaims] ADD CONSTRAINT [FK_dbo.AspNetUserClaims_dbo.AspNetUsers_UserId] FOREIGN KEY ([UserId]) REFERENCES [dbo].[AspNetUsers] ([Id]) ON DELETE CASCADE 
ALTER TABLE [dbo].[AspNetUserLogins] ADD CONSTRAINT [FK_dbo.AspNetUserLogins_dbo.AspNetUsers_UserId] FOREIGN KEY ([UserId]) REFERENCES [dbo].[AspNetUsers] ([Id]) ON DELETE CASCADE

Compare your database to this structure and it should work :)