ASP.NET 5, EF 7 and SQLite - SQLite Error 1: 'no such table: Blog'

Question

I followed the Getting Started on ASP.NET 5 guide about Entity Framework 7 and I replaced MicrosoftSqlServer with Sqlite, the only difference in the code is in Startup.cs:

services.AddEntityFramework()
    .AddSqlite()
    .AddDbContext<BloggingContext>(options => options.UseSqlite("Filename=db.db"));

When I run the website and navigate to /Blogs, I get an error:

Microsoft.Data.Sqlite.SqliteException was unhandled by user code
ErrorCode=-2147467259 HResult=-2147467259 Message=SQLite Error 1: 'no such table: Blog' Source=Microsoft.Data.Sqlite
SqliteErrorCode=1 StackTrace: at Microsoft.Data.Sqlite.Interop.MarshalEx.ThrowExceptionForRC(Int32 rc, Sqlite3Handle db) at Microsoft.Data.Sqlite.SqliteCommand.ExecuteReader(CommandBehavior behavior) at Microsoft.Data.Sqlite.SqliteCommand.ExecuteDbDataReader(CommandBehavior behavior) at System.Data.Common.DbCommand.ExecuteReader() at Microsoft.Data.Entity.Query.Internal.QueryingEnumerable.Enumerator.MoveNext() at System.Linq.Enumerable.WhereSelectEnumerableIterator`2.MoveNext() at System.Linq.Enumerable.d__1`2.MoveNext() at System.Linq.Enumerable.WhereSelectEnumerableIterator`2.MoveNext() at Microsoft.Data.Entity.Query.LinqOperatorProvider.ExceptionInterceptor`1.EnumeratorExceptionInterceptor.MoveNext() at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection) at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source) at EFGetStarted.AspNet5.Controllers.BlogsController.Index() in d:\arthur\documents\visual studio 2015\Projects\EFGetStarted.AspNet5\src\EFGetStarted.AspNet5\Controllers\BlogsController.cs:regel 18 InnerException:

I understand this as if there is no table called 'Blog', but when I open the .db file in DB Browser for SQLite, there actually is a table called 'Blog':

Does SQLite require other changes in the code, or is this an error in the SQLite connector for Entity Framework?

Solution 1

