ASP.NET 5, EF 7 and SQLite - SQLite Error 1: 'no such table: Blog'
asp.net asp.net-core entity-framework entity-framework-core sqlite
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?
Accepted Answer
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"); });
}
}
Popular Answer
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"); });
}
}