Use SQL Server Compact with ASP.NET Identity

Question

I'm using the latest ASP.NET MVC, Identity and Entity Framework.

The sample Identity project uses Sql Server Express. I want to use Sql Server Compact.

What I did:

• Created a new empty solution (not MVC template)

• Installed sample project: Install-Package Microsoft.AspNet.Identity.Samples -Pre

• Installed: Install-Package Microsoft.SqlServer.Compact

• Installed: Install-Package EntityFramework.SqlServerCompact

• Changed default connection string in web.config to:

  <add name="Foo" 
       providerName="System.Data.SqlServerCe.4.0" 
       connectionString="Data Source=|DataDirectory|\Foo.sdf;" />
  

• Changed ApplicationDbContext to use the Foo connection string:

  ApplicationDbContext() : base("name=Foo", false)
  

Problem is, when ApplicationDbContext is accessed for the first time (during seeding, in the ApplicationDbInitializer.InitializeIdentityForEF() method):

• the database is created
• I get an InvalidOperationException: UserId not found for the line result = userManager.SetLockoutEnabled(user.Id, false);

I thought it would be as simple as changing the connection string - so what am I doing wrong? Or put differently, how do I convert the solution from SQL Server Express to SQL Server Compact?

Answer 1

Found the error. My connection string was wrong, I had:

<add name="Foo"
     providerName="System.Data.SqlServerCe.4.0"
     connectionString="
       Data Source=|DataDirectory|\Foo.sdf;
       Persist Security Info=false;
       "/>

which doesn't work (XML, so thought whitespace is ignored, it's not), whereas this does:

<add name="Foo"
     providerName="System.Data.SqlServerCe.4.0"
     connectionString="Data Source=|DataDirectory|\Foo.sdf;Persist Security Info=false;" />

Regardless, the question itself contains a step-by-step for using sqlce with Identity2. Maybe it'll help someone.