It is very likely the database actually being opened by EF is not the file you are opening in DB Browser. SQLite use the process current working directory, which if launched in IIS or other servers, can be a different folder than your source code directory. (See issues https://github.com/aspnet/Microsoft.Data.Sqlite/issues/132 and https://github.com/aspnet/Microsoft.Data.Sqlite/issues/55).

To ensure your db file is in the right place, use an absolute path. Example:

public class Startup
{
    private IApplicationEnvironment _appEnv;

    public Startup(IApplicationEnvironment appEnv)
    {
        _appEnv = appEnv;
    }
    public void ConfigureServices(IServiceCollection services)
    {
        services.AddEntityFramework()
            .AddSqlite()
            .AddDbContext<MyContext>(
                options => { options.UseSqlite($"Data Source={_appEnv.ApplicationBasePath}/data.db"); });
    }
}

Solution 2

I did this and was still having trouble loading the database. I added the following code in the constructor for the database context: Database.EnsureCreated();

---

Now my context file looks like this:

It created a new database on my new Azure hosting site, so if you have a lot of existing data to migrate, this won't work. It worked for me so figured I'd share.

Solution 3

Taken from EF Core documentation...

Run from Visual Studio

To run this sample from Visual Studio, you must set the working directory manually to be the root of the project. Ifyou don't set the working directory, the following Microsoft.Data.Sqlite.SqliteException is thrown: SQLite Error 1: 'no such table: Blogs'.

To set the working directory:

• In Solution Explorer, right click the project and then select Properties.
• Select the Debug tab in the left pane.
• Set Working directory to the project directory.
• Save the changes.

Solution 4

I had this issue on netcoreapp2.0. There's a related issue that may be at fault, but I didn't want to solve it by going to a nightly build.

The solution for me was to create and pass an SqliteConnection instead of using the builder string.

So for this setup:

string id = string.Format("{0}.db", Guid.NewGuid().ToString());

var builder = new SqliteConnectionStringBuilder()
{
    DataSource = id,
    Mode = SqliteOpenMode.Memory,
    Cache = SqliteCacheMode.Shared
};

Compose for the DI like so:

var connection = new SqliteConnection(builder.ConnectionString);
connection.Open();
connection.EnableExtensions(true);
services.AddDbContext<SomeDbContext>(options => options.UseSqlite(connection));

The error I had was using this style of init:

services.AddDbContext<SomeDbContext>(options => options.UseSqlite(builder.ConnectionString));

My scaffolding also has a one-time call to:

var dbContext = serviceScope.ServiceProvider.GetService<SomeDbContext>();
dbContext.Database.OpenConnection();
dbContext.Database.EnsureCreated();

Using this approach all my DI-instantiated copies of SomeDbContext would all point at a valid SQLite db, and that db would have auto-created schema as per my entities.

Solution 5

Looks like things have changed because IApplicationEnvironment has been replaced with IHostingEnvironment.

Removing IApplicationEnvironment \ IRuntimeEnvironment

public class Startup
{
    private IHostingEnvironment _appHost;

    public Startup(IHostingEnvironment appHost)
    {
        _appHost = appHost;
    }
    public void ConfigureServices(IServiceCollection services)
    {
        services.AddEntityFrameworkSqlite()
            .AddDbContext<MyContext>(
                options => { options.UseSqlite($"Data Source={_appHost.ContentRootPath}/data.db"); });
    }
}

View more solutions

Comments

[Elijah Lofgren]

Thank you very much for sharing this answer. I ran into this using ASP.NET Core 1.0. I had my data source specified in appsettings.json and it didn't seem to like the variable so I supplied a hard-coded path like this: "ConnectionStrings": { "DefaultConnection": "Data Source=C:\\aspnetcoresqllitedbs\\data\\WebApplication.db" }

[speciesUnknown]

The last snippet is the actual key to this problem in my case. The SQLite connection behaves differently than the others I've used.

[Vector]

Wow, this was exactly my problem as well. I couldn't figure out why EF Core docs were saying my seed data would be populated, the seed data functions were running, and then any queries afterward would always throw a table does not exist exception! Thank you!!

[vasilisdmr]

By reading your answer @jjoselon I was able to figure out my own problem, so I am writing this comment if someone else is in the position as me. I am using asp.net core 3.1, entity framework core on Mac.I have 3 sub projects under the main one and the SQLite Error appeared every time I tried to insert some data into my table(even though the table existed). In order to fix that right click on the sub project I was running, Run with -> custom configuration and in the field run in directory choose the directory of the corresponding project. Hope that helps someone.

[A.J.Bauer]

Also check that the database file gets copied to the output directory when building (Right click "data.db" -> properties/Copy to Output Directory/copy always) if you want to deploy your local database to the server when publishing.

[nam]

@jjoselon Your suggestion resolved my issue that I spent two days on (thank you). I was using EF Core with startup project of .NET 4.8, .NET Standard Library 2.0 project with SQLite. And, the startup project was not finding the SQLite db. Could you please provide a link to the EF Core documentation that you quoted in your solution above?

[NCCSBIM071]

I followed docs.microsoft.com/en-us/ef/core/get-started/overview/… and this solution "Database.EnsureCreated();" provided by @Michael to get rid of this error. Hope it helps somebody.

[Sold Out]

Also make sure - especially if you work in cross platform environment like me - that you use unix, or windows file path conventions. While on Windows your program may well read paths with both: "\\" such as "/" , on Linux "\\" will likely fail to read your file. And the error message from EF Core is just misleading :)