Entity Framework Core SQLite Connection String Keyword not supported: version

Question

I created a ASP.NET MVC website using .NET Core 2.2 using a SQLite database. So far it's working well. Trouble begins when I want to add SQLite-specific keywords to the connection string, such as

Data Source=~\\App_Data\\MyDb.db; Version=3; DateTimeFormat=UnixEpoch; DateTimeKind=Utc

Now I get

Keyword not supported: 'version'

I register the database like this

// ConfigureServices(IServiceCollection services)
var conn = Configuration.GetConnectionString("MyDB").Replace("~", _env.ContentRootPath);
services.AddDbContext<MyDBContext>(options => options.UseSqlite(conn));

Then MyDBContext has

public partial class MyDBContext : DbContext
{
    public MyDBContext() { }

    public SatrimonoContext(DbContextOptions<MyDBContext> options)
        : base(options) { }

    public virtual DbSet<Book> Book { get; set; }
}

Then I use it in my page Model

private SatrimonoContext _db;

public BookAccuracyListModel(SatrimonoContext dbContext)
{
    _db = dbContext ?? throw new ArgumentNullException(nameof(dbContext));
}

and from there I can access _db normally via LINQ.

Before posting here, I did plenty of research on the topic, and the best responses I found were this

This provider is Microsoft.Data.Sqlite. Those connection strings are for System.Data.SQLite.

We support the following keywords: Cache, Data Source, Mode.

and this

The issue I was having was because I was trying to create a SqlConnection instead of a SQLiteConnection. Making that change solved my issue.

However, if I'm doing it "right", I'm not creating the SqlConnection and thus can't change it to SQLiteConnection. The other response doesn't include a solution.

So how do I get this to work the right way?

If you found documentation that says that the only keywords supported are x, y and z, why exactly do you think using other keywords will magically work? The only thing I can think of for you to do is not to use `Microsoft.Data.Sqlite`, and that will probably mean that you cannot use EF Core — Camilo Terevinto, Mar 24 '19 at 13:31

score 9 · Accepted Answer · answered Mar 24 '19 at 13:45

9

There is a thread in Github regarding the issue.

Microsoft.Data.Sqlite only supports three keywords:

Cache - Private or Shared
Data Source - The database file. Can be a URI filename.
Mode - ReadWriteCreate, ReadWrite, ReadOnly, or Memory.

No other keywords are supported for this namespace, however if you use the keywords you mentioned with System.Data.SQLite namespace, it will work, as they are keywords matched for System.Data.SQLite.

Your two options:

Remove the Version=3 keyword or any other unsupported keyword from the connection string
Change the namespace you use for your SQlite connection.

answered Mar 24 '19 at 13:45

Barr J

10,636
1
28
46

Is System.Data.SQLite compatible with Entity Framework Core? – Etienne Charland Mar 24 '19 at 13:48
Yes, you can add the package to your project, follow this thread: https://stackoverflow.com/questions/39903863/password-protected-sqlite-with-entity-framework-core – Barr J Mar 24 '19 at 13:54
I tried adding System.Data.SQLite.Core and also System.Data.SQLite to the .NET Core 2.2. project and the namespace doesn't become available. Perhaps it's not compatible with .NET Core? – Etienne Charland Mar 24 '19 at 14:17
1

Oh, closing Visual Studio and re-opening fixed it. It bugs sometimes. – Etienne Charland Mar 24 '19 at 16:17

score 5 · Answer 2 · answered Mar 25 '19 at 12:35

Expanding on Barr's response, the solution is to add System.Data.SQLite.Core to the project.

Then replace

var conn = Configuration.GetConnectionString("Satrimono").Replace("~", _env.ContentRootPath);
services.AddDbContext<SatrimonoContext>(options => options.UseSqlite(conn));

with

var connString = Configuration.GetConnectionString("Satrimono").Replace("~", _env.ContentRootPath);
var conn = new SQLiteConnection(connString);
services.AddDbContext<SatrimonoContext>(options => options.UseSqlite(conn));

That's it!

Entity Framework Core SQLite Connection String Keyword not supported: version

2 Answers2