I am setting up a brand new web application. It will likely be located on the service that charges extra for SQL Server and frankly I do not think the website needs that a database. At this time the information model is 5 tables. And I'm going to be amazed when the biggest table ever goes of 10k records.

So Let me keep your db lightweight. SQLite spurred my interest initially because I have to learn it for Android. However the insufficient foreign secrets makes me cringe. Sure you can implement something which appears like foreign key constraints however it just feels not-relational. Firebird appears such as the only lightweight (and free) db that supports FKs.

Also, I'd enjoy to obtain my ft wet in LINQ with this particular project. To date I have only found dbLINQ that allows me use SQLite or Firebird with LINQ. It's presently at v0.18 therefore it is not even close to primetime. I have run the tests for SQLite with dbLinq plus they pass for which I want.

There is another implementation of LINQ for SQLite but all of the links I have found for this finish up in 404s.

What exactly are my choices for lightweight databases which are suitable for LINQ? The compact models of SQL Server are out, unless of course there's one that is XCOPY deployable without any install of the agent/service? I can not be asking the host to set up new software since i have doubt they'll get it done and that i want the application to become highly portable (regarding hosting).

Their email list to date:

  • SQLite
  • Firebird
  • SQL Server Compact
  • VistaDB

Update: I used all the versions and wrote up my impressions here. Rapid version: SQLite wins hands lower. It's the only person which has a good GUI, no install footprint and it is free.

You should use LINQtoSQL out of the box with an existing database, as long as you may make a typical IDbConnection object.

Here's some code to make use of LINQ on the 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;

SQL Server Compact Edition (http://en.wikipedia.org/wiki/SQLCE) supports LINQ and the rest of the Versus tools, is made into Versus 2008, supports FKs, and it is XCOPY deployable having a flat-apply for a database. Be cautioned though, it's not without its caveats too, several things like sights or nested queries fall over, also it can be pretty bloated should you get the database size pretty large (i.e. 50MB+).

SQLite can also be much better if you are using the SQLite .Internet provider here (http://sqlite.phxsoftware.com/), works together with LINQ too, and it has fundamental Versus support.

I recommend you take a look at VistaDB. It'll do exactly what you're looking for using the additional benefit over SQLCE and SQLite it supports sights, saved methods and triggers. Ought to be fact it supports writing of methods and triggers in TSQL in addition to .Internet so that you can leverage your SQL Server As well as your .Internet understanding.

SQLite. Includes a good GUI (with auto-freakin-complete believe it or not), doesn't have install footprint, is free of charge and can work no matter where I host the web site. I understand I am responding to my very own question, but nobody else put just SQLite within their reply.

Important: SQLite will need an internet host that utilizes Full-Trust mode if you wish to run it on hosting that is shared.

SQL Server Express

VistaDB may be the only alternative should you likely to run your site at hosting that is shared (the majority of them will not allow you to run your websites under Full Trust mode) as well as if you want simple x-copy deployment enabled website.