'No such table' after Deploying ASP.Net Core API with SQLite DB on Linux

UPDATE: I was able to locate the measurements.db file in the root path. It does not have a Measurements-Table like the error-message says, which propably causes it.

Replacing it with my local file - fixes it and the program works as intended.

So my question changes: Why does it not create the Measurements-Table inside the db-file?

Hey there,

First of all the project runs fine locally! I am having the issue that after publishing (with the command dotnet publish -c Release -r debian-x64), moving the files of the publish-folder to the Linux VM and launching with dotnet MessdatenController.ddl that it is unable to create the measurements.db file by itself which propably results in the following error. (Nor does it find it, when adding/uploading it manualy).

info: Microsoft.Hosting.Lifetime[0]
      Now listening on: http://localhost:5000
info: Microsoft.Hosting.Lifetime[0]
      Now listening on: https://localhost:5001
info: Microsoft.Hosting.Lifetime[0]
      Application started. Press Ctrl+C to shut down.
info: Microsoft.Hosting.Lifetime[0]
      Hosting environment: Production
info: Microsoft.Hosting.Lifetime[0]
      Content root path: /
info: Microsoft.AspNetCore.Hosting.Diagnostics[1]
      Request starting HTTP/1.1 GET http://IP.OF.LINUX.VM/api/Measurements/GetMeasurements
info: Microsoft.AspNetCore.Routing.EndpointMiddleware[0]
      Executing endpoint 'MessdatenController.Controllers.MeasurementsController.GetMeasurements (MessdatenController)'
info: Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker[3]
      Route matched with {action = "GetMeasurements", controller = "Measurements"}. Executing controller action with signature Microsoft.AspNetCore.Mvc.ActionResult`1[System.Collections.Generic.IEnumerable`1[MessdatenController.Models.Measurement]] GetMeasurements() on controller MessdatenController.Controllers.MeasurementsController (MessdatenController).
info: Microsoft.EntityFrameworkCore.Infrastructure[10403]
      Entity Framework Core 3.1.9 initialized 'MeasurementsContext' using provider 'Microsoft.EntityFrameworkCore.Sqlite' with options: None
