Scaffold-DbContext error 42703 (EF Core 2.0) : column "start_value" does not exist

[abramlage]

We're writing a new version of our application in asp.net core, that requires us to connect to an existing PostgreSQL database (we have no control over this database and it has not changed in years). We connected to this in the previous .Net version of this project with no trouble (scaffolding with the "CopyMe.PostgreSQL.tt" file). However, when trying to use the Scaffold-DbContext command I am unable to do so because of the above error. I can't tell what table it is failing to scaffold, but there are two tables that do have a "start_date" column.

*Edited to add, this is for version 2.0

PM> Scaffold-DbContext "Server=xxxx;Database=xxxx;Persist Security Info=True;User Id=xxxx;Password=xxxx;Pooling=true;" Npgsql.EntityFrameworkCore.PostgreSQL
Npgsql.PostgresException (0x80004005): 42703: column "start_value" does not exist
   at Npgsql.NpgsqlConnector.<DoReadMessage>d__148.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter`1.GetResult()
   at System.Runtime.CompilerServices.ValueTaskAwaiter`1.GetResult()
   at Npgsql.NpgsqlConnector.<ReadMessage>d__147.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at Npgsql.NpgsqlConnector.<ReadMessage>d__147.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter`1.GetResult()
   at System.Runtime.CompilerServices.ValueTaskAwaiter`1.GetResult()
   at Npgsql.NpgsqlConnector.<ReadExpecting>d__154`1.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter`1.GetResult()
   at System.Runtime.CompilerServices.ValueTaskAwaiter`1.GetResult()
   at Npgsql.NpgsqlDataReader.<NextResult>d__32.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at Npgsql.NpgsqlDataReader.NextResult()
   at Npgsql.NpgsqlCommand.<Execute>d__71.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter`1.GetResult()
   at System.Runtime.CompilerServices.ValueTaskAwaiter`1.GetResult()
   at Npgsql.NpgsqlCommand.<ExecuteDbDataReader>d__92.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at System.Runtime.CompilerServices.TaskAwaiter`1.GetResult()
   at System.Runtime.CompilerServices.ValueTaskAwaiter`1.GetResult()
   at Npgsql.NpgsqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
   at Microsoft.EntityFrameworkCore.Scaffolding.Internal.NpgsqlDatabaseModelFactory.GetSequences()
   at Microsoft.EntityFrameworkCore.Scaffolding.Internal.NpgsqlDatabaseModelFactory.Create(DbConnection connection, IEnumerable`1 tables, IEnumerable`1 schemas)
   at Microsoft.EntityFrameworkCore.Scaffolding.Internal.NpgsqlDatabaseModelFactory.Create(String connectionString, IEnumerable`1 tables, IEnumerable`1 schemas)
   at Microsoft.EntityFrameworkCore.Scaffolding.Internal.RelationalScaffoldingModelFactory.Create(String connectionString, IEnumerable`1 tables, IEnumerable`1 schemas, Boolean useDatabaseNames)
   at Microsoft.EntityFrameworkCore.Scaffolding.Internal.ModelScaffolder.Generate(String connectionString, IEnumerable`1 tables, IEnumerable`1 schemas, String projectPath, String outputPath, String rootNamespace, String contextName, Boolean useDataAnnotations, Boolean overwriteFiles, Boolean useDatabaseNames)
   at Microsoft.EntityFrameworkCore.Design.Internal.DatabaseOperations.ScaffoldContext(String provider, String connectionString, String outputDir, String dbContextClassName, IEnumerable`1 schemas, IEnumerable`1 tables, Boolean useDataAnnotations, Boolean overwriteFiles, Boolean useDatabaseNames)
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.ScaffoldContextImpl(String provider, String connectionString, String outputDir, String dbContextClassName, IEnumerable`1 schemaFilters, IEnumerable`1 tableFilters, Boolean useDataAnnotations, Boolean overwriteFiles, Boolean useDatabaseNames)
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.ScaffoldContext.<>c__DisplayClass0_1.<.ctor>b__0()
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.OperationBase.<>c__DisplayClass3_0`1.<Execute>b__0()
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.OperationBase.Execute(Action action)
42703: column "start_value" does not exist

[cuteman18]

Have same issue when migrating from PostgreSQL to MySQL via MySQL Workbench. Get error:

File "/usr/lib/mysql-workbench/modules/db_generic_re_grt.py", line 76, in execute_query return cls.get_connection(connection_object).cursor().execute(query, *args, **kwargs) pyodbc.ProgrammingError: ('42703', '[42703] ERROR: column "start_value" does not exist;\nError while executing the query (1) (SQLExecDirectW)')

How to fix this??

[Bltspnt]

I have the exact same issue as OP. Any hints?

[abramlage]

Unfortunately I haven't been able to solve this (luckily it's for a feature I can put off implementing for a couple more weeks, figured I would wait for a response from @roji )

[roji]

@abramlage can you confirm that your PostgreSQL version is older than 9.1? This error is probably a result of trying to scaffold sequences and trying to find out what the starting value is. Here are the PostgreSQL docs for the relevant table, the start_value column was added in PostgreSQL 9.1.

If you're on PostgreSQL 9.0, it's strongly recommended you upgrade - the oldest version that's still officially supported by PostgreSQL themselves is 9.3. Having said that, it should be relatively easy to fix this in the Npgsql scaffolding code.

[roji]

As a sidenote, note that you can always write the database model yourself - strictly speaking scaffolding issues shouldn't block you. Another option to work around this is to dump your database schema, load it to another database and drop the sequences. You'll get all the tables and columns scaffolded, and can use this as a starting point.

[abramlage]

It is much older than 9.1, unfortunately I have no control over this database and it is not actively maintained. If there is nothing new and fancy that is needed, I can just grab the previous scaffolded version from my old project.

[roji]

OK, in that case I'll close this issue. If anyone in there future has a burning need to scaffold a pre-9.1 PostgreSQL please post back here and I'll see about reopening.