Entity Framework + SQLite deployment

Question

I have a ASP.NET MVC app that is using SQLite database through Entity Framework. Everything works on VS 2008's local development webserver.

However, deploying the web app to my service provider causes this error:

[ArgumentException: Unable to find the requested .Net Framework Data Provider.  It may not be installed.]
   System.Data.Common.DbProviderFactories.GetFactory(String providerInvariantName) +1308959
   System.Data.EntityClient.EntityConnection.GetFactory(String providerString) +35

Service provider has commented that they do not support SQLite. I had though that SQLite is independent of service provider's settings since it's App_Data deployable.

Has anyone experiences of a succesfull Entity Framework + SQLite deployment?

Cheers, -pom-

Answer 1

You're unlikely to be reading this anymore, but you're missing the following in your app.config (or, for you, web.config):

<configuration>
  <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>
</configuration>

Specifically, if you're using sqlite in a library which is linked into your website, you must add this to the config file of the website - not the library! This is because of how you're loading the provider: essentially, you're determining at runtime which dll to load, using the string "System.Data.SQLite", and locating the appropriate provider is done using the settings of the entry assembly.

Edit: By the way, when you're writing the library that has an sqlite dependancy, you can avoid this complexity. You do not need to use DbProviderFactories to look for sqlite at runtime; you can take a compile-time dependancy just as well, which can be easier to manage. Then you can ignore the above app.config section, and instead replace all instances of:

DbProviderFactories.GetFactory("System.Data.SQLite").CreateConnection()

with

System.Data.SQLite.SQLiteFactory.Instance.CreateConnection()

If you do so, you're using a plain library call to create the connection and there's no runtime selection of db provider. That can be less flexible since you can no longer exchange data providers via the config file, but for many libraries that's sufficient. Unfortunately, if you don't control the library code, this isn't an option.