SQLite in class library. DB-Provider not found

Question

We have a class library dealing with all DB-related operations. (SQLite and MSSQL) I tried to get rid of all the providers and factories (with this approach) in the executable project, since the class library already has the DB-Configuration in the App.config.

 <entityFramework>
    <providers>
      <provider invariantName="System.Data.SqlClient" type="System.Data.Entity.SqlServer.SqlProviderServices, EntityFramework.SqlServer" />
      <provider invariantName="System.Data.SQLite.EF6" type="System.Data.SQLite.EF6.SQLiteProviderServices, System.Data.SQLite.EF6" />
      <provider invariantName="System.Data.SQLite" type="System.Data.SQLite.EF6.SQLiteProviderServices, System.Data.SQLite.EF6" />
    </providers>
  </entityFramework>

  <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" />
      <remove invariant="System.Data.SQLite.EF6" />
      <add name="SQLite Data Provider (Entity Framework 6)" invariant="System.Data.SQLite.EF6" description=".NET Framework Data Provider for SQLite (Entity Framework 6)" type="System.Data.SQLite.EF6.SQLiteProviderFactory, System.Data.SQLite.EF6" />
    </DbProviderFactories>
  </system.data>

However, while MSSQL works fine so far, SQLite throws an exception:

It does work if I add the provider to the App.config in the executable. But this is what I try to avoid. Is there any solution to this problem?

Answer 1

In order to support EF6 database provider w/o app.config, you'd normally need DbConfiguration derived class which registers the appropriate DbProviderFactory and DbProviderServices using respectively SetProviderFactory and SetProviderServices methods.

However that's not enough for SQLite provider because it doesn't implement IProviderInvariantName service which causes the runtime exception in question. So you need to add implementation of that service through custom IDbDependencyResolver registered via AddDependencyResolver method.

Assuming your class library project have references to System.Data.SQLite and System.Data.SQLite.EF6 assemblies, and connection string uses "System.Data.SQLite.EF6" as providerName, add the following implementation classes to the class library project:

using System.Data.Entity.Infrastructure;
using System.Data.Entity.Infrastructure.DependencyResolution;
using System.Data.SQLite.EF6;
using System.Data.SQLite;

class SQLiteProviderInvariantName : IProviderInvariantName
{
    public static readonly SQLiteProviderInvariantName Instance = new SQLiteProviderInvariantName();
    private SQLiteProviderInvariantName() { }
    public const string ProviderName = "System.Data.SQLite.EF6";
    public string Name { get { return ProviderName; } }
}

class SQLiteDbDependencyResolver : IDbDependencyResolver
{
    public object GetService(Type type, object key)
    {
        if (type == typeof(IProviderInvariantName))
        {
            if (key is SQLiteProviderFactory || key is SQLiteFactory)
                return SQLiteProviderInvariantName.Instance;
        }
        return null;
    }

    public IEnumerable<object> GetServices(Type type, object key)
    {
        var service = GetService(type, key);
        if (service != null) yield return service;
    }
}

Then add the following configuration class (or update the existing if you already have one) again to the class library project containing your context class:

using System.Data.Entity;
using System.Data.Entity.Core.Common;
using System.Data.SQLite.EF6;

class MyDbConfiguration : DbConfiguration
{
    public MyDbConfiguration()
    {
        SetProviderFactory(SQLiteProviderInvariantName.ProviderName, SQLiteProviderFactory.Instance);
        SetProviderServices(SQLiteProviderInvariantName.ProviderName, (DbProviderServices)SQLiteProviderFactory.Instance.GetService(typeof(DbProviderServices)));
        AddDependencyResolver(new SQLiteDbDependencyResolver());
    }
}

And that's all. You can remove app.config from class library project and will be able to use connection strings like this from the executable app.config:

<add name="MyDb_SQLite" providerName="System.Data.SQLite.EF6" connectionString="Data Source =|DataDirectory|MyDb.sqlite" />

For more info, see Code-based configuration and related topics of the EF6 documentation. Please note that class libraries are not applications, so application config file really means executable config file. There is no way to force EF6 using your class library config file, so the code based configuration and executable config file are the only options.