Core 3.1 Worker Service issue reading existing SQLite3 DB table data

Question

I'm having an issue reading data in a Core 3.1 worker service from an existing SQLite3 DB table using EF.

I added the following NuGet packages to the project:

EntityFrameWork
Microsoft.EntityFrameworkCore.SQLite
System.Data.SQLite.Core
System.Data.SQLite.EF6
System.Data.SQLite.Linq

In the program class I use the option builder to associate the ProcessTestResultsDbContaxt dbSet and entity information to the existing SQLite3 DB table TestResultRequestMessageInfo.

Program Class listed below:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using Microsoft.Extensions.DependencyInjection;
using Microsoft.Extensions.Hosting;
using Microsoft.Extensions.Configuration;
using Microsoft.EntityFrameworkCore;
using static ProcessTestResultsService.ProcessTestResultsDBContext;

namespace ProcessTestResultsService
{
    public class Program
    {
        public static void Main(string[] args)
        {
            CreateHostBuilder(args).Build().Run();
        }

        public static IHostBuilder CreateHostBuilder(string[] args) =>
            Host.CreateDefaultBuilder(args)
                .ConfigureServices((hostContext, services) =>
                {
                    var optionsBuilder = new DbContextOptionsBuilder<ProcessTestResultsDbContext>();
                    optionsBuilder.UseSqlite("C:\\ACOPTesterSQLite3\\ACOPTesterSQLite3.db");
                    services.AddScoped<ProcessTestResultsDbContext>(s => new ProcessTestResultsDbContext(optionsBuilder.Options));
                    services.AddHostedService<Worker>();
                });
    }
}

Below is the ProcessTestResultsDBContext and model classes:

using Microsoft.EntityFrameworkCore;
using System;
using System.Collections.Generic;
using System.Text;

namespace ProcessTestResultsService
{
    public class ProcessTestResultsDBContext
    {
        public class ProcessTestResultsDbContext : DbContext
        {
            public ProcessTestResultsDbContext(DbContextOptions<ProcessTestResultsDbContext> options)
                : base(options)
            {
            }

            public virtual DbSet<TestResultRequestMessage> TestResultRequestMessages { get; set; }

            protected override void OnModelCreating(ModelBuilder modelBuilder)
            {
                modelBuilder.Entity<TestResultRequestMessage>(entity =>
                { 
                    entity.Property(e => e.ID);
                    entity.Property(e => e.TestInfoLogID);
                    entity.Property(e => e.TestResultRequestMessageInfo)
                          .IsUnicode(false);
                    entity.Property(e => e.IsAwaitingResponseFlag);
                    entity.Property(e => e.MessageSentCount);
                    entity.Property(e => e.AddedDateTime);

                });
            }

            protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
            {
                optionsBuilder.EnableSensitiveDataLogging(true);
            }
        }
    }
    public class TestResultRequestMessage
    {
        public int ID { get; set; }
        public int? TestInfoLogID { get; set; }
        public string TestResultRequestMessageInfo { get; set; }
        public int? MessageSentCount { get; set; }
        public bool IsAwaitingResponseFlag { get; set; }
        public DateTime AddedDateTime { get; set; }
    }
}

In the worker class I set a break point at the while statement. When the service hits the break point no testresults are being returned. Any help would be appreciated.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading;
using System.Threading.Tasks;
using Microsoft.Extensions.Hosting;
using Microsoft.Extensions.Logging;
using Microsoft.Data.Sqlite;
using Microsoft.EntityFrameworkCore;
using System.Runtime.CompilerServices;
using Microsoft.Extensions.DependencyInjection;
using static ProcessTestResultsService.ProcessTestResultsDBContext;

namespace ProcessTestResultsService
{
    public class Worker : BackgroundService
    {
       private readonly ILogger<Worker> _logger;

        public Worker(ILogger<Worker> logger, IServiceScopeFactory serviceScopeFactory)
        {
               _logger = logger;
              _serviceScopeFactory = serviceScopeFactory;
        }

        protected override async Task ExecuteAsync(CancellationToken stoppingToken)
        {
            using var scope = _serviceScopeFactory.CreateScope();
            var dbContext = scope.ServiceProvider.GetRequiredService<ProcessTestResultsDbContext>();
            var testresult = dbContext.TestResultRequestMessages
                                .Where(tr => tr.ID == 9);

            while (!stoppingToken.IsCancellationRequested)
            {
                _logger.LogInformation("Worker running at: {time}", DateTimeOffset.Now);
                await Task.Delay(1000, stoppingToken);
            }
        }
    }

Here is a look at the SQLite3 DB table: Here is the data:

You have two databases. Are you querying the right database? — jdweng

Mykezero Mykezero · Accepted Answer · 2020-09-30T15:52:17

The connection string appears to be wrong. I would try something like this instead:

optionsBuilder.UseSqlite(@"Data Source=C:\ACOPTesterSQLite3\ACOPTesterSQLite3.db;");

I'm not 100% sure where the complete documentation for SqlLite connections strings are, but this might be a good guide: https://docs.microsoft.com/en-us/dotnet/standard/data/sqlite/connection-strings

Core 3.1 Worker Service issue reading existing SQLite3 DB table data

1 Answers