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?

I've followed your instructions and everything works correctly. — Xavier Egea

h bob h bob · Accepted Answer · 2014-09-07T12:12:24

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.

Use SQL Server Compact with ASP.NET Identity

1 Answers