4

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.

vicpermir
  • 3,544
  • 3
  • 22
  • 34
  • Please write a question title with a **clear problem statement**, not the tools that are related to the problem/question – Camilo Terevinto May 26 '17 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`. – Steve Greene May 26 '17 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. – vicpermir May 27 '17 at 12:07
  • You might find this helpful https://youtu.be/zNSbYggVX9k – navule Jun 04 '20 at 11:51

2 Answers2

5

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.

vicpermir
  • 3,544
  • 3
  • 22
  • 34
  • 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. – netfed Aug 09 '18 at 22:32
  • Btw. Here is a sample connection string: '' Mark the SearchPath part, where you can specify which schema to use – netfed Aug 09 '18 at 22:49
2

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

Tsagana Nokhaeva
  • 630
  • 1
  • 6
  • 25