PostgreSQL support #2604 | Support Center

[email protected] created 7 years ago

Does the ASP.Net Zero supports PostgreSQL database? I am trying to run the migration against the database and in between its throwing an exception Unhandled MigrationOperation AlterTableOperation in NpgsqlMigrationSqlGenerator

I have added the Npgsql.EntityFramework for migration.

7 Answer(s)

0

cangunaydin created 7 years ago

Hello, I am not the expert but i have tried it also and i couldn't make it. And the problem is Npgsql.EntityFramework does not support TableAnnotations when you do the migration so probably that's why it is giving an error .The problem is when you try to create a migration. For now it is not possible to do code first and use postgresql. As an option you can remove datatable annotations and you can do it from pgadmin by altering the database after the db is created from code first. But it is not a very good method to use in my opinion. If you can find a working method, please let me know and i can try it also. Good luck.
0

ismcagdas created 7 years ago
Support Team

Hi,

Thanks @cangunaydin for your work :),

We haven't tried with PostgreSql but I assume it is possible to use it. But, current migrations are created for MsSql server. I suggest you to remove all migration code, then change your connection string for PostgreSql and regenerate an initial/single migration. You can follow a similar approach explained for MySql in this document <a class="postlink" href="http://aspnetboilerplate.com/Pages/Documents/EF-MySql-Integration">http://aspnetboilerplate.com/Pages/Docu ... ntegration</a>.

This might work for you. Please let us know if it does not and we will try it as well.

Thanks.

cangunaydin created 7 years ago

i have done the following steps.

i have installed 2 nuget packages to the entityframework project with following commands.

Install-Package Npgsql -Version 3.1.0 Install-Package EntityFramework6.Npgsql

i have changed the dbconfig with the following lines.

public class MagicInfoConfigDbConfiguration : DbConfiguration
    {
        public MagicInfoConfigDbConfiguration()
        {
            SetProviderFactory("Npgsql", NpgsqlFactory.Instance);
            SetProviderServices("Npgsql", provider: NpgsqlServices.Instance);
            SetDefaultConnectionFactory(new NpgsqlConnectionFactory());
        }
    }

i removed all migrations and created a new migration with the following command.

dotnet ef migrations add "initialize"

then i run the migrator porject. which gives me an error.

