How to Pass Full Sqlite Connection String to DbContext

Question

I am trying to pass the full connection string to DbContext constructor as an argument and I get this error:

Unable to complete operation. The supplied SqlConnection does not specify an initial catalog or AttachDBFileName.

And this is what I have tried:

public DatabaseContext() :base(@"Data Source=|DataDirectory|ComponentDatabase.sqlite") {}

---

Problem can't be about anything else but connection string because I was able to connect my database using connection string from App.config like this:

App.config

<?xml version="1.0" encoding="utf-8"?>
<configuration>
  <configSections>
    <section name="entityFramework" type="System.Data.Entity.Internal.ConfigFile.EntityFrameworkSection, EntityFramework, Version=6.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" requirePermission="false" />
    <!-- For more information on Entity Framework configuration, visit http://go.microsoft.com/fwlink/?LinkID=237468 -->
  </configSections>
  <startup>
    <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.5.2" />
  </startup>
  <entityFramework>
    <providers>
      <provider invariantName="System.Data.SQLite" type="System.Data.SQLite.EF6.SQLiteProviderServices, System.Data.SQLite.EF6" />
      <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" />
    </providers>
    <defaultConnectionFactory type="System.Data.Entity.Infrastructure.LocalDbConnectionFactory, EntityFramework">
      <parameters>
        <parameter value="mssqllocaldb" />
      </parameters>
    </defaultConnectionFactory>
  </entityFramework>
  <connectionStrings>
    <!-- use AppDomain.SetData to set the DataDirectory -->
    <add name="MapDbConnectionStr" connectionString="Data Source=|DataDirectory|ComponentDatabase.sqlite" providerName="System.Data.SQLite" />
  </connectionStrings>
  <system.data>
    <DbProviderFactories>
      <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" />
    <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>
  <runtime>
    <assemblyBinding xmlns="urn:schemas-microsoft-com:asm.v1">
      <dependentAssembly>
        <assemblyIdentity name="System.Data.SQLite" publicKeyToken="db937bc2d44ff139" culture="neutral" />
        <bindingRedirect oldVersion="0.0.0.0-1.0.105.2" newVersion="1.0.105.2" />
      </dependentAssembly>
      <dependentAssembly>
        <assemblyIdentity name="System.Data.SQLite.EF6" publicKeyToken="db937bc2d44ff139" culture="neutral" />
        <bindingRedirect oldVersion="0.0.0.0-1.0.105.2" newVersion="1.0.105.2" />
      </dependentAssembly>
    </assemblyBinding>
  </runtime>
</configuration>

DbContext

public DatabaseContext() :base("MapDbConnectionStr") {}

P.S. I know that App.config has a lot of unnecessary lines, yes.

Answer 1

As far as I am aware there isn't a Connection factory for the type of database you are trying to connect to.

You could write your own connection factory:

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

now go and find the entry for defaulConnectionfactory in app.config and replace the line which specifies the type. At the moment thats going to read something like this:

<defaultConnectionFactory type="System.Data.Entity.Infrastructure.LocalDbConnectionFactory, EntityFramework">

change it to something like this:

<defaultConnectionFactory type="MyNamespace.MySQLiteConnectionFactory, MyAssemblyHere" />

You should now be able to correctly use the Context ctor (string connectionString).

There is another was of doing this without relying on appsettings EF 6 and onwards supports code-based configuration.

So you can do something with configurations that looks a bit like this:

DbConfiguration.Loaded += (_, a) => 
   { 
       a.ReplaceService<DbProviderServices>((s, k) => new MyProviderServices(s)); 
       a.ReplaceService<IDbConnectionFactory>((s, k) => new MyConnectionFactory(s)); 
   };

Full details of this are documented here at microsoft:

Answer 2

Using the name from config file works because it can determine the provider type based on accompanying config provided. When using the connection string directly in the constructor it cannot determine that the connection string is for SQLite and assumes MSSQL so it is trying to use a SqlConnection. Hence the error message you encountered.

Take Note:

The connection to the database (including the name of the database) can be specified in several ways. If the parameterless DbContext constructor is called from a derived context, then the name of the derived context is used to find a connection string in the app.config or web.config file. If no connection string is found, then the name is passed to the DefaultConnectionFactory registered on the Database class. The connection factory then uses the context name as the database name in a default connection string. (This default connection string points to .\SQLEXPRESS on the local machine unless a different DefaultConnectionFactory is registered.) Instead of using the derived context name, the connection/database name can also be specified explicitly by passing the name to one of the DbContext constructors that takes a string. The name can also be passed in the form "name=myname", in which case the name must be found in the config file or an exception will be thrown. Note that the connection found in the app.config or web.config file can be a normal database connection string (not a special Entity Framework connection string) in which case the DbContext will use Code First. However, if the connection found in the config file is a special Entity Framework connection string, then the DbContext will use Database/Model First and the model specified in the connection string will be used. An existing or explicitly created DbConnection can also be used instead of the database/connection name.

Taken from the class remarks for DbContext

The last quoted sentence stands out...

An existing or explicitly created DbConnection can also be used instead of the database/connection name.

You could consider using SQLiteConnection

public class DatabaseContext : DbContext {

    public DatabaseContext() 
        :base(new SQLiteConnection(@"Data Source=|DataDirectory|ComponentDatabase.sqlite"), true) {
        //...
    }

    //...
}