Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Entity Framework Core SQLite Connection String Keyword not supported: version

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?

like image 230
Etienne Charland Avatar asked Mar 24 '19 13:03

Etienne Charland


2 Answers

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!

like image 61
Etienne Charland Avatar answered Sep 23 '22 12:09

Etienne Charland


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:

  1. Remove the Version=3 keyword or any other unsupported keyword from the connection string
  2. Change the namespace you use for your SQlite connection.
like image 29
Barr J Avatar answered Sep 22 '22 12:09

Barr J