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.

Not sure duplicate but this might helps https://stackoverflow.com/a/35982626/2946329 — Salah Akbari, Sep 08 '17 at 16:33
Nope, it's about missing initial catalog in config file in MSSQL database, this is about passing actual connection string to constructor in Sqlite. AFAIK Sqlite has no inital catalog option. Even if it has, I still can't find the correct form of the string when passing to constructor directly. — Ferit, Sep 08 '17 at 16:46
In case you are working **only** with SQLite database, here is a solution which does not require any configuration files - [EF6, SQLite won't work without App.confg](https://stackoverflow.com/questions/43615926/ef6-sqlite-wont-work-without-app-confg/43688403#43688403) — Ivan Stoev, Sep 11 '17 at 11:01
@IvanStoev Looks like it can fix my problem, gonna try asap, thanks. — Ferit, Sep 11 '17 at 11:28

Nkosi · Answer 1 · 2017-09-11T10:22:09.183

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) {
        //...
    }

    //...
}

Well, I was asking about passing full connection string, not passing a connection object. When you pass a connection object, entity framework can't take full control of it so it can cause small scale problems in future. I prefer to avoid it. — Ferit, Sep 11 '17 at 11:31

krystan honour · Accepted Answer · 2017-09-13T12:41:54.370

3

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:

edited Sep 13 '17 at 12:41

answered Sep 13 '17 at 09:33

krystan honour

6,523
3
36
63

Thank you for answer. Is there a way to totally avoid depending on app.config? I am trying to pass full connection string because I want to avoid using app.config. – Ferit Sep 13 '17 at 09:35
I think I found it, created a class `SQLiteConfiguration: DbConfiguration` in `DbContext` class, but still I get the same `Unable to complete operation. The supplied SqlConnection does not specify an initial catalog or AttachDBFileName.` – Ferit Sep 13 '17 at 10:13
I have augmented the article which should go some way to helping you achieve your aim. – krystan honour Sep 13 '17 at 12:38
2

Finally solved. Here is how: I used your answer in addition to https://stackoverflow.com/questions/43615926/ef6-sqlite-wont-work-without-app-confg/43688403#43688403. And deleted connection string and entity framework sections from app.config (this part is necessary otherwise error persists). – Ferit Sep 13 '17 at 13:41
I've added the other post as a favourite Saibot this is useful – krystan honour Sep 13 '17 at 14:10

score -1 · Answer 3 · answered Sep 12 '17 at 11:03

As i understood correctly it could be helpful, please use builder with db context options. I use SqlServer, hovewer there should be not a lot of changes.

var builder = new DbContextOptionsBuilder<MapDbContext>();
builder.UseSqlServer(ConfigurationManager.ConnectionStrings["MapDbConnectionStr"].ConnectionString), opt => opt.EnableRetryOnFailure());
var mycontext = new MapDbContext(builder.Options);

public MapDbContext(DbContextOptions<MapDbContext> options)
        : base(options)
    { }

Hope it helps, Good luck.

How to Pass Full Sqlite Connection String to DbContext

3 Answers3