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

SQL Server Express

Answer 2

SQL Server Compact Edition (http://en.wikipedia.org/wiki/SQLCE) supports LINQ and all the other VS tools, is built into VS 2008, supports FKs, and is XCOPY deployable with a flat-file for a database. Be warned though, that it's not without its caveats as well, a lot of things like views or nested queries fall over, and it can be pretty bloated if you start getting the database size pretty big (i.e. 50MB+).

SQLite is also much better if you use the SQLite .NET provider here (http://sqlite.phxsoftware.com/), works with LINQ as well, and has basic VS support.

Answer 3

I would suggest you have a look at VistaDB. It'll do exactly what you're looking for with the additional benefit over SQLCE and SQLite that it supports views, stored procedures and triggers. As a matter of fact it supports writing of procedures and triggers in TSQL as well as .NET so you can leverage your SQL Server AND your .NET knowledge.

Answer 4

You can use LINQtoSQL 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;

Answer 5

SQLite. Has a good GUI (with auto-freakin-complete no less), has no install footprint, is free and will work regardless of where I host the website. I know I'm answering my own question, but no one else put just SQLite in their reply.

Important: SQLite will require a web host that uses Full-Trust mode if you want to run it on shared hosting.

Answer 6

VistaDB is the only alternative if you going to run your website at shared hosting (almost all of them won't let you run your websites under Full Trust mode) and also if you need simple x-copy deployment enabled website.