c# - EF6, SQLite won't work without App.config

Question

I'm trying to make a plug in that will use EF6.1 and SQLite for an app where I can't change the App.config so all the configuration and connection string needs to be set in code.

I found this answer that looked promising Problems using Entity Framework 6 and SQLite

So now I have a configuration class like this:

public class CollectionDbConfiguration : DbConfiguration
{
    public CollectionDbConfiguration()
    {
        SetProviderServices("System.Data.SQLite"(DbProviderServices)SQLiteProviderFactory.Instance.GetService(typeof(DbProviderServices)));
        SetProviderFactory("System.Data.SQLite.EF6", SQLiteProviderFactory.Instance);
        SetProviderFactory("System.Data.SQLite", SQLiteFactory.Instance);                 
    }
}

I have confirmed this gets hit before the context is created for the first time and all these return values.

My context looks like this

public class MyContext : DbContext
{
    public MyContext(string connectionString)
    :   base(connectionString) { }

    public DbSet<MyEntity> MyEntities { get; set; }      
}

And I have some code calling it like this:

var context = new MyContext("Data Source = mytest.db; Version = 3;");
var entities = context.MyEntities.ToList();

When I try and run the code it looks like the context is assuming the connection string is for SQL Server as it gives me:

Keyword not supported: 'version'.

If I remove it I then get an error that it cannot connect and its clearly trying to connect to a SQL Server database.

I tried passing in a SQLite connection by adding a constructor:

public MyContext(DbConnection connection) 
    : base(connection, contextOwnsConnection: true)
{ }

And calling it with this:

var context = new MyContext(new SQLiteConnection("Data Source = mytest.db; Version = 3;"));
var entities = context.MyEntities.ToList();

But then I get the error:

Unable to determine the DbProviderFactory type for connection of type 'System.Data.SQLite.SQLiteConnection'. Make sure that the ADO.NET provider is installed or registered in the application config.

So I made a factory resolver and registered that

public class FactoryResolver : IDbProviderFactoryResolver
{
    public DbProviderFactory ResolveProviderFactory(DbConnection connection)
    {
        if (connection.GetType() == typeof(SQLiteConnection))
        {
            return SQLiteFactory.Instance;
        }

        if (connection.GetType() == typeof(EntityConnection))
        {
            return SQLiteProviderFactory.Instance;
        }

        return null;
    }
}

And added:

SetProviderFactoryResolver(new FactoryResolver());

but now I get this:

No Entity Framework provider found for the ADO.NET provider with invariant name 'System.Data.SQLite.EF6'. Make sure the provider is registered in the 'entityFramework' section of the application config file. See http://go.microsoft.com/fwlink/?LinkId=260882 for more information.

I've beet at this for two days now and I'm running out of ideas.

Answer 1

The minimum needed to make the constructor with connection string working is a custom IProviderInvariantName, IDbDependencyResolver and DbConfiguration:

public 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)) return SQLiteProviderInvariantName.Instance;
        if (type == typeof(DbProviderFactory)) return SQLiteProviderFactory.Instance;
        return SQLiteProviderFactory.Instance.GetService(type);
    }

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

class SQLiteDbConfiguration : DbConfiguration
{
    public SQLiteDbConfiguration()
    {
        AddDependencyResolver(new SQLiteDbDependencyResolver());
    }
}

Now this should work:

var context = new MyContext("Data Source = mytest.db; Version = 3;");
var entities = context.MyEntities.ToList();

Update: For NET4.0 you would also need a custom IDbProviderFactoryResolver:

class SQLiteDbProviderFactoryResolver : IDbProviderFactoryResolver
{
    public static readonly SQLiteDbProviderFactoryResolver Instance = new SQLiteDbProviderFactoryResolver();
    private SQLiteDbProviderFactoryResolver() { }
    public DbProviderFactory ResolveProviderFactory(DbConnection connection)
    {
        if (connection is SQLiteConnection) return SQLiteProviderFactory.Instance;
        if (connection is EntityConnection) return EntityProviderFactory.Instance;
        return null;
    }
}

and add

if (type == typeof(IDbProviderFactoryResolver)) return SQLiteDbProviderFactoryResolver.Instance;

to the SQLiteDbDependencyResolver.GetService method implementation.