Entity Framework Code First: SQL Server Compact vs LocalDB vs SQLite

If you are deploying a local database with your application to be installed on a user desktop, you should compare the following options:

  • LocalDb —
    • Entity Framework Code First Integration is seamless. 
    • It targets developer machines or IIS or Azure environment.  It isn’t meant to be deployed to end user machines.
    • Administrators of the installed machine has rights to everything.
    • Separate installation of the database software is needed. 
  • SQL Server Compact –
    • You can password protect the database. 
    • Entity Framework Code First Integration is seamless. 
    • It does not support stored procedures. 
    • Integrated application installation.
    • It is not supported in Windows 8 Metro App.
  • SQLite –
    • It supports most platforms, even Windows 8 Metro App. 
    • Code First Migration is not smooth at of this writing. 
    • You can encrypt the database. 
    • No separate installation needed.

To future proof your app, you should choose either LocalDb or SQLite.

The following requirements make me to choose SQL Server Compact:

  • Easy development with VS2013.  Code First migration is seamless.  You can download “SQL Server Compact Toolbox” for VS2013.
  • Easy Deployment/Installation.  Users should be able to install the application by launching only 1 installation.  In other words, users should not be required to install the database separately.
  • The database needs some security protection from the users/administrators on the installed machines.  One can debate the need for encryption and password protection.  However, these simple security mechanisms are better than none for non-critical applications.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s