Entity Framework, ASP.NET, and SQL Server CE

Question

With VS 2010 SP1 I created an ASP.NET MVC4 project from the "internet" template. I then created a connection string for SQL Server CE 4.0:

<add name="DefaultConnection"
     connectionString="Data Source=|DataDirectory|MyDatabase.sdf;Password=123456" 
     providerName="System.Data.SqlServerCe" />

With the web application successfully debug-launched in cassini, I choose the "Register" user option. Immediately this causes the InitializeSimpleMembershipAttribute filter to execute. The filter crashes the site when it reaches this code:

Database.SetInitializer<UsersContext>(null);
using (var context = new UsersContext())
{
    if (!context.Database.Exists())
    {
        // Create the SimpleMembership database without Entity Framework migration schema
        ((IObjectContextAdapter)context).ObjectContext.CreateDatabase();
    }
}
//This line never executes.  It is meant to configure my custom user table.
WebSecurity.InitializeDatabaseConnection("DefaultConnection", "Users", "ID", "UserName", true);

The Exists() check throws an ArgumentException stating:

Unable to find the requested .Net Framework Data Provider. It may not be installed.

Now I wanted to be sure that (1) there was nothing wrong with my connection string and (2) there was nothing wrong with my provider. To do that, I inserted a snippet of code before the Exists() check. The snippet used a new SqlCeEngine to create the database, and Dapper calls to setup user tables. That code worked just fine, just before exploding on the Exists() check again.

I then considered that EF might need some additional setup help. I tried replacing the EF defaultConnectionFactory in my web.config:

<entityFramework>
  <defaultConnectionFactory type="System.Data.Entity.Infrastructure.SqlCeConnectionFactory, EntityFramework">
    <parameters>
      <parameter value="System.Data.SqlServerCe.4.0" />
    </parameters>
  </defaultConnectionFactory>
</entityFramework>

Still the exception I received did not change.

I'm now wondering if EF needs a "special" connection string to work with SQL Server CE. I will be checking here and here. But at first glance I'm not sure that is the issue.

Thoughts?

score 0 · Answer 1 · answered Dec 19 '12 at 03:10

0

You need to install Sql Server Compact edition. You can download it from here.

answered Dec 19 '12 at 03:10

Shyju

214,206
104
411
497

I used the test suggested by soadyp to confirm that it is indeed already installed. I have also checked through web platform installer, which indicates that I already have `SQL Server Compact SP1 CTP1` installed as well as `VS 2010 SP1 tools for CE 4.0`. The only thing that is listed as not yet installed is `VS 2010 SP1 tools for CE 4.0 Installer`. – Brent Arias Dec 19 '12 at 04:18

phil soady · Accepted Answer · 2012-12-19T05:03:59.503

0

EF requires the providers to be installed. Use one the connect to DB options to check. eg ADD Entity DAta Model to Project. Just to the providers available to it. Do you see your preferred connection Client ? enter image description here

Seems Compact edition should work with some restrictions... http://technet.microsoft.com/en-us/library/cc835494.aspx

So then I think its The "DEFAULT CONNECTION" issue See the Context constructor. EF looks for a connection by that name unless you pass an alternative in. try...

<connectionStrings>
    <add name="MyContextName" connectionString="bla";App=EntityFramework"
       providerName="System.Data.SqlServerCe.4.0" />

edited Dec 19 '12 at 05:03

answered Dec 19 '12 at 03:25

phil soady

11,043
5
50
95

"Microsoft SQL Server Compact 4.0" is an already existing option for me on that dialogue box. Thus, evidently, I already have it installed. Or did I misunderstand your point? – Brent Arias Dec 19 '12 at 04:17
please check Connection Name EF is using. See the constructor. Assuming you have a class myContext:DbContext – phil soady Dec 19 '12 at 04:54
1

It turns out that merely changing the providerName from "System.Data.SqlServerCe" to "System.Data.SqlServerCe.4.0" was enough to solve the problem! It really threw me off target when Dapper worked successfully without the ".4.0" extension. I guess Dapper knows how to auto-switch between versions, whereas EF cannot. – Brent Arias Dec 19 '12 at 05:02
I was about to ask where you got the provider name from... thanks for updating ... very good habit – phil soady Dec 19 '12 at 05:05

Entity Framework, ASP.NET, and SQL Server CE

2 Answers2