CODEWITHSUNDEEP
c#asp.net-mvcentity-frameworkentity-framework-migrations
user1206480
user1206480

Reputation: 1858

How do I create the asp.net Identity tables manually with migrations?

I would like the asp.net Identity tables to exist in my database, and as well, have the corresponding respective code-first models generated before I actually run the mvc app and create a user, so that I may code some logic against these classes before hand. Is there a way that I can create these tables/classes with migrations manually?

Upvotes: 8

Views: 9537

Answers (3)

Keryn Wong
Keryn Wong

Reputation: 9

I followed Dylan Corriveau's answer and I have some column fields missing when creating(HttpPost) the new user data for the "AspNetUsers" table.

The missing fields are here. You can add these fields to your table if you're facing the same problem.

[NormalizedUserName]    NVARCHAR (MAX) NULL,
[Email]         NVARCHAR (MAX) NULL,
[NormalizedEmail]   NVARCHAR (MAX) NULL,
[EmailConfirmed]    bit NULL,
[ConcurrencyStamp]  NVARCHAR (MAX) NULL,
[PhoneNumber]       NVARCHAR (MAX) NULL,
[PhoneNumberConfirmed]  bit NULL,
[TwoFactorEnabled]  bit NULL,
[LockoutEnd]        NVARCHAR (MAX) NULL,
[LockoutEnabled]    bit NULL,
[AccessFailedCount] int NULL

and the AspNetRoles is missing

[NormalizedName]    NVARCHAR (MAX) NULL,
[ConcurrencyStamp]  NVARCHAR (MAX) NULL

Upvotes: 0

Mocha
Mocha

Reputation: 2253

Dylan Corriveau's answer works. However, if you are trying to login with these tables.. the userManager.FindAsync will cause a 500 error (Invalid UserId). the User_Id in AspNetUserClaims should be UserId.

Upvotes: 2

Dylan Corriveau
Dylan Corriveau

Reputation: 2557

If you want to add it manually in your database, you'll have to write it in a Structured Query Language ;) 

CREATE TABLE [dbo].[__MigrationHistory] (
    [MigrationId]    NVARCHAR (150)  NOT NULL,
    [ContextKey]     NVARCHAR (300)  NOT NULL,
    [Model]          VARBINARY (MAX) NOT NULL,
    [ProductVersion] NVARCHAR (32)   NOT NULL
);

GO
CREATE TABLE [dbo].[AspNetRoles] (
    [Id]   NVARCHAR (128) NOT NULL,
    [Name] NVARCHAR (MAX) NOT NULL
);

GO
CREATE TABLE [dbo].[AspNetUserClaims] (
    [Id]         INT            IDENTITY (1, 1) NOT NULL,
    [ClaimType]  NVARCHAR (MAX) NULL,
    [ClaimValue] NVARCHAR (MAX) NULL,
    [User_Id]    NVARCHAR (128) NOT NULL
);

GO
CREATE TABLE [dbo].[AspNetUserLogins] (
    [UserId]        NVARCHAR (128) NOT NULL,
    [LoginProvider] NVARCHAR (128) NOT NULL,
    [ProviderKey]   NVARCHAR (128) NOT NULL
);

GO
CREATE TABLE [dbo].[AspNetUserRoles] (
    [UserId] NVARCHAR (128) NOT NULL,
    [RoleId] NVARCHAR (128) NOT NULL
);

GO
CREATE TABLE [dbo].[AspNetUsers] (
    [Id]            NVARCHAR (128) NOT NULL,
    [UserName]      NVARCHAR (MAX) NULL,
    [PasswordHash]  NVARCHAR (MAX) NULL,
    [SecurityStamp] NVARCHAR (MAX) NULL,
    [Discriminator] NVARCHAR (128) NOT NULL
);

GO

ALTER TABLE [dbo].[__MigrationHistory]
    ADD CONSTRAINT [PK_dbo.__MigrationHistory] PRIMARY KEY CLUSTERED ([MigrationId] ASC, [ContextKey] ASC);

GO
ALTER TABLE [dbo].[AspNetRoles]
    ADD CONSTRAINT [PK_dbo.AspNetRoles] PRIMARY KEY CLUSTERED ([Id] ASC);

GO
ALTER TABLE [dbo].[AspNetUserClaims]
    ADD CONSTRAINT [PK_dbo.AspNetUserClaims] PRIMARY KEY CLUSTERED ([Id] ASC);

GO
ALTER TABLE [dbo].[AspNetUserLogins]
    ADD CONSTRAINT [PK_dbo.AspNetUserLogins] PRIMARY KEY CLUSTERED ([UserId] ASC, [LoginProvider] ASC, [ProviderKey] ASC);

GO
ALTER TABLE [dbo].[AspNetUserRoles]
    ADD CONSTRAINT [PK_dbo.AspNetUserRoles] PRIMARY KEY CLUSTERED ([UserId] ASC, [RoleId] ASC);

GO
ALTER TABLE [dbo].[AspNetUsers]
    ADD CONSTRAINT [PK_dbo.AspNetUsers] PRIMARY KEY CLUSTERED ([Id] ASC);

GO
CREATE NONCLUSTERED INDEX [IX_User_Id]
    ON [dbo].[AspNetUserClaims]([User_Id] ASC);

GO
CREATE NONCLUSTERED INDEX [IX_UserId]
    ON [dbo].[AspNetUserLogins]([UserId] ASC);

GO
CREATE NONCLUSTERED INDEX [IX_RoleId]
    ON [dbo].[AspNetUserRoles]([RoleId] ASC);

GO
CREATE NONCLUSTERED INDEX [IX_UserId]
    ON [dbo].[AspNetUserRoles]([UserId] ASC);

GO
ALTER TABLE [dbo].[AspNetUserClaims]
    ADD CONSTRAINT [FK_dbo.AspNetUserClaims_dbo.AspNetUsers_User_Id] FOREIGN KEY ([User_Id]) REFERENCES [dbo].[AspNetUsers] ([Id]) ON DELETE CASCADE;

GO
ALTER TABLE [dbo].[AspNetUserLogins]
    ADD CONSTRAINT [FK_dbo.AspNetUserLogins_dbo.AspNetUsers_UserId] FOREIGN KEY ([UserId]) REFERENCES [dbo].[AspNetUsers] ([Id]) ON DELETE CASCADE;

GO
ALTER TABLE [dbo].[AspNetUserRoles]
    ADD CONSTRAINT [FK_dbo.AspNetUserRoles_dbo.AspNetRoles_RoleId] FOREIGN KEY ([RoleId]) REFERENCES [dbo].[AspNetRoles] ([Id]) ON DELETE CASCADE;

GO
ALTER TABLE [dbo].[AspNetUserRoles]
    ADD CONSTRAINT [FK_dbo.AspNetUserRoles_dbo.AspNetUsers_UserId] FOREIGN KEY ([UserId]) REFERENCES [dbo].[AspNetUsers] ([Id]) ON DELETE CASCADE;

GO

That code just pasted into your database editor should be able to create all the default tables for Identity.

That being said, if you don't mind me asking, what kind of logic are you trying to add in?

Upvotes: 11

Related Questions