CODEWITHSUNDEEP
asp.netasp.net-mvcentity-frameworkasp.net-mvc-5asp.net-identity
Alp
Alp

Reputation: 624

Invalid object name 'dbo.AspNetUsers' in Asp.NET MVC 5 Entity Framework

If you have an existing database, and you want to include ASP.NET Identity tables to it, you can face this error. You may not know how to integrate [AspNetRoles], [AspNetUserClaims], [AspNetUsers], [AspNetUserLogins] tables into your existing database when you start. Although there are a lot of resources about this topic, this answer tries to be short and to the point. You may want to use the Database-First approach in Entity Framework together with the ASP.NET Identity feature of ASP.NET MVC. This is a very short tutorial for dummies. I am sorry if my English is poor.

Upvotes: 15

Views: 49642

Answers (10)

Yutao Huang
Yutao Huang

Reputation: 1571

I ran into the same issue today. The following .NET CLI commands worked for me. I needed to run them from my project folder:

dotnet tool install dotnet-ef --version 8.0.* --global
dotnet ef migrations add CreateIdentitySchema
dotnet ef database update

My understanding is that it made sure to create all the necessary tables with the right schemas according to your code.

Upvotes: 1

MorbidCamel
MorbidCamel

Reputation: 41

Similar to above you could do this in ConfigureServices

services.Configure<ApplicationDbContext>(o =>
{
  // Make sure the identity database is created
  o.Database.Migrate();
});
    //     Applies any pending migrations for the context to the database. Will create the
    //     database if it does not already exist.
    //     Note that this API is mutually exclusive with DbContext.Database.EnsureCreated().
    //     EnsureCreated does not use migrations to create the database and therefore the
    //     database that is created cannot be later updated using migrations.

Upvotes: 1

robino16
robino16

Reputation: 315

Here are the steps I took to add AspNetCore.Identity.EntityFramework to an existing project with an existing SQL database. Same as OP, I was missing the required database tables in order to use the framework. I'm running ASP.NET Core v6.0.5.

  1. With Visual Studio, create a new temporary project using the ASP.NET Core Web App (Model-View-Controller) template. Choose authentication type Individual Accounts.
  2. Open the Package Management Console (you can find it under View -> Other Windows -> Package Management Console).
  3. In the Package Management Console, you can initialize the missing database tables by typing Update-Database -Connection 'CON_STR' where CON_STR is your database connection string. The Package Management Console will create the tables. It will also print the SQL queries it used to create them.

After the SQL tables had been created, I had no further issues. Here is the code from my Program.cs file:

builder.Services.AddDbContext<IdentityDbContext>(options => options.UseSqlServer(CON_STR));
builder.Services.AddIdentity<IdentityUser, IdentityRole>().AddEntityFrameworkStores<IdentityDbContext>().AddDefaultTokenProviders();

Upvotes: 1

Sarfaraz Ansari
Sarfaraz Ansari

Reputation: 129

Go to package manager console,

copy paste this line --> Add-Migration Init --> press enter

copy paste this line --> Update-Database --> press enter

Upvotes: 0

AminM
AminM

Reputation: 1814

I go through same issue.
sometime user create customize class for membership hence asp.net dont create AspNetUsers table
example : 

public class Member : IdentityUser
{
    Public int MemberID { get; set; }
    public string Name { get; set; }
    public string Family { get; set; }
    public string Password { get; set; }

}

in this scenario ASP.NET create Member table and when user want to authenticate,ASP.NET search for AspNetUsers and did not find this table. this problem solved with add TableAttribute in begin of customized class 

[Table("AspNetUsers")]
public class Member : IdentityUser
{
    Public int MemberID { get; set; }
    public string Name { get; set; }
    public string Family { get; set; }
    public string Password { get; set; }

}

Upvotes: 4

TicTac
TicTac

Reputation: 1

the fastest way is to recreate a new project and select the authentication to individual. And then use the old files to instead the new ones.

Upvotes: -4

Adam Sierosławski
Adam Sierosławski

Reputation: 106

