SQL Server Compact 4.0 connection string for Entity Framework code first?

Question

I am creating an application for Windows XP so I am stuck with .Net framework 4.0. I tried to use SQL Server Compact and EF code first but it make an error when update-database.

I wish to put the database in my code directory to deploy in customer machine.

This is my connection string:

<add name="QuanLyKhoContext" 
     connectionString="Data Source=MyData.sdf;Persist Security Info=False;AttachDBFileName=MyData.sdf" 
     providerName="System.Data.SqlClient" />

And the error:

This operation requires a connection to the 'master' database. Unable to create a connection to the 'master' database because the original database connection has been opened and credentials have been removed from the connection string. Supply an unopened connection.

Answer 1

In one of my projects I used SQL Server Compact 4.0 and my connection string was simple enough:

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

I think that you should check your providerName. I am not sure if System.Data.SqlClient is the right one.

Have you installed EntityFramework.SqlServerCompact NuGet package? Once it is installed System.Data.SqlServerCe.4.0 provider name is added and it should be used in the connection string.

Check if SqlServerCompact provider is added to your web.config.

  <entityFramework>
    <defaultConnectionFactory type="System.Data.Entity.Infrastructure.SqlCeConnectionFactory, EntityFramework">
      <parameters>
        <parameter value="System.Data.SqlServerCe.4.0" />
      </parameters>
    </defaultConnectionFactory>
    <providers>
      <provider invariantName="System.Data.SqlClient" type="System.Data.Entity.SqlServer.SqlProviderServices, EntityFramework.SqlServer" />
      <provider invariantName="System.Data.SqlServerCe.4.0" type="System.Data.Entity.SqlServerCompact.SqlCeProviderServices, EntityFramework.SqlServerCompact" />
    </providers>
  </entityFramework>
  <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>