INFO  2017-03-07 14:22:36,410 [1    ] ByteBrick.MagicInfoConfig.Migrator.Log   - Npgsql.PostgresException (0x80004005): 22023: length for type varchar cannot exceed 10485760
   at Npgsql.NpgsqlConnector.DoReadMessage(DataRowLoadingMode dataRowLoadingMode, Boolean isPrependedMessage)
   at Npgsql.NpgsqlConnector.ReadMessageWithPrepended(DataRowLoadingMode dataRowLoadingMode)
   at Npgsql.NpgsqlDataReader.NextResultInternal()
   at Npgsql.NpgsqlDataReader.NextResult()
   at Npgsql.NpgsqlCommand.Execute(CommandBehavior behavior)
   at Npgsql.NpgsqlCommand.ExecuteNonQueryInternal()
   at System.Data.Entity.Infrastructure.Interception.InternalDispatcher`1.Dispatch[TTarget,TInterceptionContext,TResult](TTarget target, Func`3 operation, TInterceptionContext interceptionContext, Action`3 executing, Action`3 executed)
   at System.Data.Entity.Infrastructure.Interception.DbCommandDispatcher.NonQuery(DbCommand command, DbCommandInterceptionContext interceptionContext)
   at System.Data.Entity.Migrations.DbMigrator.ExecuteSql(MigrationStatement migrationStatement, DbConnection connection, DbTransaction transaction, DbInterceptionContext interceptionContext)
   at System.Data.Entity.Migrations.DbMigrator.ExecuteStatementsInternal(IEnumerable`1 migrationStatements, DbConnection connection, DbTransaction transaction, DbInterceptionContext interceptionContext)
   at System.Data.Entity.Migrations.DbMigrator.ExecuteStatementsWithinTransaction(IEnumerable`1 migrationStatements, DbTransaction transaction, DbInterceptionContext interceptionContext)
   at System.Data.Entity.Migrations.DbMigrator.ExecuteStatementsWithinNewTransaction(IEnumerable`1 migrationStatements, DbConnection connection, DbInterceptionContext interceptionContext)
   at System.Data.Entity.Migrations.DbMigrator.ExecuteStatementsInternal(IEnumerable`1 migrationStatements, DbConnection connection, DbInterceptionContext interceptionContext)
   at System.Data.Entity.Migrations.DbMigrator.ExecuteStatementsInternal(IEnumerable`1 migrationStatements, DbConnection connection)
   at System.Data.Entity.Migrations.DbMigrator.ExecuteStatements(IEnumerable`1 migrationStatements, DbTransaction existingTransaction)
   at System.Data.Entity.Migrations.DbMigrator.ExecuteOperations(String migrationId, VersionedModel targetModel, IEnumerable`1 operations, IEnumerable`1 systemOperations, Boolean downgrading, Boolean auto)
   at System.Data.Entity.Migrations.DbMigrator.ApplyMigration(DbMigration migration, DbMigration lastMigration)
   at System.Data.Entity.Migrations.DbMigrator.Upgrade(IEnumerable`1 pendingMigrations, String targetMigrationId, String lastMigrationId)
   at System.Data.Entity.Migrations.DbMigrator.UpdateInternal(String targetMigration)
   at System.Data.Entity.Migrations.DbMigrator.EnsureDatabaseExists(Action mustSucceedToKeepDatabase)
   at System.Data.Entity.Migrations.DbMigrator.Update(String targetMigration)
   at System.Data.Entity.MigrateDatabaseToLatestVersion`2.InitializeDatabase(TContext context)
   at Abp.Zero.EntityFramework.AbpZeroDbMigrator`2.CreateOrMigrate(AbpTenantBase tenant)
   at ByteBrick.MagicInfoConfig.Migrator.MultiTenantMigrateExecuter.Run(Boolean skipConnVerification) in D:\dev\vs\dotnetcore\MagicInfoConfig\Server.Core\src\ByteBrick.MagicInfoConfig.Migrator\MultiTenantMigrateExecuter.cs:line 60
INFO  2017-03-07 14:22:36,411 [1    ] ByteBrick.MagicInfoConfig.Migrator.Log   - Canceled migrations.

0

ismcagdas created 7 years ago
Support Team

Hi,

Probably you need to modify a field's length in generated migration file but this message is not helping about it. We will also try this but it might take a few days. We will let you know when we tried.

Thanks.
0
[email protected] created 7 years ago
Hi Guys,

Thanks for all the reply.

I am now able to run the migration on the PostgreSQL.

There are some changes required and some things which as of now i have commented.

Following is the list Code changes done

TCDbConfiguration: SetProviderServices("Npgsql", Npgsql.NpgsqlServices.Instance); SetProviderFactory("Npgsql", Npgsql.NpgsqlFactory.Instance); SetDefaultConnectionFactory(new Npgsql.NpgsqlConnectionFactory()); SetMigrationSqlGenerator("Npgsql", MyNPGSqlGenerator.Instance);

Added the class public class MyNPGSqlGenerator : NpgsqlMigrationSqlGenerator { public override IEnumerable<MigrationStatement> Generate(IEnumerable<System.Data.Entity.Migrations.Model.MigrationOperation> operations, string providerManifestToken) { var statments = new List<MigrationOperation>(); foreach (var item in operations) { var migration = item as AlterTableOperation; if (migration == null) { statments.Add(item); } } return base.Generate(statments, providerManifestToken); }

public static MyNPGSqlGenerator Instance() { return new MyNPGSqlGenerator(); }

}

changed all the migration code to have dbo.Tablename ex: CreateTable( "dbo.AbpPermissions"

Currently commented the Sql functionality in the migration.
0

ismcagdas created 7 years ago
Support Team

Hi @ictindia,

Thank you very much for your feedback, it is really valuable for other poeple I think.

Thanks again :)
0

cangunaydin created 7 years ago

Hello @ictindia, Can you explain little a bit more about the code. Why do we need custom npgsqlgenerator? And what does the class do? Thank you so much for the feedback.

Have an answer to this question? Log in and write your answer.