Where do you import from NegoziazioneEventi ?

[borisdj]

The type or namespace name 'NegoziazioneEventi' could not be found (are you missing a using directive or an assembly reference?)

[darionato]

Yes, sorry. There were few references of a project. Try to pull now and let me know!

[borisdj]

Ok, that solved this problem, but now there is another, actually a few of them, regarding Join method, and one for

'Error 1 No overload for method 'Join' takes 1 arguments' 278: writer.Write(createIndexOperation.Columns.Join(Quote));

Error 2 No overload for method 'Join' takes 0 arguments 296: writer.Write(addPrimaryKeyOperation.Columns.Select(Quote).Join());

Error 10 'System.Collections.Generic.ICollection' does not contain a definition for 'Each' and no extension method 'Each' accepting a first argument of type 'System.Collections.Generic.ICollection' could be found (are you missing a using directive or an assembly reference?) D:\MyProjects\MvcMovie\MvcMovie\Migrations\PostgreSqlMigrationSqlGenerator .cs 446: createTableOperation.Columns.Each(

I am using, VS 2012, .Net 4.5, MVC4 project.

[borisdj]

[darionato]

Try to pull now. There was a missing class...

[borisdj]

Thanks man... Build works now but am still having some problem with executing migration: ' Input string was not in a correct format.' Will try to see where's the issue and resolve it. If not I might ask for help and send you test project. Thanks in advance.

BTW EF Code First Migration originally works only for MsSQL, right ? So you made your own generator to make it work on PostgreSQL ?

[darionato]

To fix your error you must put the maxlength attribute on each string properties of your models

ex.

[MaxLength(50)] public string Name { get; set; }

Yes, Entity Framework support Ms SQL Server and Ms SQL CE.

With this generator you can use migration with postgresql.

Remenber that you have manually create the database and inside a schema called "dbo"

[darionato]

in your web.config you have to change your connection string with one such this:

[borisdj]

It solved the issue. My commendations for your project. I have tested it for several case scenarios and all works perfectly so far. I would suggest adding remark to Readme: '[MaxLength(n)] attribute is required on each string properties of models'. Because string if often used and that attribute is not mandatory with MsSQL.

Just one more thing. We are using 'Automatically Upgrading on Application Startup'. Last Paragraph on: http://msdn.microsoft.com/en-us/data/jj591621.aspx

For that to work there is just one line of code: 'Database.SetInitializer(new MigrateDatabaseToLatestVersion<TestDbContext, Migrations.Configuration>());' And it works fine with MsSQL but when using this generator with PostgreSQL it gives error: 'ERROR: 42703: column ALIAS2.CreatedOn does not exist'. I made simple Test project with this problem(dropbox link) with stack trace of error. Hope you can give some light to it. https://dl.dropbox.com/u/70690624/TestPgMigration.zip

[darionato]

You don't need to manually migrate to the last version. EF should do it automatically the first time you need the database.

[borisdj]

I know, but wanted to have both option. Very interesting, I just run this test project on another computer and it runs fine. So it actually works. This error obviously has something to do with only my computer.

Anyway keep up the good work. I think it could be good idea to make changes so that MaxLength wouldn't be mandatory, and when it is not present to use default, meaning max as you made it MaxLength(0) which will be turned into postgres text filed. And second thing is scheme 'dbo' is now hard coded into generator, but it would be much flexible if 'dbo' would be default when not specified, but to allow custom schema, specifying it like this [Table("MyTable", Schema = "mysh")] I'll try to do it my self and will push changes to you, unless you do it first. Greetings...

[borisdj]

Just to add some info. First it is easy to remove hardcoded schema name 'dbo'. In PostgreSqlMigrationSqlGenerator add string _schema property and change constructor to: public PostgreSqlMigrationSqlGenerator(string schema = "dbo") { _schema = schema; ... so it still have 'dbo' as default. Finally change all references of 'dbo' to _schema in this class. Tables can belong to different schema [Table("SomeTable", Schema = "someDbo")] but _MigrationHistory table is hardcoded to 'dbo' schema even in EF5. But they said that it will be changed in EF6 that is now in beta, so this would be fix for future.

Another thing worth mentioning is that we had some problem with Npgsql provider. Error 'ALIAS2.CreatedOn' mentioned on previous posts only on one computer. Actual problem was that Npgsql reference was loaded before EF, and the solution was to add Npgsql and Mono Security dll to GAC - Global Assembly Cache via gacutil: http://msdn.microsoft.com/en-us/library/ex0ss12c.aspx

And last thing, there seems to be one more bug. I can't put MaxLength to Max. It looks like the EF requires [MaxLength()] with no attributes and this generator requires [MaxLength(0)] with 0. Anyway [MaxLength()] gives following error: -------------------------------------------------------------- 'System.FormatException: Input string was not in a correct format. at System.Number.StringToNumber(String str, NumberStyles options, NumberBuffer& number, NumberFormatInfo info, Boolean parseDecimal) at System.Number.ParseInt32(String s, NumberStyles style, NumberFormatInfo info) at System.Convert.ToInt32(String value) at System.Data.Entity.Migrations.Infrastructure.EdmModelDiffer.BuildColumnModel(XElement property, String entitySetName, ModelMetadata modelMetadata)' -------------------------------------------------------------- And [MaxLength(0)] gives: -------------------------------------------------------------- 'The MaxLengthAttribute on property 'Desc' on type 'Db.Test is not valid. The Length value must be greater than zero. Use MaxLength() without parameters to indicate that the string or array can have the maximum allowable length.' --------------------------------------------------------------

I looked into the code generator, this part case PrimitiveTypeKind.String: if (column.MaxLength != null && column.MaxLength > 0) return "varchar"; else return "text"; but could not figured what was the problem, so do you have idea how to fix this? Thanks again....

[darionato]

You have to use the max length with the length as parameter: ex. [MaxLength(20)]. You can't leave it empty...

[borisdj]

I know that but this is when I want to get column type: in MsSQL 'nvarchar(MAX)' and in PostgreSQL 'text' This works with standard migration generator to MsSQL by writing [MaxLength()], as it is said in the second exception what to use. But with this generator it throws first exception?

[darionato]

Yes, It throw that exception. I tried to figure out why but I didn't find why..

[borisdj]

Another thing I have stumbled upon. I make Db model and the Initial script and execute it successfully. Let's say in one class we have string parameter ** [MaxLength(50)] ** public string Title { get; set; }

After that I change it so that max length is 255. ** [MaxLength(255)] ** public string Title { get; set; }

And get the update script: **\ AlterColumn("dbo.Test", "Title", c => c.String(maxLength: 255, fixedLength: true));

But then on executing it to database got error: **\ 'ERROR: 42601: syntax error at or near "varchar"'

  'at Npgsql.NpgsqlState.<ProcessBackendResponses_Ver_3>d__a.MoveNext() at Npgsql.ForwardsOnlyDataReader.GetNextResponseObject() at Npgsql.ForwardsOnlyDataReader.GetNextRowDescription() at Npgsql.ForwardsOnlyDataReader.NextResult()...'

[darionato]

Try to throw this command in the Package manager console:

Update-Database -verbose

Check out the query that fails.

[borisdj]

Did that already, but it does not give me any more info. This is the full error: .......................... Using StartUp project 'TestPgMigration'. Using NuGet project 'TestPgMigration'. Specify the '-Verbose' flag to view the SQL statements being applied to the target database. Target database is: 'test' (DataSource: 127.0.0.1, Provider: Npgsql, Origin: Configuration). Applying code-based migrations: [201304111715166_Initial2]. Applying code-based migration: 201304111715166_Initial2. ALTER TABLE "dbo"."Movies" ALTER COLUMN "Title" varchar Npgsql.NpgsqlException: syntax error at or near "varchar" Severity: ERROR Code: 42601 at Npgsql.NpgsqlState.d__a.MoveNext() at Npgsql.ForwardsOnlyDataReader.GetNextResponseObject() at Npgsql.ForwardsOnlyDataReader.GetNextRowDescription() at Npgsql.ForwardsOnlyDataReader.NextResult() at Npgsql.ForwardsOnlyDataReader..ctor(IEnumerable1 dataEnumeration, CommandBehavior behavior, NpgsqlCommand command, NotificationThreadBlock threadBlock, Boolean synchOnReadError) at Npgsql.NpgsqlCommand.GetReader(CommandBehavior cb) at Npgsql.NpgsqlCommand.ExecuteNonQuery() at System.Data.Entity.Migrations.DbMigrator.ExecuteSql(DbTransaction transaction, MigrationStatement migrationStatement) at System.Data.Entity.Migrations.Infrastructure.MigratorLoggingDecorator.ExecuteSql(DbTransaction transaction, MigrationStatement migrationStatement) at System.Data.Entity.Migrations.DbMigrator.ExecuteStatements(IEnumerable1 migrationStatements) at System.Data.Entity.Migrations.Infrastructure.MigratorBase.ExecuteStatements(IEnumerable1 migrationStatements) at System.Data.Entity.Migrations.DbMigrator.ExecuteOperations(String migrationId, XDocument targetModel, IEnumerable1 operations, Boolean downgrading, Boolean auto) at System.Data.Entity.Migrations.DbMigrator.ApplyMigration(DbMigration migration, DbMigration lastMigration) at System.Data.Entity.Migrations.Infrastructure.MigratorLoggingDecorator.ApplyMigration(DbMigration migration, DbMigration lastMigration) at System.Data.Entity.Migrations.DbMigrator.Upgrade(IEnumerable1 pendingMigrations, String targetMigrationId, String lastMigrationId) at System.Data.Entity.Migrations.Infrastructure.MigratorLoggingDecorator.Upgrade(IEnumerable1 pendingMigrations, String targetMigrationId, String lastMigrationId) at System.Data.Entity.Migrations.DbMigrator.Update(String targetMigration) at System.Data.Entity.Migrations.Infrastructure.MigratorBase.Update(String targetMigration) at System.Data.Entity.Migrations.Design.ToolingFacade.UpdateRunner.RunCore() at System.Data.Entity.Migrations.Design.ToolingFacade.BaseRunner.Run() ERROR: 42601: syntax error at or near "varchar" ..........................

So far my not perfect fix is to change update script to sql query like this: Sql("ALTER TABLE dbo.\"Movies\" ALTER COLUMN \"Title\" TYPE varchar(255)");

When you have time, could you check if you are getting the same error? Just change maxLengt of string property and update database.

[darionato]

Could you post your migration code?

[borisdj]

This is the model: ............................ class Movie { [Key] public int MovieId { get; set; }

[MaxLength(50)] public string Title { get; set; } } ............................ Which gives following migration script code: ............................ CreateTable( "dbo.Movies", c => new { MovieId = c.Int(nullable: false, identity: true), Title = c.String(maxLength: 50, fixedLength: true), }) .PrimaryKey(t => t.MovieId); ............................ Then in model change MaxLength to 255 and make change script: ............................ AlterColumn("dbo.Movies", "Title", c => c.String(maxLength: 255, fixedLength: true)); ............................

Executing second script produces previously mentioned error.

[darionato]

I have the same error, tomorrow will be fixed. I'll let you know when it will be ready.

[darionato]

Done! Try and let me know.

[borisdj]

Works, thanks...