SQLite vs. SQL CE 3.5 with Entity Framework 4

My wife and I have been taking a Japanese class. Im enjoying it quite a bit, even though learning languages has never been easy for me. I need to work hard to memorize all the new words, and that means lots and lots of practice.

My wife bought a pack of index cards. This is what a normal person would do. To me, it seems like defeat. Why use paper when theres a computer in the room? While shes at the store, I grab a white board and sketch out a data model.

I used WPF for a UI, two simple view models, and Entity Framework 4 to store the lists of characters, words, and phrases. I started out with SQL Compact Edition 3.5 as a database because that was the one that was there. That didnt work out so well, so on the advice of a colleague I switched my app to SQLite.

This is what I found:

SQL CE 3.5 SQLite
Pros
  • Can be created easily from the VS designer
  • Can view and modify data inside Visual Studio
  • Supports Identity Columns
  • Easy to deploy
  • Lots of good tools available for managing databases
  • Can be used from non .Net and even non Windows applications
Cons
  • Doesnt support Identity Columns in EF
  • Requires extra installation on client computers
  • Generating tables from a model is painful
  • Many model changes require regenerating the database
  • Doesnt map Integer type correctly
  • Doesn’t support foreign key constraints
  • Generating tables from a model is somewhat painful

SQL Compact Edition 3.5

Before I start dumping on SQL CE, I should say that I was trying the 3.5 version, not the fancy new 4.0 version. Maybe they’ve fixed all these issues.

Creating the model was easy, but as I went through a few iterations of the data model I quickly became frustrated with the inability to change the database to match the new model. I’m sure it’s possible to change it, but you have to write the scripts yourself and hope that you’ve named everything exactly right. Entity Framework created a handy database-generation script, but it won’t run directly on as SQL CE database because itdoesn’t support the GO statement. Copy and pasting each section of the script is annoying, but still better than typing all the names manually.

The realshock was adding data from inside my app. Entity Framework can’t generate data in a SQL CE database if the primary key is an identity. The best workaround I could find was to use a regular old integer for a primary key and search the model for the highest key value every time an entity is created. Not fun at all.

SQLite

It took a bit of fooling around to switch my database to SQLite. Most of the creation script ran on the database, though the types had to be changed a bit to match the database. All integers in SQLite are actually Int64 in .Net, but the Entity Framework maps them to Int32 in the model. Modifying the mapping files manually is annoying, but not hard to figure out.

Once the tables were set up and working, it was easy to copy the data over. The database tool I’m using for SQLite can import CSV files, even the multi-byte Japanese characters came across correctly.

The only thing left is to study.

You can get a .Net library for SQLite here: https://sqlite.phxsoftware.com/

It’s able to integrate with Visual Studio, even the Express version, but it didn’t work well for me. With a good database management tool you don’t need it. I’ve been using SQLite Administrator, which you can get here: https://sqliteadmin.orbmu2k.de/