Entity Framework with File-Based Database

Question

I am in the process of developing a desktop application that needs a database. The application is currently targeted to SQL Express 2005 and works wonderfully. However, I'm not crazy about having this dependency on SQL Express and would prefer to use a small file-based database.

My problem is that I am using Entity Framework. I have tried both SQL Compact and SQLite, and they both have bizarre problems with EF v1. I get errors creating the Model, invalid models when it does get created... it's a nightmare.

I'm about ready to give up and write a data layer and repository in the good-old-school Connection/Command pattern. Not my favorite plan...

Is there a lightweight, file-based database out there that plays well with EF?

OR

Is there a better ORM tool that I should use instead of EF with my lightweight DB?

Answer 1

I believe NHibernate supports both SQL Server Compact Edition and SQLite. That is the route that I would recommend you take.

http://www.nhforge.org/doc/nh/en/index.html#configuration-optional-dialects

Answer 2

Visual Studio 2010 with Entity Framework 4.0 is now in Beta 2 and has a Go-Live license, so if you don't mind using beta software you can have all it's goodness now. I believe it has much better support for SQL Server Compact Edition.

Answer 3

Mindscape LightSpeed has superb handling for file system databases (such as SQLite) and is free for small databases.

It's fairly straight forward to make the move as it has a LINQ provider, Visual Studio integrated designer (including supporting SQLite on the design surface) and there's significant help on getting up and running fast :-)

Answer 4

Give a try to Devart dotConnect for SQLite!