Efcore.pg: column "TwoFactorEnabled" cannot be cast automatically to type boolean

[MrOldham101]

When trying to use the Microsoft identity library with npgsql, if you create a brand new project with a brand new database and attempt to update that database for the first time with a generated migration you get the following error message:

Build started...
Build succeeded.
fail: Microsoft.EntityFrameworkCore.Database.Command[20102]
Failed executing DbCommand (3ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
ALTER TABLE "AspNetUsers" ALTER COLUMN "TwoFactorEnabled" TYPE boolean;
ALTER TABLE "AspNetUsers" ALTER COLUMN "TwoFactorEnabled" SET NOT NULL;
ALTER TABLE "AspNetUsers" ALTER COLUMN "TwoFactorEnabled" DROP DEFAULT;
Failed executing DbCommand (3ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
ALTER TABLE "AspNetUsers" ALTER COLUMN "TwoFactorEnabled" TYPE boolean;
ALTER TABLE "AspNetUsers" ALTER COLUMN "TwoFactorEnabled" SET NOT NULL;
ALTER TABLE "AspNetUsers" ALTER COLUMN "TwoFactorEnabled" DROP DEFAULT;
Npgsql.PostgresException (0x80004005): 42804: column "TwoFactorEnabled" cannot be cast automatically to type boolean
at Npgsql.NpgsqlConnector.<>c__DisplayClass160_0.<g__ReadMessageLong|0>d.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at Npgsql.NpgsqlConnector.<>c__DisplayClass160_0.<g__ReadMessageLong|0>d.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at Npgsql.NpgsqlDataReader.NextResult(Boolean async, Boolean isConsuming)
at Npgsql.NpgsqlDataReader.NextResult()
at Npgsql.NpgsqlCommand.ExecuteReaderAsync(CommandBehavior behavior, Boolean async, CancellationToken cancellationToken)
at Npgsql.NpgsqlCommand.ExecuteNonQuery(Boolean async, CancellationToken cancellationToken)
at Npgsql.NpgsqlCommand.ExecuteNonQuery()
at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteNonQuery(RelationalCommandParameterObject parameterObject)
at Microsoft.EntityFrameworkCore.Migrations.MigrationCommand.ExecuteNonQuery(IRelationalConnection connection, IReadOnlyDictionary2 parameterValues) at Microsoft.EntityFrameworkCore.Migrations.Internal.MigrationCommandExecutor.ExecuteNonQuery(IEnumerable1 migrationCommands, IRelationalConnection connection)
at Microsoft.EntityFrameworkCore.Migrations.Internal.Migrator.Migrate(String targetMigration)
at Microsoft.EntityFrameworkCore.Design.Internal.MigrationsOperations.UpdateDatabase(String targetMigration, String contextType)
at Microsoft.EntityFrameworkCore.Design.OperationExecutor.UpdateDatabaseImpl(String targetMigration, String contextType)
at Microsoft.EntityFrameworkCore.Design.OperationExecutor.UpdateDatabase.<>c__DisplayClass0_0.<.ctor>b__0()
at Microsoft.EntityFrameworkCore.Design.OperationExecutor.OperationBase.Execute(Action action)
Exception data:
Severity: ERROR
SqlState: 42804
MessageText: column "TwoFactorEnabled" cannot be cast automatically to type boolean
Hint: You might need to specify "USING "TwoFactorEnabled"::boolean".
File: tablecmds.c
Line: 10394
Routine: ATPrepAlterColumnType
42804: column "TwoFactorEnabled" cannot be cast automatically to type boolean

At the first look, this seems to be an issue between ASP.NET Core and PostgreSql. Can anyone else replicate this?

[roji]

When you use the ASP.NET templates, you get a project with migrations already generated for SQL Server or Sqlite; these migrations are incompatible in various ways with PostgreSQL. The solution is simply to delete and regenerate the migrations in Data/Migrations right after you create the project. Hopefully at some point in the future, ASP.NET will include templates for PostgreSQL as well.

[MrOldham101]

Thank you. Ive only just realised that when first creating the project I accidentally ran database update instead of create migration. I thought nothing of it (as it failed) and then created the migration as normal, deleted the original one and reran the database update. I now realise that when I ran the database update the first time it managed to add the tables but not correctly. When I then came to apply my Npgsql migration it caused this error message.

[Danieliessen]

Still does not work, i recreated the table, it says i should use USING two_factor_enabled::boolean, but where should i put it, andh by the way i ve now deleted everything according to twofactoreanbled, but the update database statement still gets it from somewhere..