Entity Framework Core Connection String - Environment Variable

Question

I have a .net core app using entity framework core. When running entity framework migrations or updates via the command line, I get a "value cannot be null. Parameter name: connectionString"

The connection string is kept as an environment variable:

ConnectionStrings__SomeContextConnection  ...(localdb)\\MSSQLLocalDB...

However, when I move that exact same connection string into a .json config file:

"ConnectionStrings": {  
    "SomeContextConnection": "...(localdb)\\MSSQLLocalDB..." 
}

Then the entity framework tools recognize the connection string without issue. When debugging the code, in Startup.cs:

var connectionString = _config.GetConnectionString("SomeContextConnection");

the connectionString variable is set to the correct string when the string is stored in either of the two locations, but it crashes when trying to connect to the database when using the environment var.

(note: in the environment variable case, the connection string is escaped so goes from

(localdb)\\MSSQLLocalDB...

to

(localdb)\\\\MSSQLLocalDB...

but the issue persists even after removing the extra back-slashes)

UPDATE: When the connection string is moved into a Windows level environment variable, it works fine. Seems to only be an issue when using Visual Studio environment variables.

Answer 1

I suggest you use a DesignTimeContextFactory class for the migrations:

public class DesignTimeDbContextFactory : IDesignTimeDbContextFactory<MyContext>
{
    AmpContext IDesignTimeDbContextFactory<MyContext>.CreateDbContext(string[] args)
    {
        var connectionString = ConfigHelper.GetConnectionString();

        var optionsBuilder = new DbContextOptionsBuilder<MyContext>();
        optionsBuilder.UseSqlServer(connectionString);

        return new AmpContext(optionsBuilder.Options);
    }
}

the GetConnectionstring for me is like this and I use it throughout my application (that is for my Web API project and for integration tests):

public class ConfigHelper
{
    /// <summary>
    /// Gets the connectionstring from the appsettings.databasestring.json file in the solution root if there is no environment variable to be found
    /// </summary>
    /// <param name="solutionBasePath">Optional to not auto resolve the solution base path</param>
    /// <returns></returns>
    public static string GetConnectionString(string solutionBasePath = null)
    {
       //how to set it on IIS on the server: https://stackoverflow.com/a/36836533/1343595
        var environmentString = Environment.GetEnvironmentVariable("CUSTOMCONNSTR_MyContextDb");

        if (!string.IsNullOrEmpty(environmentString))
            return environmentString;

        if(!string.IsNullOrEmpty(solutionBasePath))
            return GetStringFromConfig(Path.Combine(solutionBasePath, "appsettings.databasestring.json"));

        var filePath = Path.Combine(GetSolutionBasePath(), "appsettings.databasestring.json");

        return GetStringFromConfig(filePath);
    }

    private static string GetStringFromConfig(string filePath)
    {
        IConfigurationRoot config = new ConfigurationBuilder()
            .AddJsonFile(filePath) //you can change the value of the connectionstring in the appsettings file and add it to gitignore so the change will not effect others
            .Build();

        var connectionString = config.GetConnectionString("MyContextDb");
        return connectionString;
    }

    /// <summary>
    /// Gets the current soution base path
    /// </summary>
    /// <returns></returns>
    public static string GetSolutionBasePath()
    {
        var appPath = PlatformServices.Default.Application.ApplicationBasePath;
        var binPosition = appPath.IndexOf("\\bin", StringComparison.Ordinal);
        var basePath = appPath.Remove(binPosition);

        var backslashPosition = basePath.LastIndexOf("\\", StringComparison.Ordinal);
        basePath = basePath.Remove(backslashPosition);
        return basePath;
    }
}

Answer 2

It works if you remove the double backslash after (localdb) so that there's only one backslash.

So once you've corrected that in the environment variable it should look something like this:

Server=(localdb)\MSSQLLocalDB...