Update SQL Server CE database (.sdf) from 3.5 to 4.0

Question

I need to upgrade a SQL Server Compact database from 3.5 to 4.0 version. I am using linq-to-sql

I tried some things that I found on stackoverflow, that did not help:

1. I tried Add 4.0 connection dialog (no error messages, bak file was created)

2. I tried upgrading in code: (no error messages)

   System.Data.SqlServerCe.SqlCeEngine engine= new System.Data.SqlServerCe.SqlCeEngine("Data source = ...");
   engine.Upgrade();
   

3. I checked for database corruption (system returned that there are no corruption problems)

   System.Data.SqlServerCe.SqlCeEngine engine= new System.Data.SqlServerCe.SqlCeEngine("Data source = ...");
   engine.Verify();
   

   After these operations I wanted to recreate dbml file - I received error message

   Incompatible Database Version (..) DB version 4000000, Requested version 3505053 (..)

4. In debug mode I checked db.Connection.ServerVersion = returns 3.5.8080.0

5. In database connection properties version is 4.0.8876.1

Any suggestions?

Answer 1

Once you have upgraded your database to 4.0, you can no longer create a dbml file, as the Tool responsible for this only Works with 3.5 databasee files. One possible workaround is to have two version of the database, one 3.5 for dbml generation, and another for use. Remember to initialize the DataContext object with a SqlCeConnection object, otherwise 4.0 will not Work with LINQ to SQL - or you can try my SQL Server Compact Toolbox, that allows you to generate a DataContext directly from a 4.0 database file (must still initialize with SqlCeConnection object)