Login/Register
Migrating SimpleMembership to Identity 2.0
Asked Answered
Solved asp.net-identityentity-framework-6.1
P

1

6

This question has evolved so I've updated the title.

This was the original title: Identity 2 UserManager.Find throws "Invalid object name 'dbo.ApplicationUser'" error

I'm converting from SimpleMembership to Identity 2. I've ran the conversion script and refactored the various files for Identity use. I can build and run the app but when attempt to login an "Invalid object name 'dbo.ApplicationUser'" error is thrown on var user = UserManager.Find(vM.UserName, vM.Password);

Account Controller:

  [RequireHttps]
  [Authorize]
  public class AccountController : Controller
  {
    private readonly IUserService _userService;

    public UserManager<ApplicationUser> UserManager { get; private set; }

    public AccountController() 
    : this(new UserService(), new UserManager<ApplicationUser>(new UserStore<ApplicationUser>(new MyDb()))) { } 

    public AccountController(IUserService userService, UserManager<ApplicationUser> userManager)
    { _userService = userService; UserManager = userManager; }

    // GET: /Account/Login
    [AllowAnonymous]
    public ActionResult Login() { return View(); }

    // POST: /Account/Login
    [HttpPost]
    [AllowAnonymous]
    [ValidateAntiForgeryToken]
    public ActionResult Login(LoginVm vM)
    { 
      if (ModelState.IsValid) 
      { 
        var user = UserManager.Find(vM.UserName, vM.Password);
        if (user != null)
        { 
          FormsAuthentication.SetAuthCookie(user.UserName, false);
          return RedirectToAction("Index", "Home");
        }
      } 
      ModelState.AddModelError("", "The user name or password provided is incorrect.");

      return View(vM);
    }

ApplicationUser:

  public class ApplicationUser : IdentityUser
  { 
    [StringLength(15)]
    public new string UserName { get; set; }
    public int AcId { get; set; }
    public int LcId { get; set; }
    public string ConfirmationToken { get; set; }
    public bool IsConfirmed { get; set; }
    public string PasswordResetToken { get; set; }
  }

DbContext:

  public class MyDb : IdentityDbContext<ApplicationUser> // DbContext  
  {
    public MyDb() : base("MyApplicaiton") { }

    // public virtual DbSet<UserProfiles> Users { get; set; }
    public virtual DbSet<MyTable> MyTables { get; set; } // properties marked virtual for Mocking override
    ...

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
      modelBuilder.Conventions.Remove<PluralizingTableNameConvention>();

      modelBuilder.Entity<IdentityUserLogin>().HasKey<string>(l => l.UserId);
      modelBuilder.Entity<IdentityRole>().HasKey<string>(r => r.Id);
      modelBuilder.Entity<IdentityUserRole>().HasKey(r => new { r.RoleId, r.UserId });
    }
  }

Why is user manager trying to access dbo.[ApplicationUser] (which doesn't exist) rather than dbo.[AspNetUsers]?

UPDATE 1: I downgraded to Microsoft.AspNet.Identity.EntityFramework 1.0 and Microsoft.AspNet.Identity.Core 1.0 and I now get a "Invalid object name 'dbo.IdentityUser'" error when UserManager.Find is called.

UPDATE 2:

I upgraded back to Identity 2.0 and just to see what would happen backed up and deleted the database and regenerated it with code first (enable-migrations, update-database).

Instead of adding the default Identity tables of:
AspNetRoles
AspNetClaims
AspNetUserLogins
AspNetUserRoles
AspNetUsers

It added these tables:
dbo.ApplicationUser
dbo.IdentityRole
dbo.IdentityUserClaim
dbo.IdentityUserLogin
dbo.IdentityUserRole

Which would explain why it is looking for ApplicationUser. What is it about my configuration that is forcing those names instead of the standard Identity names? I probably could change my migration script to those names but then I would end up with non-standard table names which would only lead to confusion going forward. How do I configure things to get the default Identity table names?

Precious answered 28/4, 2014 at 16:31 Comment(0)
P
2

The problem with the table names was with the override of OnModelCreating. My calls to .Entity<...>().HasKey were invoking those table names. For more on the override see Olav Nyb0's answer here: Asp.net Identity Validation Error. I've updated the OnModelCreating to:

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
  base.OnModelCreating(modelBuilder);
  modelBuilder.Conventions.Remove<PluralizingTableNameConvention>();
}

My ApplicationUser and migration script were modeled on Identity 1.0 and I needed to update them for Identity 2.0.

ApplicationUser: 

  public class ApplicationUser : IdentityUser
  { 
    public int AcId { get; set; }
    public int LcId { get; set; }
   }