Remove folder /.vs/[solution_name]/DesignTimeBuild. Next from command line: dotnet ef database update --context [your_db_context_name] It works for me.

Upvotes: 0

Nilay Mehta
Nilay Mehta

Reputation: 1907

I have faced same problem, Here I was forget to call EnsureCreated(). After calling this method it will create all tables required by Identity.

Startup.cs

public void Configure(IApplicationBuilder app, IHostingEnvironment env, IServiceProvider serviceProvider)
{
        ...

        // Make sure we have the database
        serviceProvider.GetService<ApplicationDbContext>().Database.EnsureCreated();
}

Upvotes: 7

Kuleris
Kuleris

Reputation: 101

In Visual Studio go to "Tools -> NuGet Package Manager -> Package Manager Console" and in console window (inside Visual Studio) execute "Update-Database" command

Upvotes: 5

Alp
Alp

Reputation: 624

Here is the shortest integration of Asp.NET Identity tables to your existing database.

1) Open new project or your existing project in Visual Studio (2015 or 2013). Open your Server Explorer and open your DefaultConnection.Find your Identity tables. (In WebConfig file, localDB connectionstring should be active. And your other existing Database's connection string should not be active.) Double Click your [AspNetRoles], [AspNetUserClaims], [AspNetUsers], [AspNetUserLogins] tables. And copy all of their SQL codes.

2) Open your existing database in your SQL Server Management Studio, right click your database and click New Query past here what you copied in 1st part. You will past something like that: 

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 UNIQUE NONCLUSTERED INDEX [RoleNameIndex]
ON [dbo].[AspNetRoles]([Name] ASC);



CREATE TABLE [dbo].[AspNetUsers] (
[Id]                   NVARCHAR (128) NOT NULL,
[Email]                NVARCHAR (256) NULL,
[EmailConfirmed]       BIT            NOT NULL,
[PasswordHash]         NVARCHAR (MAX) NULL,
[SecurityStamp]        NVARCHAR (MAX) NULL,
[PhoneNumber]          NVARCHAR (MAX) NULL,
[PhoneNumberConfirmed] BIT            NOT NULL,
[TwoFactorEnabled]     BIT            NOT NULL,
[LockoutEndDateUtc]    DATETIME       NULL,
[LockoutEnabled]       BIT            NOT NULL,
[AccessFailedCount]    INT            NOT NULL,
[UserName]             NVARCHAR (256) NOT NULL,
CONSTRAINT [PK_dbo.AspNetUsers] PRIMARY KEY CLUSTERED ([Id] ASC)
);


GO
CREATE UNIQUE NONCLUSTERED INDEX [UserNameIndex]
ON [dbo].[AspNetUsers]([UserName] ASC);


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_UserId]
ON [dbo].[AspNetUserRoles]([UserId] ASC);


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



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 CLUSTERED ([LoginProvider] ASC, [ProviderKey] ASC, [UserId] 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);



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


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

If you forget the lines which starting with GO, you will exactly see the error which is in Title of this question. Run this query and wait for succesfully creation of tables. Your existing database is now ready for Identity features of Asp.NET MVC 5.

3) Open WebConfig in your Visual Studio. We will change connectionstring here. Write this:

 <add name="DefaultConnection" connectionString="Data Source=YOUR_SERVER_NAME;Initial Catalog=YOUR_DATABASE_NAME;Persist Security Info=True;User ID=YOUR_USER_ID;Password=YOUR_PASSWORD.;MultipleActiveResultSets=True;Application Name=EntityFramework"  providerName="System.Data.SqlClient"/>

Instead of localDB connection string. Which is this:

 <add name="DefaultConnection" connectionString="Data Source=(LocalDb)\MSSQLLocalDB;AttachDbFilename=|DataDirectory|\aspnet-....mdf;Initial Catalog=aspnet-...;Integrated Security=True" providerName="System.Data.SqlClient" />

That's all you need to do. Run your project and Register. You can see your new user data in AspNetUsers table, in your existing database.

Upvotes: 15

Related Questions