Relative path in Connection string pointing to different locations

[jwdavidson]

It looks like the changes in aspnet/Hosting #633 (announcement #157) undid the work to resolve aspnet/Microsoft.Data.Sqlite #188.

Using aspnet core 1.0.0 the update-database command creates the database and correctly populates the schema at <appName>/src/<appName>/bin/src/<appName>/bin/Debug/netcoreapp1.0/<dbname>.db

When the MVC app runs it creates an empty database at <appName>/src/<appName>/bin/Debug/netcoreapp1.0/<dbname>.db

The connection string from appsettings.json

....
  "ConnectionStrings": {
    "SqLiteConnection": "Filename=LynxJournal.db"
  },
.....

The updated startup.cs (after #633)

....
        public Startup(IHostingEnvironment env)
        {
            var builder = new ConfigurationBuilder()
                .SetBasePath(env.ContentRootPath)
                .AddJsonFile("appsettings.json", optional: true, reloadOnChange: true)
                .AddJsonFile($"appsettings.{env.EnvironmentName}.json", optional: true);
....
        public void ConfigureServices(IServiceCollection services)
        {
            //// Add framework services.
            services.AddDbContext<LynxJournalDbContext>(options =>
                    options.UseSqlite(Configuration.GetConnectionString("SqliteConnection")));
....

WebRootPath and ContentRootPath do not appear to be available in the ConfigureServices method, nor is there a GetBasePath extension available to mirror the SetBasePath extension.

Is there an approved method to set a relative path for an sqlite database so that an MVC application can be made to work in Windows, Linux and macOS?

[jwdavidson]

A solution to this issue is complicated by what may be another issue.

The connection string setting in Startup.cs is ignored

        public void ConfigureServices(IServiceCollection services)
        {
            string connectionString = "Filename=" + Path.Combine(Environment.ContentRootPath, Configuration.GetConnectionString("SqliteConnection"));
            //// Add framework services.
            //services.AddDbContext<LynxJournalDbContext>(options =>
            //    options.UseSqlServer(Configuration.GetConnectionString("DefaultConnection")));
            services.AddDbContext<LynxJournalDbContext>(options =>
                    options.UseSqlite(connectionString));

            services.AddIdentity<ApplicationUser, IdentityRole>()
                .AddEntityFrameworkStores<LynxJournalDbContext>()
                .AddDefaultTokenProviders();

            services.AddAuthentication(options => options.SignInScheme = CookieAuthenticationDefaults.AuthenticationScheme);

            services.AddMvc();
            services.AddSession(options => {
                options.IdleTimeout = System.TimeSpan.FromMinutes(30);
                options.CookieName = ".LynxJournalSession";
            });

            // Add application services.
            services.AddTransient<IEmailSender, AuthMessageSender>();
            services.AddTransient<ISmsSender, AuthMessageSender>();

            services.AddTransient<IImageService, ImageService>();
            services.AddTransient<IJournalService, JournalService>();

            services.AddSingleton<IConfiguration>(Configuration);
            services.AddSingleton<IHostingEnvironment>(Environment);
        }

Instead the effective string setting seems to come from the OnConfiguring() method in the DbContext class. This requires some setup in the Startup.cs class so that IConfiguration and IHostingEnvironment can be injected into the DbContext constructor. In addition to the ConfigureServices method shown above I have a Startup method as shown with 2 additional properties

        public Startup(IHostingEnvironment env)
        {
            var builder = new ConfigurationBuilder()
                .SetBasePath(env.ContentRootPath)
                .AddJsonFile("appsettings.json", optional: true, reloadOnChange: true)
                .AddJsonFile($"appsettings.{env.EnvironmentName}.json", optional: true);

            if (env.IsDevelopment())
            {
                // For more details on using the user secret store see http://go.microsoft.com/fwlink/?LinkID=532709
                builder.AddUserSecrets();
            }

            builder.AddEnvironmentVariables();
            Configuration = builder.Build();
            Environment = env;
        }
        public IConfigurationRoot Configuration { get; }
        private IHostingEnvironment Environment { get; }

The properties Configuration and Environment are set as Singletons for Dependency Injection into the constructor for the DbContext in use

using System.IO;
using Microsoft.AspNetCore.Hosting;
using Microsoft.AspNetCore.Identity.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Configuration;

namespace LynxJournal.Models
{
    public class LynxJournalDbContext : IdentityDbContext<ApplicationUser>
    {
        private IConfiguration Configuration { get; set; }
        private IHostingEnvironment Environment { get; set; }
        public LynxJournalDbContext(DbContextOptions<LynxJournalDbContext> options, 
                IConfiguration configuration, IHostingEnvironment environment) : base(options) {
            Configuration = configuration;
            Environment = environment;
        }

        public LynxJournalDbContext() {  } // needed for data migration

        protected override void OnConfiguring(DbContextOptionsBuilder builder) {
            string connectionString = "Filename=" + Path.Combine(Environment.ContentRootPath, Configuration.GetConnectionString("SqliteConnection"));

            //    //builder.UseSqlServer("Server=.\\SQL2012Express;Database=LynxJournal;Integrated Security=True;MultipleActiveResultSets=true");
            builder.UseSqlite(connectionString);
        }

    }
}

Now the Environment can read the ContentRootPath and combine that with the Connection string from the appsettings.json file

{
  "ConnectionStrings": {
    "DefaultConnection": "Server=.\\SQL2012Express;Database=LynxJournal;Integrated Security=True;MultipleActiveResultSets=true",
    "SqLiteConnection": "data\\LynxJournal.db",
    "SqLiteWinDevConnection": "Filename=C:\\Users\\jwdav\\Documents\\Visual Studio 2015\\Projects\\LynxJournal\\src\\LynxJournal\\data\\LynxJournal.db",
    "SqLiteWinProdConnection": "Filename=C:\\VirtualDir\\LynxJournal\\data\\LynxJournal.db"
  },
  "Logging": {
    "IncludeScopes": false,
    "LogLevel": {
      "Default": "Debug",
      "System": "Information",
      "Microsoft": "Information"
    }
  }
}

It will now detect the correct ContentRootPath for the executable environment making the other connections strings redundant.

This appears to be a functional workaround for the issue.

[clarkis117]

@jwdavidson It appears that this issue is also affecting my code. I'm currently working on replicating your workaround to see if it solves my problem.

[natemcmaster]

This is likely a bug in Entity Framework, not in Microsoft.Data.Sqlite. To help us debug, we need more info. You can add the verbose option Update-Database -Verbose to see more output.

More info:
When the given path is relative, Microsoft.Data.Sqlite will use the ADO.NET "DataDirectory". In .NET Core, this is set with an environment variable "ADONET_DATA_DIR" and in .NET Framework setting AppDomain.GetData("DataDirectory").

If this datadirectory is not specified, Microsoft.Data.Sqlite uses the application base directory as the relative path's root. In .NET Core, this folder is System.AppContext.BaseDirectory, which is usually the location of your ".deps.json" file. In .NET Framework AppDomain.CurrentDomain.BaseDirectory is typically the folder containing the entry assembly.

See also https://github.com/aspnet/Microsoft.Data.Sqlite/wiki/Connection-Strings for full explanation of relative file paths. See https://github.com/aspnet/Microsoft.Data.Sqlite/blob/1.0.0/src/Microsoft.Data.Sqlite/SqliteConnection.cs#L208-L217 for its implementation.

[clarkis117]

It appears the options.UseSqlite(ConnectionString) method ignores the connection string passed to it. I had to configure the DbContext in it's OnConfiguring method, in order for it to work. However, I was not able to replicate the pathing issues.

[natemcmaster]

@jwdavidson I wasn't able to reproduce the error either, but it's definitely possible that runtime and design-time could be computing two different paths. To help us debug this, add the following lines just inside your startup constructor. Then share the output when you run EF commands vs launching your ASP.NET app.

    public class Startup
    {
        public Startup(IHostingEnvironment env)
        {
            Console.WriteLine("Content root path = " + env.ContentRootPath);
            Console.WriteLine("App base path = " + Microsoft.Extensions.PlatformAbstractions.PlatformServices.Default.Application.ApplicationBasePath);
            Console.WriteLine("Current dir = " + System.IO.Directory.GetCurrentDirectory());

[jwdavidson]

I will be able to get some time Thursday morning and will do it then. If I get an opportunity to do it earlier I will

thanks for all your work

Sent from my iPad

On Aug 2, 2016, at 19:55, Nate McMaster [email protected] wrote:

@jwdavidson I wasn't able to reproduce the error either, but it's definitely possible that runtime and design-time could be computing two different paths. To help us debug this, add the following lines just inside your startup constructor. Then share the output when you run EF commands vs launching your ASP.NET app.

public class Startup
{
    public Startup(IHostingEnvironment env)
    {
        Console.WriteLine("Content root path = " + env.ContentRootPath);
        Console.WriteLine("App base path = " + Microsoft.Extensions.PlatformAbstractions.PlatformServices.Default.Application.ApplicationBasePath);
        Console.WriteLine("Current dir = " + System.IO.Directory.GetCurrentDirectory());

—
You are receiving this because you were mentioned.
Reply to this email directly, view it on GitHub, or mute the thread.

[jwdavidson]

Finally got some time to run a test with the output requested:

Content root path = C:\Users\jwdav\documents\visual studio 2015\Projects\LynxJournal\src\LynxJournal
App base path = C:\Users\jwdav\documents\visual studio 2015\Projects\LynxJournal\src\LynxJournal\bin\Debug\netcoreapp1.0
Current dir = C:\Users\jwdav\documents\visual studio 2015\Projects\LynxJournal\src\LynxJournal

[natemcmaster]

@jwdavidson I'm not sure why update-database would use <appName>/src/<appName>/bin/src/<appName>/bin/Debug/netcoreapp1.0/<dbname>.db. The directories you listed above are what the tooling expects and I haven't been able to reproduce this on my own.

As I can't reproduce and there is a workaround, I'm closing the investigation for now. Feel free to continue discussion. We can reopen if we find this problem is reported by others and/or if we can reproduce it.

FYI the tooling has been completely refactored recently (dotnet/efcore#5334). I'll keep an eye out for this behavior as we finish testing this new version of the tooling.

[natemcmaster]

@jwdavidson we received another report that helped us find the same issue you experienced. It's a problem with ASP.NET Web Tooling in VS. I opened dotnet/efcore#6335 to track this.