Entity Framework connection to SQLite database not working after deployment

Question

I changed a dataset based approach to my sqlite database to one using the Entity Framework. I created the entities using Visual studio 2008's wizard and had the connection string stored in the app.config. A client program then communicates through WCF with the database. This works well on the development computer when I host the WCF service in a console application. After hosting the service in IIS7 on the remote computer I get the error: "The underlying provider failed on open". It is generated the first time a read operation is attempted from the database.

The autogenerated connection string is:

     <add name="xPMDbEntities" connectionString="metadata=res://*/PM_EDM.csdl|
            res://*/PM_EDM.ssdl|
            res://*/PM_EDM.msl;
            provider=System.Data.SQLite;
            provider connection string=&quot;
            data source=G:\PMPersistence\xPMDb.s3db&quot;"          
            providerName="System.Data.EntityClient" />

(I broke up the string in a few lines to make it more readable).

On the server this is not working, even if I locate the database in the exact same location as on the development computer. Does anything of the rest of the connection string, (of which I do not understand the first thing) need to be edited too? A link to a good resource about how to construct EF connection strings would be very helpful too!

EDIT: I also added the section below to the web.config file:

<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"/>
     </DbProviderFactories>
  </system.data>

This seems to work (Thomas suggests it should be located in the machine.config file) as before I added this entry I got an error saying that System.Data.SQLite could not be found.

EDIT2: I installed the System.Data.SQLite dll on the server rather than just xcopying the dll to the BIN directory. Both the machine.config in the Framework/Config and Framework64/Config folders now have the above DbFactory entry. It does not make a diference (unless I have to restart the server??)

Answer 1

The connection string was indeed OK. The problem was that you have to deploy both the system.data.sqlite.dll and the system.data.sqlite.linq.dll to the Bin folder of the web service that hosts the database connection.

Answer 2

Your connection string seems correct to me. Are you sure the SQLite ADO.NET provider is properly installed on the server ? Open your machine.config file, and check if there is an entry for System.Data.SQLite in the DbProviderFactories section

A link to a good resource about how to construct EF connection strings would be very helpful too!

Basically, you have to specify : - the model metadata (the parts with "res://...") - the store provider ("System.Data.SQLite" in your case) - the store connection string, which varies depending on which provider you use

The easiest way to construct a connection string dynamically is to use the EntityConnectionStringBuilder class, along with the connection string builder of your store provider.