fail: Microsoft.EntityFrameworkCore.Database.Command[20102]
      Failed executing DbCommand (21ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      SELECT "m"."MeasurementId", "m"."PM10", "m"."PM25", "m"."Posted", "m"."Timestamp"
      FROM "Measurements" AS "m"
      ORDER BY "m"."Timestamp"
fail: Microsoft.EntityFrameworkCore.Query[10100]
      An exception occurred while iterating over the results of a query for context type 'MessdatenController.Business.MeasurementsContext'.
      Microsoft.Data.Sqlite.SqliteException (0x80004005): SQLite Error 1: 'no such table: Measurements'.
         at Microsoft.Data.Sqlite.SqliteException.ThrowExceptionForRC(Int32 rc, sqlite3 db)
         at Microsoft.Data.Sqlite.SqliteCommand.PrepareAndEnumerateStatements(Stopwatch timer)+MoveNext()
         at Microsoft.Data.Sqlite.SqliteCommand.GetStatements(Stopwatch timer)+MoveNext()
         at Microsoft.Data.Sqlite.SqliteDataReader.NextResult()
         at Microsoft.Data.Sqlite.SqliteCommand.ExecuteReader(CommandBehavior behavior)
         at Microsoft.Data.Sqlite.SqliteCommand.ExecuteDbDataReader(CommandBehavior behavior)
         at System.Data.Common.DbCommand.ExecuteReader()
         at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReader(RelationalCommandParameterObject parameterObject)
         at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable`1.Enumerator.InitializeReader(DbContext _, Boolean result)
         at Microsoft.EntityFrameworkCore.Storage.Internal.NoopExecutionStrategy.Execute[TState,TResult](TState state, Func`3 operation, Func`3 verifySucceeded)
         at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable`1.Enumerator.MoveNext()
Microsoft.Data.Sqlite.SqliteException (0x80004005): SQLite Error 1: 'no such table: Measurements'.
   at Microsoft.Data.Sqlite.SqliteException.ThrowExceptionForRC(Int32 rc, sqlite3 db)
   at Microsoft.Data.Sqlite.SqliteCommand.PrepareAndEnumerateStatements(Stopwatch timer)+MoveNext()
   at Microsoft.Data.Sqlite.SqliteCommand.GetStatements(Stopwatch timer)+MoveNext()
   at Microsoft.Data.Sqlite.SqliteDataReader.NextResult()
   at Microsoft.Data.Sqlite.SqliteCommand.ExecuteReader(CommandBehavior behavior)
   at Microsoft.Data.Sqlite.SqliteCommand.ExecuteDbDataReader(CommandBehavior behavior)
   at System.Data.Common.DbCommand.ExecuteReader()
   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReader(RelationalCommandParameterObject parameterObject)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable`1.Enumerator.InitializeReader(DbContext _, Boolean result)
   at Microsoft.EntityFrameworkCore.Storage.Internal.NoopExecutionStrategy.Execute[TState,TResult](TState state, Func`3 operation, Func`3 verifySucceeded)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable`1.Enumerator.MoveNext()
info: Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker[2]
      Executed action MessdatenController.Controllers.MeasurementsController.GetMeasurements (MessdatenController) in 1061.5466ms
info: Microsoft.AspNetCore.Routing.EndpointMiddleware[1]
      Executed endpoint 'MessdatenController.Controllers.MeasurementsController.GetMeasurements (MessdatenController)'
fail: Microsoft.AspNetCore.Server.Kestrel[13]
      Connection id "0HM4PTAT76L67", Request id "0HM4PTAT76L67:00000001": An unhandled exception was thrown by the application.
Microsoft.Data.Sqlite.SqliteException (0x80004005): SQLite Error 1: 'no such table: Measurements'.
   at Microsoft.Data.Sqlite.SqliteException.ThrowExceptionForRC(Int32 rc, sqlite3 db)
   at Microsoft.Data.Sqlite.SqliteCommand.PrepareAndEnumerateStatements(Stopwatch timer)+MoveNext()
   at Microsoft.Data.Sqlite.SqliteCommand.GetStatements(Stopwatch timer)+MoveNext()
   at Microsoft.Data.Sqlite.SqliteDataReader.NextResult()
   at Microsoft.Data.Sqlite.SqliteCommand.ExecuteReader(CommandBehavior behavior)
   at Microsoft.Data.Sqlite.SqliteCommand.ExecuteDbDataReader(CommandBehavior behavior)
   at System.Data.Common.DbCommand.ExecuteReader()
   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReader(RelationalCommandParameterObject parameterObject)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable`1.Enumerator.InitializeReader(DbContext _, Boolean result)
   at Microsoft.EntityFrameworkCore.Storage.Internal.NoopExecutionStrategy.Execute[TState,TResult](TState state, Func`3 operation, Func`3 verifySucceeded)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable`1.Enumerator.MoveNext()
   at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
   at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
   at MessdatenController.Controllers.MeasurementsController.GetMeasurements() in C:\Users\michu\Desktop\Wissenschaftspreis-2021\MessdatenController\MessdatenController\Controllers\MeasurementsController.cs:line 23
   at lambda_method(Closure , Object , Object[] )
   at Microsoft.Extensions.Internal.ObjectMethodExecutor.Execute(Object target, Object[] parameters)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ActionMethodExecutor.SyncObjectResultExecutor.Execute(IActionResultTypeMapper mapper, ObjectMethodExecutor executor, Object controller, Object[] arguments)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.InvokeActionMethodAsync()
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Next(State& next, Scope& scope, Object& state, Boolean& isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.InvokeNextActionFilterAsync()
--- End of stack trace from previous location where exception was thrown ---
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Rethrow(ActionExecutedContextSealed context)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Next(State& next, Scope& scope, Object& state, Boolean& isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.InvokeInnerFilterAsync()
--- End of stack trace from previous location where exception was thrown ---
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeFilterPipelineAsync>g__Awaited|19_0(ResourceInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeAsync>g__Logged|17_1(ResourceInvoker invoker)
   at Microsoft.AspNetCore.Routing.EndpointMiddleware.<Invoke>g__AwaitRequestTask|6_0(Endpoint endpoint, Task requestTask, ILogger logger)
   at Microsoft.AspNetCore.Authorization.AuthorizationMiddleware.Invoke(HttpContext context)
   at Microsoft.AspNetCore.Server.Kestrel.Core.Internal.Http.HttpProtocol.ProcessRequests[TContext](IHttpApplication`1 application)
info: Microsoft.AspNetCore.Hosting.Diagnostics[2]
      Request finished in 1312.107ms 500

Routing via Apache should work correctly, because if reaches the controller-methods.

Now regarding my files:

Program.cs remains default.

Startup.cs

public class Startup
    {
        public Startup(IConfiguration configuration)
        {
            Configuration = configuration;
        }

        public IConfiguration Configuration { get; }

        // This method gets called by the runtime. Use this method to add services to the container.
        public void ConfigureServices(IServiceCollection services)
        {
            services.AddDbContext<MeasurementsContext>(options => options.UseSqlite("Data Source=measurements.db"));

            services.AddTransient<IMeasurementsService, MeasurementsService>();

            services.AddControllers();
        }

        // This method gets called by the runtime. Use this method to configure the HTTP request pipeline.
        public void Configure(IApplicationBuilder app, IWebHostEnvironment env)
        {
            if (env.IsDevelopment())
            {
                app.UseDeveloperExceptionPage();
            }
    
            app.UseRouting();

            app.UseAuthorization();

            app.UseEndpoints(endpoints =>
            {
                endpoints.MapControllers();
            });
        }
    }

Measurement.cs

public class Measurement
    {
        public Measurement(PostMeasurement pm, DateTime posted)
        {
            this.Timestamp = pm.Timestamp;
            this.PM10 = pm.PM10;
            this.PM25 = pm.PM25;
            this.Posted = posted;
        }

        public Measurement(Guid measurementId, DateTime timestamp, double pM10, double pM25, DateTime posted)
        {
            MeasurementId = measurementId;
            Timestamp = timestamp;
            PM10 = pM10;
            PM25 = pM25;
            Posted = posted;
        }

        public Guid MeasurementId { get; set; }

        public DateTime Timestamp { get; set; }

        public Double PM10 { get; set; }

        public Double PM25 { get; set; }

        public DateTime Posted { get; set; }
    }

MeasurementsContext.cs:

public class MeasurementsContext : DbContext
    {
        public MeasurementsContext(DbContextOptions<MeasurementsContext> options) : base(options)
        { }
        public DbSet<Measurement> Measurements { get; set; }
    }
}

SERVICES

public interface IMeasurementsService
    {
        public Measurement AddMeasurement(PostMeasurement measurement, DateTime posted);
        public ICollection<Measurement> AddMeasurements(IEnumerable<PostMeasurement> measurements, DateTime posted);
        public IQueryable<Measurement> GetAllMeasurements();
        public IQueryable<Measurement> GetMeasurementsBetweenTimestamps(DateTime start, DateTime end);
    }

its Implementation

public class MeasurementsService : IMeasurementsService
    {
        private readonly MeasurementsContext _context;

        public MeasurementsService(MeasurementsContext context)
        {
            _context = context;
        }

        public Measurement AddMeasurement(PostMeasurement measurement, DateTime posted)
        {
            Measurement m = new Measurement(measurement, posted);
            _context.Add(m);
            _context.SaveChanges();

            return m;
        }

        public ICollection<Measurement> AddMeasurements(IEnumerable<PostMeasurement> measurements, DateTime posted)
        {
            List<Measurement> result = new List<Measurement>();

            foreach (PostMeasurement postM in measurements)
            {
                Measurement tempM = new Measurement(postM, posted);
                _context.Add(tempM);
                result.Add(tempM);
            }
            _context.SaveChanges();

            return result;
        }

        public IQueryable<Measurement> GetAllMeasurements()
        {
            return _context.Measurements.OrderBy(m => m.Timestamp);
        }

        public IQueryable<Measurement> GetMeasurementsBetweenTimestamps(DateTime start, DateTime end)
        {
            return _context.Measurements.Where(r => r.Timestamp > start && r.Timestamp < end).OrderBy(m => m.Timestamp);
        }

    }

I left out the controller as it should not be important, when taking a look at my problem.

Any advice is appreciated and feel free to ask me anything - I'll reply as fast as possible

The SQLite provider for EntityFramework will not create the database or add missing tables automatically for you. You can call dbContext.Database.EnsureCreatedAsync() to make EF create the database file if it does not exist. It will then also create all the required tables. However, if the file already exists, it will not modify it.

If you want to add additional tables to your database at a later time, you should consider using migrations which allow you to evolve your database schema over time while providing means to migrate from older versions of a database to the current schema.

If you have migrations set up, you can call dbContext.Database.EnsureMigratedAsync() to apply pending migrations to the database and make sure that the database matches the model you expect.

You can also create the database or apply migrations using the dotnet ef command line utility.