LINQ + lightweight database: which db should I choose?

Question

I'm starting up a new web application. It's going to be hosted on a service that charges extra for SQL Server and frankly I don't think the site needs that much of a database. Right now the data model is 5 tables. And I'll be amazed if the largest table ever goes of 10k records.

So I'd like to keep the db lightweight. SQLite piqued my interest originally because I need to learn it for Android. But the lack of foreign keys makes me cringe. Sure it's possible to implement something that looks like foreign key constraints but it just feels un-relational. Firebird seems like the only lightweight (and free) db that supports FKs.

Also, I'd really like to get my feet wet in LINQ with this project. So far I've only found dbLINQ that lets me use SQLite or Firebird with LINQ. It's currently at v0.18 so it's far from primetime. I've run the tests for SQLite with dbLinq and they pass for what I need.

There was one other implementation of LINQ for SQLite but all the links I've found for it end up in 404s.

So what are my options for lightweight databases that are compatible with LINQ? Any of the compact editions of SQL Server are out, unless there's one that's XCOPY deployable with no install of an agent/service? I can't be asking the host to install new software since I doubt they'll do it and I want the app to be highly portable (with respect to hosting).

The list so far:

• SQLite
• Firebird
• SQL Server Compact
• VistaDB

Update: I tried out all of the versions and wrote up my impressions here. The short version: SQLite wins hands down. It's the only one that has a good GUI, no install footprint and is free.

Answer 1

You can use LINQ to SQL as is on an existing database, so long as you can make a standard IDbConnection object.

Here's some code to use LINQ on a Firebird DB.

DbProviderFactory dbProvider = DbProviderFactories
    .GetFactory("FirebirdSql.Data.FirebirdClient");

DbConnection connection = dbProvider.CreateConnection();
connection.ConnectionString = "some connection string";

DataContext linqContext = new DataContext(connection);

var query = from something in linqContext.GetTable<SomeType>() 
            select something.someproperty;