sqlite on .net core publish to azure fails

Question

I'm trying to publish a code-first webapp that uses sqlite to Azure. Now, the application is working fine locally. but when I publish, it seems that the database file is not being created properly, and I get the following error:

SqliteException: SQLite Error 1: 'no such table: Blogs'. Microsoft.Data.Sqlite.Interop.MarshalEx.ThrowExceptionForRC(int rc, Sqlite3Handle db)

I am not sure as to why the Blogs table isn't created (and I assume the rest aren't created either).

my ApplicationDbContext.cs looks like this:

public class ApplicationDbContext : IdentityDbContext<ApplicationUser>
{
    public ApplicationDbContext(DbContextOptions<ApplicationDbContext> options)
        : base(options)
    {

    }

    public ApplicationDbContext()
    {
        Database.EnsureCreated();
        Database.Migrate();
    }

    protected override void OnModelCreating(ModelBuilder builder)
    {
        base.OnModelCreating(builder);
    }

    public DbSet<Publication> Publications { get; set; }
    public DbSet<Blog> Blogs { get; set; }
    public DbSet<Student> Students { get; set; }
    public DbSet<ApplicationUser> ApplicationUser { get; set; }
}

Configuration:

    public void ConfigureServices(IServiceCollection services)
    {
        // Add framework services.
        services.AddDbContext<ApplicationDbContext>(options =>
            options.UseSqlite(Configuration.GetConnectionString("DefaultConnection"))
        );
    }

and DefaultConnection in appsettings.json:

  "ConnectionStrings": {
    "DefaultConnection": "Filename=.\\mydb.sqlite"
  },

Furthermore, when I download mydb.sqlite and open it, its completely empty.

You can't run `EnsureCreated` and `Migrate` at the same time. You must use one or the other. `EnsureCreated` creates a new table each time the model is changed (or if there is non yet - cant remember). Migrate always looks at the migrations and applies it. But when `EnsureCreated` creates the DB `Migrate` usually fails since the tables are already there. Also don't do such actions in the constructor, it's a blocking operation. Constructs should be **Fast** — Tseng, Apr 07 '17 at 05:56
@Tseng I will test your suggestion shortly, but on my computer this works. Is there any reason this works differently on azure? — Shiran Dror, Apr 07 '17 at 07:40
Check the logs, maybe you got an exception during creation/migration — Tseng, Apr 07 '17 at 08:48
Nothing, just `2017-04-07T09:15:07 PID[5312] Information DeploymentComplete` — Shiran Dror, Apr 07 '17 at 09:15
try changing the path in your conn string to a sub folder. make the folder if necessary. — Sentinel, Apr 07 '17 at 14:42

score 2 · Accepted Answer · edited May 23 '17 at 12:34

@Tseng your instinct about EnsureCreated was right. I ended up solving this by clearing the constructor and adding this to the end of the Configure method in Startup.cs

    var serviceScopeFactory = app.ApplicationServices.GetRequiredService<IServiceScopeFactory>()
    using (var serviceScope = serviceScopeFactory.CreateScope())
    {
        var dbContext = serviceScope.ServiceProvider.GetService<ApplicationDbContext>();
        dbContext.Database.EnsureCreated();
    }

I found the answer here

sqlite on .net core publish to azure fails

1 Answers1