Share Sqlite database between asp.net core project and .net core console project with a third data class library project

Question

I have 3 projects in a vs2019 solution which should use the same sqlite database using Entity Framework Core.

First project: an .net core class library with all database models, database context, database repositories and migrations.

Second project: an .net core console application which needs to scan files, retrieve metadata from inet and seed the database with retrieved content. This is done by referencing the first project and use the repositories to access the database.

Third project: an Asp.net core Webapi which need to accept client requests for retrieving, updating, etc data from the same database. This is also done by referencing the first project and using the repositories. This project is the main project and needs to start the second project when needed.

All projects need to be able to get installed on client machines targeting Windows, MacOS and Linux. And be self hosted. It has to act like a content server simular to Plex, Emby and Calibre.

My problem is how to create the datasource option string to be able to use the same db file and have that file copied to the location of the Asp.net core Webapi project.

I now have this in my dbcontext file at the first project:

    public class ApplicationDbContext : DbContext
    {
        public ApplicationDbContext(DbContextOptions<ApplicationDbContext> options) : base(options) { }

        #region DbSets
        public DbSet<Authors> Authors { get; set; }
        ...
        ...

    public class DesignTimeDbContextFactory : IDesignTimeDbContextFactory<ApplicationDbContext>
    {

        public ApplicationDbContext CreateDbContext(string[] args)
        {
            var builder = new DbContextOptionsBuilder<ApplicationDbContext>();
            builder.UseSqlite("Data Source=Chapter.db");
            return new ApplicationDbContext(builder.Options);
        }
    }

And this in my repository class in the first project:

public class GenericRepository<T> : IGenericRepository<T> where T : class
{
    private DbContextOptionsBuilder<ApplicationDbContext> _optionsBuilder;
    public ApplicationDbContext _context = null;
    public DbSet<T> table = null;
    public GenericRepository()
    {
        _optionsBuilder = new DbContextOptionsBuilder<ApplicationDbContext>().UseSqlite("Data Source=Chapter.db");
        this._context = new ApplicationDbContext(_optionsBuilder.Options);
        table = _context.Set<T>();
    }

When I use entity framework to create the database from the migrations in the first project, it creates the Chapter.db file in the main folder of the first project.

In my Asp.net Webapi project i have this to use the database:

public void ConfigureServices(IServiceCollection services)
{
    services.AddDbContext<ApplicationDbContext>(options =>
    options.UseSqlite("Data Source=Chapter.db"));

Do i need this line of code or can i use the repository classes from the first project?

When I build the solution I have all the projects their dll's in the debug folder of the third project but not the chapter.db database file.

How do you copy the db file automaticly in the correct projects output directories when building for debug and release?

And what do I need to use for the Datasource= connection string to be sure that after people install the application it targets the db file (in windows, linux and macos)?

I have read two answers from simular questions to use:

Var SqlitePath = Path.combine(

Environment.GetFolderPath(Environment.SpecialFolder.ApplicationData),
@"<YourAppName>\Chapter.db");

And:

var builder = new SqliteConnectionStringBuilder(connectionString);
builder.DataSource = Path.GetFullPath( Path.Combine(
AppDomain.CurrentDomain.GetData("DataDirectory") as string ?
AppDomain.CurrentDomain.BaseDirectory, builder.DataSource);
connectionString = builder.ToString();

Are these answers having the same outcome? And am I able to use one of these in the asp.net core project aswell as in the .net core console application?

And does that work cross-platform?

Answer 1

I'm a bit late and I'm assuming that you have already figured this out, but I'm writing this answer since it was a top result to one of my google searches.

The issue at hand is pointing to the correct location of the database.

public void ConfigureServices(IServiceCollection services)
{
    services.AddDbContext<ApplicationDbContext>(options =>
    options.UseSqlite("Data Source=Chapter.db"));

This line of code will be creating a database called "Chapter.db" within the project directory of whichever project you are running it in. The solution to the problem was provided at the bottom of your question.

Var SqlitePath = Path.combine(

Environment.GetFolderPath(Environment.SpecialFolder.ApplicationData),
@"<YourAppName>\Chapter.db");

This code will convert the relative path to the database (based on the project directory) to an absolute path (one based on your file system instead, i.e. C:...\Chapter.db or /home/.../Chapter.db). The other blob you provided should do the same thing, but with more lines of code. After reading SqliteConnectionStringBuilder documentation it seems that the later has more functionality allowing for passwords and other fun stuff to be added through code rather than manually put through a string.

Absolute pathing will help out in this scenario as it will allow for you to specify a direct path to the database file you are using. Otherwise, you would need to modify the connection string to use relative pathing for each of your projects which could become complex if they were to ever move to different parts of your file system. It can still be done. For example if both projects are in the same location, appending "../" in front of your database name would suffice in providing the same location to all of your projects.

Using absolute pathing, you could just specify a location like "C:\Chapter.db" and all of your projects would use the database at that location rather than their individual files.