How to fix Incorrect syntax near the keyword "NOT" during Update-Database using Migration


I am new into software development. I am currently developing Asp.Net Core API. I have managed to do Add-migration successfully, the problem start when I need to Update-Database.

The error is as follows

System.Data.SqlClient.SqlException (0x80131904): Incorrect syntax near the keyword 'NOT'.
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
   at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async, Int32 timeout, Boolean asyncWrite)
   at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, Boolean sendToPipe, Int32 timeout, Boolean asyncWrite, String methodName)
   at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
   at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.Execute(IRelationalConnection connection, DbCommandMethod executeMethod, IReadOnlyDictionary`2 parameterValues)
   at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.ExecuteNonQuery(IRelationalConnection connection, IReadOnlyDictionary`2 parameterValues)
   at Microsoft.EntityFrameworkCore.Migrations.MigrationCommand.ExecuteNonQuery(IRelationalConnection connection, IReadOnlyDictionary`2 parameterValues)
   at Microsoft.EntityFrameworkCore.Migrations.Internal.MigrationCommandExecutor.ExecuteNonQuery(IEnumerable`1 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.UpdateDatabase.<>c__DisplayClass0_1.<.ctor>b__0()
   at Microsoft.EntityFrameworkCore.Design.OperationExecutor.OperationBase.Execute(Action action)
Error Number:156,State:1,Class:15
Incorrect syntax near the keyword 'NOT'.

Your assistance will be much appreciated.

asked on Stack Overflow May 31, 2019 by Ras C-kay sKatle • edited May 31, 2019 by ckuri

2 Answers


I faced the same issue in initial migration to create database. The PMC console throw " Incorrect syntax near the keyword 'NOT'. " error. after debugging couple of minutes i found in the .HasColumnType("varchar(3"); attribute for one of the field. It seems that during migration one bracket was lost in auto generated migration file - it looked like this "HasColumnType("varchar(3") instead it should look like this HasColumnType("varchar(3)") like this.

I found this in "/Migrations/..ModelSnapshot.cs", in both, "Up" and "Down"methods. and "/Migrations/..20190613110239_InitialCreate"

After adding bracket in this two places the Update-Database command works as intended.

I hope it will help! Happy Coding.

answered on Stack Overflow Jun 13, 2019 by YogeshJadhav

As highlighted by YogeshJadadhav, you might need to check your auto generated file for errors. Note that the error might not be highlighted by the debugger because its not a syntax error, but rather a string error. Here is a snippet of code showing how I resolved this issue.

Auto generated code with error

 Price = table.Column<decimal>(type: "decimal(18, 2", nullable: false)

Corrected Code

 Price = table.Column<decimal>(type: "decimal(18, 2)", nullable: false)

You will have to ractify this error in your migrationName.cs and migrationName.Designer.cs.

answered on Stack Overflow Oct 25, 2020 by Emmanuel Motsi

User contributions licensed under CC BY-SA 3.0