Add Identity Scaffolding using Postgresql DbContext in ASP.NET Core 7.0

Question

I have converted my month old project to use Postgresql rather than Sql Server, I tested it and it correctly applies identity migrations and creates the tables. When logged in with Google OAuth it wrote the new record to the database for the google account. Next I wanted to alter the UI's for the login and other identity pages, so I went to Add Scaffolding for the identity pages, but when the dialog pops up for which pages you want to override, it shows only a disabled "SQL Server" as the database provider. I've seen other posts online where they have the appropriate provider like MySql lite listed there. I have removed the package for SQL Server, and have added:

Npgsql.EntityFrameworkCore.PostgreSQL
Npgsql.EntityFrameworkCore.PostgreSQL.Design

Program.cs contains

var connectionString = builder.Configuration.GetConnectionString("DefaultConnection") 
     ?? throw new InvalidOperationException("Connection string 'DefaultConnection' not found.");
builder.Services.AddDbContextFactory<ApplicationDbContext>(options =>
            options.UseNpgsql(connectionString));

builder.Services.AddDatabaseDeveloperPageExceptionFilter();

// then lower
app.UseRouting();

app.UseAuthentication();
app.UseAuthorization();

ApplicationDbContext.cs Used pregenerated and then just added the create parts and OnConfiguring

    public class ApplicationDbContext : IdentityDbContext
    {
        private readonly IConfiguration _configuration;
        public ApplicationDbContext(DbContextOptions<ApplicationDbContext> options,
            [FromServices] IConfiguration configuration)
            : base(options)
        {
            _configuration = configuration;
            try
            {
                // Create the database if it doesn't exist
                var databaseCreator = Database.GetService<IDatabaseCreator>() as RelationalDatabaseCreator;
                if (databaseCreator != null)
                {
                    if (!databaseCreator.CanConnect()) { databaseCreator.Create(); }
                    if (!databaseCreator.HasTables()) { databaseCreator.CreateTables(); }
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
        }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {

            // from environment vars
            //var dbHost = Environment.GetEnvironmentVariable("DB_HOST");
            //var dbName = Environment.GetEnvironmentVariable("DB_NAME");
            //var dbPassword = Environment.GetEnvironmentVariable("DB_SA_PASSWORD");
            //var dbUsername = Environment.GetEnvironmentVariable("DB_USERNAME");
            //var connectionString = $"Username={dbUsername};Password={dbPassword};Server={dbHost};Database={dbName};Integrated Security=true;Pooling=true;";


            //from configuration
            var connectionString = _configuration.GetConnectionString("DefaultConnection")
              ?? throw new InvalidOperationException("Connection string 'DefaultConnection' not found.");
            optionsBuilder.UseNpgsql(connectionString);
        }
    }

appsetings.json

"ConnectionStrings": {
    "DefaultConnection": "Provider=PostgreSQL OLE DB;Driver={PostgreSQL};User ID=postgres;Password=*******;Host=**********;Database=*********"
  }

I added Provider and Driver to a working connection string trying to fix my problem without any affect.

And yet when I add scaffolding the dialog looks like this:

And when I click Add I get:

I have tried clearing Package Manager storage/cache and the problem persists.

I'm not sure how to get the project to add the scaffolding after the project has been converted to another database provider.

I have searched my project for any reference to SQL Server and have none, which is probably why it's grayed out, yet does not populate with my provider.

Visual Studio Community 2022 Version 17.5.5

score 1 · Answer 1 · answered Jul 01 '23 at 18:06

I created a throw away project and created a local only git repo then did a diff to see the changes after adding the scaffolding there. I could see that it changed the version of some of my relevant packages in the project:

After that I copied the new pages and cs code (co-located) to the appropriate folder in my active project. I ran it and was able to see changes to those new pages rendered.

Here are some of those changes:

I don't have the true answer to the question, but at least it's a work around. It may be that starting with upgrading the package versions could have solved it.

If you do this, make sure you change the namespaces of all the files you copy over, probably with a Find and Replace All. I found out I had my "throw away" namespace a few days later after I just copied the files into my project. — Steven Edison, Jul 13 '23 at 13:00

score 1 · Answer 2 · answered Jul 26 '23 at 17:56

I experienced the same issue and used dotnet CLI to scaffold. It is an easy procedure. You can read more about it here:

https://learn.microsoft.com/en-us/aspnet/core/security/authentication/scaffold-identity?view=aspnetcore-7.0&tabs=netcore-cli#scaffold-identity-into-a-razor-project-with-authorization

However, it still requires you to have Microsoft.EntityFrameworkCore.SqlServer in your dependencies list, but the program works with PostgreSQL. Otherwise, the CLI just does not allow scaffolding.

Add Identity Scaffolding using Postgresql DbContext in ASP.NET Core 7.0

2 Answers2