Update-Database results in `There was an error parsing the query` for second migration

[ChrisK91]

Update-Database results in error after second migration

After adding a second migration file via Add-Migration, the Update-Database results in an There was an error parsing the query. [ Token line number = 1,Token line offset = 2,Token in error = ; ] using SqlServerCe

Functional impact

Makes manual updates to database file necessary

Minimal repro steps

1. Create a new console application
2. Run Install-Package EntityFramework.MicrosoftSqlServer –Pre
3. Run Install-Package EntityFramework.Commands –Pre
4. Run Install-Package EntityFramework.SqlServerCompact40 –Pre

5. Add a minimal default database

   public class TiffFilesContext : DbContext
   {
      public DbSet<FileInfo> Files { get; set; }
   
      protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
      {
          optionsBuilder.UseSqlCe(@"Data Source=Database.sdf");
      }
   
      protected override void OnModelCreating(ModelBuilder modelBuilder)
      {
          modelBuilder.Entity<FileInfo>().Property(f => f.Path).IsRequired();
      }
   }
   
   public class FileInfo
   {
      public int FileInfoId { get; set; }
      public string Path { get; set; }
      public String BlindedName { get; set; }
      public bool ContainsSynapse { get; set; }
   }

6. Run Add-Migration Migration01
7. Run Update-Database
8. Update FileInfo to contain another int-Column

public class FileInfo
    {
        public int FileInfoId { get; set; }
        public string Path { get; set; }
        public String BlindedName { get; set; }
        public bool ContainsSynapse { get; set; }
        public int Quality { get; set; }
    }

1. Run Add-Migration Migration02
2. Run Update-Database

   Expected result

The Migration should be applied succesfully

Actual result

The Migration fails with There was an error parsing the query. [ Token line number = 1,Token line offset = 2,Token in error = ; ]

[ErikEJ]

Could you try to script the migration: http://ef.readthedocs.org/en/latest/cli/powershell.html#script-migration

[ChrisK91]

This generates the following SQL:

ALTER TABLE [FileInfo] ADD [Quality] int NOT NULL DEFAULT 0;

GO

INSERT INTO [__EFMigrationsHistory] ([MigrationId], [ProductVersion])
VALUES ('20160213114504_Migration02', '7.0.0-rc1-16348');

GO

In the first query, omitting the semicolon (NOT NULL DEFAULT 0;), creates a valid query which I can rund using e.g. LINQPad. It seems, that SqlCe doesnt like that semicolon.

[ErikEJ]

The original SQL

ALTER TABLE [FileInfo] ADD [Quality] int NOT NULL DEFAULT 0;

parses fine in the SQL Compact Toolbox editor...

So maybe the issue with the semicolon is LinQPad related In other words, could you try to run the generated script against a test dabase file in the "SQL Server Compact Toolbox" extinsion?

[ChrisK91]

I've now installed the Toolbox as well... And indeed, it works if I run the query from within the toolbox window. However the Update-Database still results in said error. I wonder if these different tools use different versions of SqlCe...

[ErikEJ]

No, they all use the same version - I will try to investigate! Maybe the script generation is different from Update-Database...

[ErikEJ]

Thanks for reporting this bug, it is fixed in the latest source, and the fix will be included in RC2 https://github.com/ErikEJ/EntityFramework.SqlServerCompact/pull/308

[ChrisK91]

Awesome, thank you for your help :+1: