Sunday, October 31, 2021

Create Identity in a new Schema in Visual Studio 2022 and .NET 6 / ASP.NET MVC 6

Identity tables are created in the [dbo] schema by default, when using SQL Server. We can create them in a different schema if we want to, in a few simple steps.

(This does not migrate existing data, but rather, it creates new tables in the specified schema.)

The six steps required are given below.


Make a complete backup of your database before doing this! You must ensure you can recover everything from a backup if things don't work out as expected. 

1. Set the connection string in appsettings.json. For example:

"ConnectionStrings": {
    "DefaultConnection": "Server=YOUR-SERVERNAME;Database=YOUR-DBNAME;Trusted_Connection=True;MultipleActiveResultSets=true"

You can specify a local or network server, or it could be an Azure SQL database.

2. Under the Properties folder in your web project:

Open the files serviceDependencies.json and serviceDependencies.local.json, and comment out the code in both of them:

//  "dependencies": {
//    "mssql1": {
//      "type": "mssql",
//      "connectionId": "ConnectionStrings:DefaultConnection"
//    }
//  }

3. In Program.cs, create 2 new classes ApplicationUser and ApplicationDbContext, placing them at the end of the file:

(The new classes derive from IdentityUser and IdentityDbContext, respectively.)

#region App-specific code added for placing identity tables into a new schema
/// <summary>
/// Extra fields can be added to the user table here
/// </summary>
public class ApplicationUser : IdentityUser
    //public string CustomTag { get; set; }

public class ApplicationDbContext : IdentityDbContext<ApplicationUser>
    public ApplicationDbContext(DbContextOptions<ApplicationDbContext> options)
        : base(options)

    /// <summary>
    /// Create ident tables in a different schema
    /// </summary>
    protected override void OnModelCreating(ModelBuilder builder)

Your new schema will be whatever you specify as YOURCUSTOMSCHEMA at the end of the code above.

4. Also in Program.cs, change references from IdentityUser and IdentityDbContext to ApplicationUser and ApplicationDbContext respectively - for example:

builder.Services.AddDbContext<ApplicationDbContext>(options =>
builder.Services.AddDefaultIdentity<ApplicationUser>(options => options.SignIn.RequireConfirmedAccount = true)

5. In Views\Shared\_LoginPartial.cshtml, change references from IdentityUser to ApplicationUser:

@inject SignInManager<ApplicationUser> SignInManager
@inject UserManager<ApplicationUser> UserManager

6. Create and run migrations, to generate the new schema and identity tables:

- Click on Tools > NuGet Package Manager > Package Manager Console (PMC), to get a PMC window

- Create the migration by entering the following command against the PM prompt:
PM> add-migration IdentCreation -context ApplicationDbContext

You can specify any name for the migration, IdentCreation in the above example.

The migration file will be generated, and automatically opened, by Visual Studio 2022.

IMPORTANT! Check the resulting code in it to make sure all looks good.

IMPORTANT! Up till now, nothing has changed in your database. The next command updates the database!

AND ... MOST IMPORTANT! Make a complete backup of your database before running the next command!

- Run the migration to update your database:
PM> update-database -context ApplicationDbContext

This will create the new identity tables in your database, and will also create the schema itself if it doesn't already exist.

That's it - done! On running the program, when you add a new user, they'll be added into the tables in the new schema.

No comments:

Post a Comment