SQLite & Entity Framework 6 "The underlying data provider failed to open"

c# entity-framework-6 sqlite winforms

Question

When my Winforms application reaches the first Entity Framework data "request" I get the following error.

Message: The underlying provider failed to open.
InnerException: Data source cannot be empty.

My connection string is being built dynamically to cater for the user's %appdata% directory path. I am building it at first runtime and updating the app.config with the connection string once it is built. The code that handles this is below:

public void ConstructEntityFrameworkConnectionString(string dbFileName)
{
    var config = ConfigurationManager.OpenExeConfiguration(ConfigurationUserLevel.None);

    if (config.ConnectionStrings.ConnectionStrings["RemManagerDBEntities"] == null)
    {
        // Specify the provider name, server and database.
        var provider = "System.Data.SQLite.EF6";
        var providerName = "System.Data.EntityClient";
        var attacheddbfilename = dbFileName;

        // Initialize the connection string builder for the underlying provider.
        SqlConnectionStringBuilder sqlBuilder = new SqlConnectionStringBuilder {
                AttachDBFilename = attacheddbfilename,
                IntegratedSecurity = true,
                MultipleActiveResultSets = true,
                ConnectTimeout = 30,
                ApplicationName = "RemManagerDBEntities"
            };

        // Build the SqlConnection connection string.
        string providerString = sqlBuilder.ToString();

        // Initialize the EntityConnectionStringBuilder.
        EntityConnectionStringBuilder entityBuilder = new EntityConnectionStringBuilder {
                Provider = provider,
                ProviderConnectionString = providerString,
                Metadata = @"res://*/EntityFramework.RemManagerDBModel.csdl|res://*/EntityFramework.RemManagerDBModel.ssdl|res://*/EntityFramework.RemManagerDBModel.msl"
            };

        var cs = new ConnectionStringSettings("RemManagerDBEntities", entityBuilder.ConnectionString, providerName);

        if (config.ConnectionStrings.ConnectionStrings["RemManagerDBEntities"] == null) 
        {
            config.ConnectionStrings.ConnectionStrings.Add(cs);
            config.Save(ConfigurationSaveMode.Modified);

            ConfigurationManager.RefreshSection("connectionStrings");
        }
    }
}

An example connection string that it creates:

metadata=res://*/EntityFramework.RemManagerDBModel.csdl|res://*/EntityFramework.RemManagerDBModel.ssdl|res://*/EntityFramework.RemManagerDBModel.msl; provider=System.Data.SQLite.EF6; provider connection string="AttachDbFilename=C:\Users\MacbookPro\AppData\Roaming\RemManager\Datasource\RemManagerDB.db; Integrated Security=True; MultipleActiveResultSets=True; Connect Timeout=30; Application Name=RemManagerDBEntities"

The method is called as part of the main method in the program.cs file.

static void Main() 
{
    var conController = new ConnectionController();
    conController.ConstructEntityFrameworkConnectionString($"{Environment.GetFolderPath(Environment.SpecialFolder.ApplicationData)}\\RemManager\\Datasource\\RemManagerDB.db");

    // Start the application
    Application.EnableVisualStyles();
    Application.SetCompatibleTextRenderingDefault(false);
    Application.Run(new RemManager());
}

I've got the following NuGet packages installed:

  • Entity Framework 6
  • System.Data.SQLite
  • System.Data.SQLite.EF6
  • System.Data.SQLite.Core
  • System.Data.SQLite.Linq

Initially I have based the setup procedure from the following site with an amendment made by the second link shown.

https://www.codeproject.com/Tips/1056400/Setting-up-SQLite-and-Entity-Framework-Code-First

https://github.com/ErikEJ/SqlCeToolbox/wiki/EF6-workflow-with-SQLite-DDEX-provider

Below is an example of the calls that I am making to the database via entity framework. When stepping through the code the exception begins on the db.tblGroups.Select line.

public class GroupController : IGroup
{
    public List<tblGroup> SelectAllGroups()
    {
        using (var db = new RemManagerDBEntities())
        {
            return db.tblGroups.Select(g => g).ToList();
        }
    }

  ...

}

The final piece of the puzzle is the app.config. The current config is shown below.

<?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" />
  </configSections>
  <connectionStrings>
    <!-- See Datasource.ConnectionController for the connection string builder.-->
  </connectionStrings>
  <entityFramework>
    <providers>
      <provider invariantName="System.Data.SQLite" type="System.Data.SQLite.EF6.SQLiteProviderServices, System.Data.SQLite.EF6" />
      <provider invariantName="System.Data.SQLite.EF6" type="System.Data.SQLite.EF6.SQLiteProviderServices, System.Data.SQLite.EF6" />
    </providers>
  </entityFramework>
  <appSettings>
    <add key="databaseFilePath" value="" />
    <add key="ClientSettingsProvider.ServiceUri" value="" />
  </appSettings>
       <system.web>
         <membership defaultProvider="ClientAuthenticationMembershipProvider">
           <providers>
             <add name="ClientAuthenticationMembershipProvider" type="System.Web.ClientServices.Providers.ClientFormsAuthenticationMembershipProvider, System.Web.Extensions, Version=4.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" serviceUri="" />
      </providers>
    </membership>
    <roleManager defaultProvider="ClientRoleProvider" enabled="true">
      <providers>
        <add name="ClientRoleProvider" type="System.Web.ClientServices.Providers.ClientRoleProvider, System.Web.Extensions, Version=4.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" serviceUri="" cacheTimeout="86400" />
           </providers>
         </roleManager>
       </system.web>
       <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" />
      <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>
</configuration>

For what else it is worth, entity framework is working from a database first configuration. I am also running Entity Framework 6.2 and SQLite 1.0.106.0.

I've tried my best to find a valid solution on other sites that caters for the fact that I am combining Entity Framework and SQLite, but I haven't been able to come up with much. When I take the SQLite out of the equation, I still haven't been able to come up with much. One of the better questions I had found was here, but I am already doing what the suggested answer recommends.

I had a similar problem earlier, except with localdb. The application has been working previously on the localdb, however this issue has started since I have been endeavouring toward making the application more self contained and user friendly (e.g. not needing to modify the exe.config manually once installed).

Any suggestions would be greatly appreciated.

1
0
1/20/2018 12:59:19 PM

Accepted Answer

You cannot use a SqlConnectionStringBuilder with SQLite, use SQLiteConnectionStringBuilder instead, and set the DataSource property to the database file name

0
1/20/2018 11:49:52 AM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow