Using EF Code First and SqlCe in WPF

Question

I'm trying to use EF Code First with my WPF application , the idea is to create a SqlCe Db in AppData/MyApp (if there isn't one) and use it with EF Code First.

At the moment it's giving error when I try to read data off the database it supposed to create , but when I checked the db context object I saw it's trying to create it in SqlExpress.

First of all , how can I set it to work with CE instead of SqlExpress and set the file location?

I tried changing the connection string in app.config but couldn't get it to work (it didn't created sdf file) and also I'm not sure how to set connection string path to AppData folder as it's in User folder (not fixed).

Never worked with SqlCe or EF Code First before , so any help is welcome & appreciated.

Thanks in advance.

Answer 1

I've managed to get it working after a lot of messing around. My app.config has the following:

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
  <configSections>
  </configSections>

  <connectionStrings>
    <add name="Database"
         connectionString="Data Source=Database.sdf"
         providerName="System.Data.SqlServerCe.4.0" />
  </connectionStrings>

  <system.data>
    <DbProviderFactories>
      <remove invariant="System.Data.SqlServerCe.4.0" />
      <add name="Microsoft SQL Server Compact Data Provider 4.0"
           invariant="System.Data.SqlServerCe.4.0"
           description=".NET Framework Data Provider for Microsoft SQL Server Compact"
           type="System.Data.SqlServerCe.SqlCeProviderFactory, System.Data.SqlServerCe, Version=4.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"/>
    </DbProviderFactories>
  </system.data>

</configuration>

Having the DbProviderFactory in there will help when it comes to deployment too. It will allow users to use SQLCE 4 without running the installer for it (providing you supply the native binares as well, some info here: http://erikej.blogspot.com/2011/02/using-sql-server-compact-40-with.html).