ASP.NET MVC4 Project + SQL CE 4 limitations

With the official RTM release of Visual Studio 2012 and the MVC 4 project (also available for VS 2010), I have been using some of the features of new features and out of the box features of hte MVC 4 project to ease the development of my user stories.

SQL CE 4 made database backends much simpler by allowing developers to utilize a SQL engine even if you don’t have it installed on your computer and the zero-config usage is great. SQL CE does have some its limitations and the OAuthWebSecurity class  exposes one such limitation: Transasction Scopes.

Given a development environment with a SQL CE 4 database, you can register a number of authentication providers such as Facebook and Google out of the box with no issues.

Dictionary<String,Object> facebookPermissions = new Dictionary<string,object>() ;
facebookPermissions.Add( "scope", "email" );
OAuthWebSecurity.RegisterFacebookClient(
  appId: "My Facebook App ID",
  appSecret: "My Facebook App Secret",
  displayName: "Facebook",
  extraData: facebookPermissions
);

OAuthWebSecurity.RegisterGoogleClient();

The limitation materializes when trying to disassociate the authentication provider (in this case, Google) from the base account. If you are using SQL CE 4 and the code made available to you with the out of the box ASP.NET MVC 4 project, you will get the following error when attempting to disassociate the account with the OAuth provider.

The connection object can not be enlisted in transaction scope.

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.InvalidOperationException: The connection object can not be enlisted in transaction scope.

Source Error:

Line 110: using (var scope = new TransactionScope(TransactionScopeOption.Required, new TransactionOptions { IsolationLevel = IsolationLevel.Serializable }))
Line 111: {
Line 112:  bool hasLocalAccount = OAuthWebSecurity.HasLocalAccount(WebSecurity.GetUserId(User.Identity.Name));
Line 113:   if (hasLocalAccount || OAuthWebSecurity.GetAccountsFromUserName(User.Identity.Name).Count > 1)
Line 114:   {

Deploying an ASP.NET Web Application with SQL CE 4 & Entity Framework without installation

Update – 12/15/2011

As of Visual Studio 2010 SP1, there’s an easier way to do everything below.

In the Solution Explorer, right click the project and select ‘Add Deployable Dependencies…’, select the dependencies you want to include, and click OK.

Visual Studio 2010 - Add Deployable Dependencies

This will generate the folder _bin_deployableAssemblies with the proper DLLs. Now when you build your solution, the DLLs in _bin_deployableAssemblies are copied to the bin folder.

See Scott Hanselman’s post for a more thorough explanation.

Original Post

I have built an ASP.NET project using SQL CE 4.0 and Entity Framework 4.1. When the time came to deploy the web application to our managed environment, I ran into some issues due to some missing references.

Do to the nature of our hosted environment, installing the EntityFramework and SQLCE4 libraries was not an option but I have worked out all of the required steps to get the web application working in the deployed environment.

Referenced Libraries

The following libraries should be referenced in your project.

EntityFramework
System.Data.SqlServerCE
System.Data.SqlServerCE.Entity
System.Web.Providers

Make sure to set the Copy Local property to true.

Manual Copy

The following files should be copied as part of your build script process. These files are placed on your file system when you install the SQL CE 4 package.

sqlceca40.dll
sqlcecompact40.dll
sqlceer40EN.dll
sqlceme40.dll
sqlceoledb40.dll
sqlceqp40.dll

Web.config

On my development box, the machine.config has been modified (when I installed SQL CE 4) to include the SQL CE 4 provider. To use the provider without explicitly installing it on the server, we need to manually add the provider in the Web.config

  <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>