5

I have a web project that was setup using SQL Server, and that now has to be migrated to PostgreSQL. I'm using Entity Framework version 6.0 with the latest version of Microsoft.AspNet.Identity to manage user credentials. I'm using VS2015 and .NET Framework 452.

The project works fine with the PostgreSQL server for everything except AspNet.Identity. When I try to register a new user or do a login I get the same error message described in this question. Same error, same line but the question is 2 years old and the solution given doesn't work for me (I have tried Add-Migration and Update-Database multiple times).

Everything else works, I have checked and my postgreSQL database does NOT contain any tables related to AspNet.Identity even though they were created automatically when the project was using SQL Server. Other model related tables are present and working.

Thank you.

Improve this question
4
  • Please write a question title with a clear problem statement, not the tools that are related to the problem/question Commented May 26, 2017 at 12:24
  • Do you have a single context inheriting from IdentityDbContext or separate contexts? Try creating a master script of all objects to see what it produces: update-database -Script -SourceMigration: $InitialDatabase. Commented May 26, 2017 at 16:05
  • I have separate contexts, one for identity and the other for everything else. Both contexts use the same connection string, I will try what you say. Thanks @Steve, title changed sorry. Commented May 27, 2017 at 12:07
  • You might find this helpful youtu.be/zNSbYggVX9k Commented Jun 4, 2020 at 11:51

2 Answers 2

Reset to default
6

I was unable to make Microsoft's AspNet.Identity to work with postgresql, it seems to be designed to work with SQL Server and doesn't acknowledge the presence of a different data context configuration no matter what you do.

I have everything working now with the aid of an external library so I will provide an answer to my question in case someone else runs into this problem.

I followed the instructions present in the following project (credit to vincechan):

Step 2 was a bit problematic. After importing the project into my solution I had to resolve reference issues in it. In the Package Manager Console you can Install the missing packages and/or update the ones that are outdated. My final configuration required using:

  • Npgsql 3.2.2 on my main project (using EntityFramework6.Npgsql)
  • Npgsql 2.2.7 on the downloaded project (using Npgsql.EntityFramework)

Once you have everything ready, add a new migration (Add-Migration <migration-name) and update your database (Update-Database).

Now execute the SQL script that creates Identity related tables, they will not be created automatically like the do with SQL Server. The script is included in the project (file PostgreSQLIdentity.sql).

Everything should work now. Forgive the poor formatting of this answer.

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

I say. This was an excellent solution. Only problem was figuring out how to run it without migration. I found that issuing the command ** Remove-Migration ** in the project Nuget console window, or ** dotnet ef migrations remove ** in the -Net Developer DOS command window helped a lot.
Btw. Here is a sample connection string: '<add name="DefaultConnection" connectionString="User ID=[YouUserName];Password=[YourPassword];Host=[databaseIpAddress];Port=5432;Database=[TheDataBaseName];Pooling=true; SearchPath=public;" providerName="Npgsql" />' Mark the SearchPath part, where you can specify which schema to use
3

If your PostgreSQL database doesn't contain any tables related to ASP.NET Identity, then it most likely means that migrations are not enabled for ASP.NET Identity context class. In my case, I'm using VS scaffolding and I mean this class:

public class ApplicationDbContext : IdentityDbContext<ApplicationUser>

So, the first step is to run Enable-Migrations in Package Manager Console without parameters. If you have a clean database then it will work exactly how it worked in the SO topic mentioned earlier. So if you want a simple solution then destroy your PostgreSQL database completely, create it again and simply run Enable-Migrations.

If you don't want to destroy your PostgreSQL database or/and lose any migrations done before then just enable migrations only for ASP.NET Identity context in a separate directory. There already was a SO answer explaining how to do this.

Enable migrations in some directory (e.g. MigrationsIdentity) for ApplicationDbContext:

Enable-Migrations -ContextTypeName MyProject.Models.ApplicationDbContext -MigrationsDirectory MigrationsIdentity

Add initial migration for this context:

Add-Migration IdentityInitial -ConfigurationTypeName MyProject.MigrationsIdentity.Configuration

Apply this migration:

Update-Database -ConfigurationTypeName MyProject.MigrationsIdentity.Configuration

After these steps there will be ASP.NET Identity tables in your PostgreSQL database generated automatically by Code First, you don't need to run anything manually with SQL-scripts.

In the comment you've mentioned that you have separate contexts. However there is a disadvantage of multiple contexts approach: you won't use them together easily. Also you will have to specify explicitly the context with -ConfigurationTypeName flag every time you migrate your database. I would use single context as was discussed already, but it depends on the requirements of your task.

Versions:

  • EntityFramework 6

  • Microsoft.AspNet.Identity.Core 2.1

  • Npgsql 3.1.10.0

  • EntityFramework6.Npgsql 3.1.1.0

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.