Here's the migration script I ended up with that I run against my SimpleMembership database. A bit off the original question but I include it here to hopefully save someone else the hours I spent figuring it out.

    /****** Object: Table [dbo].[AspNetRoles] Script Date: 4/29/14 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

IF OBJECT_ID('dbo.AspNetUserRoles', 'U') IS NOT NULL
    DROP TABLE [dbo].[AspNetUserRoles]
GO
--IF OBJECT_ID('dbo.AspNetUserLogins', 'U') IS NOT NULL
--  DROP TABLE [dbo].[AspNetUserLogins]
--GO
IF OBJECT_ID('dbo.AspNetUserClaims', 'U') IS NOT NULL
    DROP TABLE [dbo].[AspNetUserClaims]
GO
IF OBJECT_ID('dbo.AspNetRoles', 'U') IS NOT NULL
    DROP TABLE [dbo].[AspNetRoles]
GO
IF OBJECT_ID('dbo.AspNetUsers', 'U') IS NOT NULL
    DROP TABLE [dbo].[AspNetUsers]
GO

CREATE TABLE [dbo].[AspNetUsers] (
    [Id]                                      NVARCHAR (128) NOT NULL,
    [UserName]                                NVARCHAR (15) NULL,
    [AcId]                                    INT            NOT NULL,
    [LcId]                                    INT            NOT NULL,
    [Email]                                   NVARCHAR (256) NULL,
    [EmailConfirmed]                          BIT            DEFAULT ((0)) NULL,
    [PasswordHash]                            NVARCHAR (MAX) NULL,
    [SecurityStamp]                           NVARCHAR (MAX) NULL,  
    [PhoneNumber]                             NVARCHAR (MAX) NULL,
    [PhoneNumberConfirmed]                    BIT            DEFAULT ((0)) NULL,
    [TwoFactorEnabled]                        BIT            DEFAULT ((0)) NULL,
    [LockoutEndDateUtc]                       DATETIME       NULL,
    [Lockoutenabled]                          BIT            DEFAULT ((0)) NULL,
    [AccessFailedCount]                       INT            DEFAULT ((0)) NOT NULL,
    [Discriminator]                           NVARCHAR (128) NOT NULL,
    [CreateDate]                              DATETIME       NULL,
    [ConfirmationToken]                       NVARCHAR (128) NULL,
    [IsConfirmed]                             BIT            DEFAULT ((0)) NULL,
    [LastPasswordFailureDate]                 DATETIME       NULL,
    [PasswordFailuresSinceLastSuccess]        INT            DEFAULT ((0)) NULL,
    [PasswordChangedDate]                     DATETIME       NULL,
    [PasswordVerificationToken]               NVARCHAR (128) NULL,
    [PasswordVerificationTokenExpirationDate] DATETIME       NULL,
    CONSTRAINT [PK_dbo.AspNetUsers] PRIMARY KEY CLUSTERED ([Id] ASC)
);
GO
CREATE TABLE [dbo].[AspNetRoles] (
    [Id]   NVARCHAR (128) NOT NULL,
    [Name] NVARCHAR (256) NOT NULL,
    CONSTRAINT [PK_dbo.AspNetRoles] PRIMARY KEY CLUSTERED ([Id] ASC)
);
GO
CREATE TABLE [dbo].[AspNetUserRoles] (
    [UserId] NVARCHAR (128) NOT NULL,
    [RoleId] NVARCHAR (128) NOT NULL,
    CONSTRAINT [PK_dbo.AspNetUserRoles] PRIMARY KEY CLUSTERED ([UserId] ASC, [RoleId] ASC),
    CONSTRAINT [FK_dbo.AspNetUserRoles_dbo.AspNetRoles_RoleId] FOREIGN KEY ([RoleId]) REFERENCES [dbo].[AspNetRoles] ([Id]) ON DELETE CASCADE,
    CONSTRAINT [FK_dbo.AspNetUserRoles_dbo.AspNetUsers_UserId] FOREIGN KEY ([UserId]) REFERENCES [dbo].[AspNetUsers] ([Id]) ON DELETE CASCADE
);
GO
CREATE NONCLUSTERED INDEX [IX_RoleId]
    ON [dbo].[AspNetUserRoles]([RoleId] ASC);
GO
CREATE NONCLUSTERED INDEX [IX_UserId]
    ON [dbo].[AspNetUserRoles]([UserId] ASC);
GO
CREATE TABLE [dbo].[AspNetUserLogins] (
    [UserId]        NVARCHAR (128) NOT NULL,
    [LoginProvider] NVARCHAR (128) NOT NULL,
    [ProviderKey]   NVARCHAR (128) NOT NULL,
    CONSTRAINT [PK_dbo.AspNetUserLogins] PRIMARY KEY CLUSTERED ([UserId] ASC, [LoginProvider] ASC, [ProviderKey] ASC),
    CONSTRAINT [FK_dbo.AspNetUserLogins_dbo.AspNetUsers_UserId] FOREIGN KEY ([UserId]) REFERENCES [dbo].[AspNetUsers] ([Id]) ON DELETE CASCADE
);
GO
CREATE NONCLUSTERED INDEX [IX_UserId]
    ON [dbo].[AspNetUserLogins]([UserId] ASC);
GO

CREATE TABLE [dbo].[AspNetUserClaims] (
    [Id]         INT            IDENTITY (1, 1) NOT NULL,
    [ClaimType]  NVARCHAR (MAX) NULL,
    [ClaimValue] NVARCHAR (MAX) NULL,
    [UserId]    NVARCHAR (128) NOT NULL,
    CONSTRAINT [PK_dbo.AspNetUserClaims] PRIMARY KEY CLUSTERED ([Id] ASC),
    CONSTRAINT [FK_dbo.AspNetUserClaims_dbo.AspNetUsers_User_Id] FOREIGN KEY ([UserId]) REFERENCES [dbo].[AspNetUsers] ([Id]) ON DELETE CASCADE
);
GO
CREATE NONCLUSTERED INDEX [IX_User_Id]
    ON [dbo].[AspNetUserClaims]([UserId] ASC);
GO

INSERT INTO AspNetUsers(Id, UserName, BaId, OfcId, PasswordHash, SecurityStamp, Discriminator,
CreateDate, ConfirmationToken, IsConfirmed, LastPasswordFailureDate, PasswordFailuresSinceLastSuccess,
PasswordChangedDate, PasswordVerificationToken, PasswordVerificationTokenExpirationDate)
SELECT UserProfile.UserId, UserProfile.UserName, UserProfile.BaId, UserProfile.OfcId,
webpages_Membership.Password, webpages_Membership.PasswordSalt, 'User', CreateDate, 
ConfirmationToken, IsConfirmed, LastPasswordFailureDate, PasswordFailuresSinceLastSuccess,
PasswordChangedDate, PasswordVerificationToken, PasswordVerificationTokenExpirationDate
FROM UserProfile
LEFT OUTER JOIN webpages_Membership ON UserProfile.UserId = webpages_Membership.UserId
GO

INSERT INTO AspNetRoles(Id, Name)
SELECT RoleId, RoleName
FROM webpages_Roles
GO

INSERT INTO AspNetUserRoles(UserId, RoleId)
SELECT UserId, RoleId
FROM webpages_UsersInRoles
GO

IF OBJECT_ID('dbo.webpages_OAuthMembership', 'U') IS NOT NULL
    DROP TABLE [dbo].[webpages_OAuthMembership]
GO

IF OBJECT_ID('dbo.webpages_UsersInRoles', 'U') IS NOT NULL
    DROP TABLE [dbo].[webpages_UsersInRoles]
GO
IF OBJECT_ID('dbo.webpages_Roles', 'U') IS NOT NULL
    DROP TABLE [dbo].[webpages_Roles]
GO
IF OBJECT_ID('dbo.UserProfile', 'U') IS NOT NULL
    DROP TABLE [dbo].[UserProfile]
GO
IF OBJECT_ID('dbo.webpages_Membership', 'U') IS NOT NULL
    DROP TABLE [dbo].[webpages_Membership]
GO

--INSERT INTO AspNetUserLogins(UserId, LoginProvider, ProviderKey)
--SELECT UserId, Provider, ProviderUserId
--FROM webpages_OAuthMembership
--GO

I'm not using Social logins hence the commenting out of Insert into AspNetUserLogins (you do need to create the table though since Identity 2.0 is expecting it).

The Identity 2.0 AspNetUsers Table has these fields by default:
[Id]
[Email]
[EmailConfirmed]
[PasswordHash]
[SecurityStamp]
[PhoneNumber]
[PhoneNumberConfirmed]
[TwoFactorEnabled]
[LockoutEndDateUtc]
[LockoutEnabled]
[AccessFailedCount]
[UserName]

I'm still experimenting, use your best judgment as to what you need to migrate from the webpages_Membership table. At this point I can login though.

UPDATE:

In my ApplicationUser I had overridden the UserName to shorten the field. DO NOT do this, it will cause an authentication error. You can control the field length in the migration script. I've removed the override in the OP. For more see User.IsInRole failing.

ApplicationUser:

  public class ApplicationUser : IdentityUser
  { 
    // [StringLength(15)]   // do not override UserName, will cause authentication error.
    // public new string UserName { get; set; }
    public int AcId { get; set; }
    public int LcId { get; set; }
    // public string ConfirmationToken { get; set; }  // Depends on your app if you need to migrate these fields
    // public bool IsConfirmed { get; set; }
    // public string PasswordResetToken { get; set; }
  }
Precious answered 29/4, 2014 at 18:20 Comment(0)

© 2022 - 2024 — McMap. All rights reserved.