Can't get SQLite to work in VS2019 with EF6

I'm trying all day to get this working, but so far no luck.

I'm building a WinForm application for .NETv4.7.2 I will distribute this application to a client and might need to update it later. For this reason, I would like to enable AutoMigrations. I also want to use CodeFirst.

I'm using the SQLiteCeToolbox of @ErikEJ and installed all as mentioned at http://erikej.blogspot.com/2018/03/using-entity-framework-6-and-sqlite.html

My toolbox settings:

Version 4.7.644.0

SQL Server Compact 4.0 in GAC - Yes - 4.0.8482.1
SQL Server Compact 4.0 DbProvider - Yes

SQL Server Compact 4.0 DDEX provider - No
SQL Server Compact 4.0 Simple DDEX provider - Yes


SQL Server Compact 3.5 in GAC - Yes - 3.5.8080.0
SQL Server Compact 3.5 DbProvider - Yes

SQL Server Compact 3.5 DDEX provider - No

Sync Framework 2.1 SqlCe 3.5 provider - No

SQLite ADO.NET Provider included: 1.0.110.0
SQLite EF6 DbProvider in GAC - Yes
System.Data.SQLite DDEX provider - No
SQLite Simple DDEX provider - Yes

My packages:

<package id="EntityFramework" version="6.3.0" targetFramework="net472" />
<package id="log4net" version="2.0.8" targetFramework="net472" />
<package id="SQLite.EF6.Migrations-Extensible" version="1.0.106" targetFramework="net472" />
<package id="System.Data.SQLite" version="1.0.112.0" targetFramework="net472" />
<package id="System.Data.SQLite.Core" version="1.0.112.0" targetFramework="net472" />
<package id="System.Data.SQLite.EF6" version="1.0.112.0" targetFramework="net472" />
<package id="System.Data.SQLite.Linq" version="1.0.112.0" targetFramework="net472" />

And my App.config

<add name="MySqliteConnection" connectionString="Data Source=D:\dev\foo\bar\db\Mydb.sqlite" providerName="System.Data.SQLite.EF6" />

<entityFramework>
  <providers>
    <provider invariantName="System.Data.SQLite.EF6" type="System.Data.SQLite.EF6.SQLiteProviderServices, System.Data.SQLite.EF6" />
  </providers>
</entityFramework>
<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" />
 </DbProviderFactories>
</system.data>

I've tried numerous variants of App.confg, but I keep getting errors like:

No Entity Framework provider found for the ADO.NET provider with invariant name 'System.Data.SQLite'. 
Make sure the provider is registered in the 'entityFramework' section of the application config file.

Not sure if it is relevant but this is my service, which I call in my (unit)test:

public List<UploadedFile> GetAllUploadedFiles()
{
    using var db = new ApplicationContext();
    return db.UploadedFiles.ToList();
}

This is my ApplicationContext:

public class ApplicationContext : DbContext
{
    static ApplicationContext()
    {
        Database.SetInitializer(new MigrateDatabaseToLatestVersion<ApplicationContext, ContextMigrationConfiguration>(true));
    }

    public ApplicationContext(DbConnection connection) : base(connection, false)
    { }

    public ApplicationContext() : this(MakeConnection())
    {
    }

    private static DbConnection MakeConnection()
    {
        const string dbFilename = "MyDb.sqlite";

        var filePath = Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments), 
            "db", dbFilename);
        if (File.Exists(filePath)) return new SQLiteConnection($"Data Source={filePath};Version=3;");
        // First time, copy empty db from installer. TODO: might be better to create empty db instead:
        var path = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "db", dbFilename);
        if (!File.Exists(path)) throw new FileNotFoundException("Cannot find " + path, path);
        File.Copy(path, filePath, true);
        return  new SQLiteConnection($"Data Source={filePath};Version=3;");
    }

    public DbSet<UploadedFile> UploadedFiles { get; set; }
}

Before installing SQLite.EF6.Migrations-Extensible and using MigrateDatabaseToLatestVersion I got some more progress. My test did work, but the table wasn't created.

So most likely I'm mixing too many different approaches. I'm open to another approach (if it is working of course) that will use Code-First and auto-updates the database of my client.

1 answer

  • answered 2019-11-08 18:36 Paul Meems

    I finally get it working. This app.config is working for me in VS2017 and VS2019:

    <connectionStrings>
      <add name="MySqliteConnection" connectionString="Data Source=D:\dev\foo\bar\db\MyDb.sqlite" providerName="System.Data.SQLite.EF6" />
    </connectionStrings>
    <entityFramework>
      <defaultConnectionFactory type="System.Data.Entity.Infrastructure.LocalDbConnectionFactory, EntityFramework">
        <parameters>
          <parameter value="v13.0" />
        </parameters>
      </defaultConnectionFactory>
      <providers>
        <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" />
        <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.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.112.0" newVersion="1.0.112.0" />
        </dependentAssembly>
        <dependentAssembly>
          <assemblyIdentity name="System.Data.SQLite.EF6" publicKeyToken="db937bc2d44ff139" culture="neutral" />
          <bindingRedirect oldVersion="0.0.0.0-1.0.112.0" newVersion="1.0.112.0" />
        </dependentAssembly>
      </assemblyBinding>
    </runtime>
    

    And I got this working because I created a new project in VS2017 without a separate Test project and with a button on a form to get some data from the database.
    After that, I got back to my larger VS2019 project. Fixed its App.config AND, this is the most important part, removed the App.config from my Test project and linked the one from the main project.

    Hopefully, this will help somebody else as well. I spend most of my day on this ;(