The SQL Compact Database format is a simple file that acts as a SQL database, lately I have loved that kind of database as the set up is mainly non existing and the portability is amazing. Of course, without explicitly saying it, there are lots of limitations. You need to use that kind of technology for simple applications that do not require lots of data.
Using the SQL Compact Database with Entity Framework Code First is just elegant and clean code, then a little ASP.NET MVC 3 creates a project that can be deployed almost anywhere.
First stop was the Azure Cloud, just by adding a new project Azure to the solution, will let you publish the web project to Azure, that simple, you do not need to convert the ASP.NET MVC 3 app to Azure, you still can deploy the web app to your server with a simple Publish or Web Deploy.
Now, when deploying to Azure, you’ll see this error with the SQL Server Compact:
General failure Unable to load the native components of SQL Server Compact corresponding to the ADO.NET provider of version 8482. Install the correct version of SQL Server Compact. Refer to KB article 974247 for more details.
The problem is a missing DLL that is required for the SQL Server Compact, to avoid this problem, I recommend to go to project and add all deployable dependencies to the project like the image below:
Now all the dlls required to run the application will be in a folder ready to package and be sent to Azure.
In a few minutes, your ASP.NET MVC 3 with SQL Server Compact Database using Entity Framework Code First will be running in the cloud. Please make sure to delete the package from Azure when you are not using it to avoid charges, just by doing an stop of the application is not enough.