Problems Using Entity Framework 6 and SQLite

Problems using Entity Framework 6 and SQLite

Based on magicandre1981's comment, I began to look more closely at the syntax of the provider node. I found that my assembly was a different version than what was specified in the type attribute, though I had not inserted or touched that particular line. By deleting the strong naming, I got .Net to load the library. For reference, here's the new line:

<provider invariantName="System.Data.SQLite" type="System.Data.SQLite.SQLiteProviderServices, System.Data.SQLite.Linq" />

That put me back on track and I was able to match my results with the ones on the blog.

I feel compelled to note, however, that I have decided that SQLite is not a good fit for the Entity Framework, as too many critical functions are missing. I switched over to SQL Server Compact Edition, which I installed via NuGet. A simple tweak to my Connection String and I was running with the full power of Entity Framework. It took less than a minute, compared to the multi-hour slog that was SQLite. I'd recommend switching databases if possible, System.Data.SQLite just isn't ready for the Entity Framework.

Can't get SQLite to work in VS2019 with EF6

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 ;(

SQLite with Entity Framework 6 no such table

Like ErikEJ said does SQLite not support code first in they way I use it, and apparently it has a strict naming policy.

To make it all work I had to manually create the table, and adapt the context to the naming schema (Media => MediaModels).

Context

public DbSet<MediaModel> MediaModels { get; set; }

DAL

public DataAccessLayer(string path)
{
    _path = Path.GetFullPath(path);
    _connectionPath = _path + @"\MyDatabase.sqlite";
    
    using (var db = new MediaContext(_connectionPath))
    {
        db.Database.ExecuteSqlCommand("CREATE TABLE IF NOT EXISTS 'MediaModels' ('Name' TEXT, 'FilePath' TEXT NOT NULL PRIMARY KEY, 'Tags' TEXT, 'Note' TEXT, 'FileExtension' TEXT)");

        db.SaveChanges();
    }
}
    
public void Generate()
{
    using (var db = new MediaContext(_connectionPath))
    {
        db.MediaModels.Add(new MediaModel("test"));
        db.SaveChanges();
    }
}

That code is still not perfect, and I'll try to update this answer as I improve it.

Issue with Entity Framework 6 & Sqlite. Any() query fails but Contains() query is working

Your first statement var hugeDataListObj= dbContext.Table1.Select(x=>x.Field1).ToList(); is using ToList(), will evaluate the query and create a List<> of primitives in memory.

Your 2nd statement(s) use this list to build a SQL statement. It's no wonder that your EF providers are having trouble with this - it's probably attempting to build and execute a SQL statement that has a clause like IN ('a', 'b', 'c', 'd'... which contains thousands of values.

These queries both use the same dbContext. Consider combining the queries into one before execution, which will cause the join to happen in the database.

Better still, because there are conceptual links between these two tables, you can create navigation properties in the entities, which makes it much easier to create EF/Linq queries around these objects. Consult the EF documentation on how to do this.

Entity Framework 6 not creating tables in SQLite database

According to Entity Framework 6 with SQLite 3 Code First - Won't create tables, EF6 doesn't create tables when used with SQLite, so I had to do so myself.

I was able to create a SQLite database using DB Browser for SQLite, and create the tables in it myself. I had to be careful to make sure the structure of the tables I created matched the properties in my Model classes, and to use the [Key] annotation on the Model classes to indicate which field is the primary key field.

Importantly, I had to Add Existing Item in Visual Studio to grab that file and make sure that Visual Studio knew about the file.

Also, importantly, I had to go to the Properties of that file and set its 'Copy to Output Directory' property to 'Copy if Newer', so that the database makes its way to the bin/debug or bin/release directory when I run the app. If I didn't do this, the database would not exist at runtime, which would cause a runtime crash.

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

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

Sqlite and EntityFramework 6 Invalid cast on SqlConnection to EntityConnection

After a couple of hours and differents errors I just find the solution (or at least make it work for the moment)

"Just" change the providerName of the ConnectionString from System.Data.SQLite to System.Data.EntityClient on the app.config file.

Hope this works for the next guy who face similar problems.

Trouble using SQLite 1.0.92 with Entity Framework 6.1

Are you sure you have your app.config setup properly? I'm using EF6.1 with System.Data.SQLite v1.0.92.0 using the following 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" />
  </configSections>
  <system.data>
    <DbProviderFactories>
      <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" />
      <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" />
    </DbProviderFactories>
  </system.data>
  <entityFramework>
    <providers>
      <provider invariantName="System.Data.SQLite" type="System.Data.SQLite.EF6.SQLiteProviderServices, System.Data.SQLite.EF6" />
    </providers>
  </entityFramework>
</configuration>

This will need to be in the app.config for the executing program not the app.config for the assembly which contains your DbContext. Alternatively you could use code based configuration as I answered here which you can place in the same assembly as your DbContext.

And also yes if your DbContext is in a different assembly then that assembly will need some code referencing SQLite otherwise the compiler won't include the references to the required SQLite assemblies. You've provided an incorrect reference above to SqlServer rather than SQLite which won't work.

Entity Framework 6 with SQLite 3 Code First - Won't create tables

Unfortunately, the EF6 provider implementation in System.Data.SQLite.EF6 doesn't support creating tables. I downloaded the SQLite source code to have a look but couldn't find anything for creating tables and for migrations. The EF6 provider is basically the same as their Linq implementation so it's all aimed at querying the database rather than modifying it.

I currently do all of my work with SQL Server and generate sql scripts for SQLite using the SQL Server Compact & SQLite Toolbox. The scripts can then be run using an SQLiteCommand to simulate migrations.

Update

In EF7 support for SQL server compact has been dropped and a new provider for SQLite is being developed by the EF team. The provider will use Microsoft's managed SQLite wrapper project, Microsoft.Data.SQLite rather than the System.Data.SQLite project. This will also allow for using EF7 on iOS, Android, Windows Phone / Mobile, Linux, Mac etc. as Microsoft's wrapper is being developed as a portable library.

It's all still in beta but you can get nuget packages from the ASP.Net development feeds at MyGet (dev, master, release) if you wish to have a look. Look for the EntityFramework.SQLite package.

Simple example using System.Data.SQLite with Entity Framework 6

A similar question is asked over here:
Entity Framework 6 with SQLite 3 Code First - Won't create tables

kjbartel gives very useful explanation that table creation is not supported by the EF SQLite Driver.

Also see https://github.com/msallin/SQLiteCodeFirst, which provides an excellent solution. I installed the SQLite.CodeFirst NuGet package, and added the below code, then the app works fine:

    class MyContext : DbContext
    {
        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            var sqliteConnectionInitializer = new SqliteCreateDatabaseIfNotExists<MyContext>(modelBuilder);
            Database.SetInitializer(sqliteConnectionInitializer);
        }
        public DbSet<Person> Persons { get; set; }
    }

---

Related Topics