SQLite EF6 programmatically set connection string at runtime

Question

I try to migrate form EF 3.5 to 6 (with SQLite as database). We can not set the connection string in the app config file (this works without problems with ef6). We have to set connection string programmatically at runtime (after user has selected the SQLite file).

Here is our app.config

<?xml version="1.0" encoding="utf-8"?>
<configuration>
  <configSections>
    <!-- For more information on Entity Framework configuration, visit http://go.microsoft.com/fwlink/?LinkID=237468 -->
    <section name="entityFramework" type="System.Data.Entity.Internal.ConfigFile.EntityFrameworkSection, EntityFramework, Version=6.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" requirePermission="false" />
  </configSections>
  <connectionStrings>
    <add name="testContext" connectionString="data source=Data\testdb.sqlite;Foreign Keys=True"
      providerName="System.Data.SQLite" />
  </connectionStrings>
  <entityFramework>
    <defaultConnectionFactory type="System.Data.Entity.Infrastructure.LocalDbConnectionFactory, EntityFramework">
      <parameters>
        <parameter value="v11.0" />
      </parameters>
    </defaultConnectionFactory>
    <providers>
      <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" />
      <remove invariant="System.Data.SQLite.EF6" />
      <add name="System.Data.SQLite" invariant="System.Data.SQLite" description=".Net Framework Data Provider for SQLite (Entity Framework 6)" type="System.Data.SQLite.EF6.SQLiteProviderFactory, System.Data.SQLite.EF6" />
    </DbProviderFactories>
  </system.data>
</configuration>

Here ist the DbContext

public class FirmwareContext : DbContext
{
    public FirmwareContext()
        : this(DEFAULT_CONNECTION)
    {

    }

    public FirmwareContext(string connextionNameOrString)
        : base(connextionNameOrString)
    {

    }
}

If I connect with the connecation name from app.config all works without problems. If I try to pass the connection string with the second constructor this fails

Here is small example

SQLiteConnectionStringBuilder builder =
    factory.CreateConnectionStringBuilder() as SQLiteConnectionStringBuilder;
builder.DataSource = dataSource; // Path to file name of the user
builder.ForeignKeys = true;

var context = new FirmwareContext(builder.ToString());

var context = new FirmwareContext(builder.ToString());
var test = context.Firmware.FirstOrDefault();

I got the following exception ("Schlüsselwort wird nicht unterstützt: 'foreign keys'.") => The key 'foreign key' is nott supported. If I remove the foreign key set, I got the following exception ("The provider did not return a ProviderManifestToken string.") and some inner exceptions.

It seems that the bas(connectionString) build the MSSQL connection string and for SQLite.

How can I make my second constructor campatible with sqlite?

Answer 1

After looking at this further it seems the problem is that there isn't a IDbConnectionFactory defined for SQLite. So another approach would be to define your own factory implementation.

public class SQLiteConnectionFactory : IDbConnectionFactory
{
    public DbConnection CreateConnection(string connectionString)
    {
        return new SQLiteConnection(connectionString);
    }
}

Then replace the defaultConnectionFactory entry in your 'app.config' with something like:

<defaultConnectionFactory type="MyDAL.SQLiteConnectionFactory, MyDAL" />