Using Entity Framework with SQLite - Installation

SQLite is a simple but powerful open source database engine which you can use among everything else at development time and later connect your application to database server at run time.

Tools I have found to be pretty helpful to administer SQLite databases are: SQLite Expert Personal and sqliteadmin.

To use SQLite with the Entity Framework you need ADO.NET Provider. A complete provider is System.Data.SQLite.

Download file SQLite-1.0.65.0-setup.exe and install to appropriate location. I used directory C:\Programme\SQLite.NET as installation directory. In subdirectory bin into the installation directory you'll find two libraries - "System.Data.SQLite.Linq.dll", "System.Data.SQLite.dll" - which you optionally can register into the global assembly cache (gacutil -I ). To connect the Entity Framework to SQLite database you'll need to add references to these two assemblies.

Now, there is a nice dot.NET project on MSDN - EFQuerySamples - which demonstrates the Entity Framework with the well known Northwind database. You can use that application to learn the syntax of the Entity Framework queries and even to see the generated SQL. Download the project and unzip the file.

To get the EFQuerySamples running against SQLite database, there is a supplement to the MSDN project - EFQuerySamples_SQLite. You need to copy from this over the original project the following files.

Having the two projects unzipped next to each other copy:

file EFQuerySamples_SQLite\App.config to EFQuerySamples\App.config
file EFQuerySamples_SQLite\Entity Framework Query Samples.csproj to EFQuerySamples\Entity Framework Query Samples.csproj
file EFQuerySamples_SQLite\DB\northwindEF.db to EFQuerySamples\DB\northwindEF.db
file EFQuerySamples_SQLite\Schemas\NorthwindEFModel.SQLite.ssdl to EFQuerySamples\Schemas\NorthwindEFModel.SQLite.ssdl

Open the Visual Studio solution from EFQuerySamples.

Add references to the assemblies System.Data.SQLite and System.Data.SQLite.Linq.

Open file App.config and add new DbProviderFactory:

  
  <system.data>
    <DbProviderFactories>
      <remove invariant="System.Data.SQLite"/>
      <add name="SQLite Data Provider" invariant="System.Data.SQLite" 
           description=".Net Framework Data Provider for SQLite" 
           type="System.Data.SQLite.SQLiteFactory, System.Data.SQLite, Version=1.0.65.0, Culture=neutral, PublicKeyToken=db937bc2d44ff139"/>
   </DbProviderFactories>
    </system.data>
   
 

Take care of the Version in attribute type, make sure equals the version of the System.Data.SQLite.dll you downloaded.

Also in App.config there should be addition to section connectionStrings coming with the supplement project:

  
  <connectionStrings>
    <add name="NorthwindEF (SQLite)" connectionString="provider=System.Data.SQLite;metadata=Schemas\NorthwindEFModel.csdl|Schemas\NorthwindEFModel.msl|Schemas\NorthwindEFModel.SQLite.ssdl;Provider Connection String='Data Source=DB\northwindEF.db'"
      providerName="System.Data.EntityClient" />


    ...

Now you can run the EFQuerySamples application, choose the SQLite connection and run the various examples.

Enjoy!

Atanas Hristov