SQLite vs. SQL CE 3.5 with Entity Framework 4
My wife and I have been taking a Japanese class. I’m 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 there’s a computer in the room? While she’s 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 didn’t 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
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
Doesn’t 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
Doesn’t map Integer type correctly
Doesn’t support foreign key constraints
Generating tables from a model is somewhat painful
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 it doesn’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 real shock 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.
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/