Pass different connection strings to IDesignTimeDbContextFactory using Azure Functions with .NET 5 (Isolated)

Question

I have created a new FunctionApp in Visual Studio Version 16.10.0 using the template Azure Functions with .NET 5 (Isolated) and Http trigger.

Following the guide for Design-time DbContext Creation it seems I can only use IDesignTimeDbContextFactory and not application services. I have created a separate question for using HostBuilder.

https://learn.microsoft.com/en-us/ef/core/cli/dbcontext-creation?tabs=vs

Microsoft.EntityFrameworkCore.Tools - Azure Functions with .NET 5 (Isolated) Design-time DbContext Creation with HostBuilder

Getting the connection string from Environment.GetEnvironmentVariable is not a problem at runtime:

public class ApplicationDbContextFactory : IDesignTimeDbContextFactory<ApplicationDbContext>
{
    public ApplicationDbContext CreateDbContext(string[] args)
    {

        var connectionString = Environment.GetEnvironmentVariable("ConnectionStrings:DefaultConnection");
        var optionsBuilder = new DbContextOptionsBuilder<ApplicationDbContext>();
        optionsBuilder.UseSqlServer(connectionString);

        return new ApplicationDbContext(optionsBuilder.Options);
    }
}

local.settings.json:

{
  "ConnectionStrings": {
    "DefaultConnection": "Server=(localdb)\\mssqllocaldb;Database=FunctionApp1.Test;Trusted_Connection=True;MultipleActiveResultSets=true"
  },
  "IsEncrypted": false,
  "Values": {
    "AzureWebJobsStorage": "UseDevelopmentStorage=true",
    "FUNCTIONS_WORKER_RUNTIME": "dotnet-isolated"
  }
}

Function1cs:

var applicationDbContextFactory = new ApplicationDbContextFactory();
using var context = applicationDbContextFactory.CreateDbContext(null);

However using Microsoft.EntityFrameworkCore.Tools Add-Migration InitialCreate in Package Manager Console I get the following error:

System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> System.ArgumentNullException: Value cannot be null. (Parameter 'connectionString')

This is expected since environment variables are not present when running via Package Manager Console. I can not use code like this either:

$ConnectionStrings.DefaultConnection="Server=tcp:mySqlServerStuffxxx"
Add-Migration InitialCreate

Will result in the following error:

The property 'DefaultConnection' cannot be found on this object. Verify that the property exists and can be set.

https://stackoverflow.com/a/67822243/3850405

I can load a connection string like this but in production I would like a different value. Works a lot better with appsettings.json that gets deployed and checked in by default.

IConfigurationRoot configuration = new ConfigurationBuilder()
    .SetBasePath(Directory.GetCurrentDirectory())
    .AddJsonFile("local.settings.json")
    .Build();

var connectionString = configuration.GetConnectionString("DefaultConnection");

https://stackoverflow.com/a/52418717/3850405

I then tried to use -Args as mentioned in MS Docs.

https://learn.microsoft.com/en-us/ef/core/cli/dbcontext-creation?tabs=vs#args

var connectionString = args[0];

With this I could add a new migration and it looked good.

Add-Migration InitialCreate -Args 'Server=(localdb)\\mssqllocaldb;Database=FunctionApp1.Test;Trusted_Connection=True;MultipleActiveResultSets=true'

However when running Update-Database it fails with the following error:

Microsoft.Data.SqlClient.SqlException (0x80131904): A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SNI_PN11, error: 50 - Local Database Runtime error occurred. Specified LocalDB instance name is invalid.

Update-Database -Args 'Server=(localdb)\\mssqllocaldb;Database=FunctionApp1.Test;Trusted_Connection=True;MultipleActiveResultSets=true'

If I either hard code the exact same value or get the value from ConfigurationBuilder everything works with these settings.

var connectionString = "Server=(localdb)\\mssqllocaldb;Database=FunctionApp1.Test;Trusted_Connection=True;MultipleActiveResultSets=true";

Which is the best way to pass different connection strings to IDesignTimeDbContextFactory that works both at runtime and Package Manager Console?

Answer 1

Given that the solution with ConfigurationBuilder works without duplicating the connection string we decided to use that solution and then only use ApplicationDbContextFactory for design and never in code.

public class ApplicationDbContextFactory : IDesignTimeDbContextFactory<ApplicationDbContext>
{
    public ApplicationDbContext CreateDbContext(string[] args)
    {
        IConfigurationRoot configuration = new ConfigurationBuilder()
            .SetBasePath(Directory.GetCurrentDirectory())
            .AddJsonFile("local.settings.json")
            .Build();

        var connectionString = configuration.GetConnectionString("DefaultConnection");
        var optionsBuilder = new DbContextOptionsBuilder<ApplicationDbContext>();
        optionsBuilder.UseSqlServer(connectionString);

        return new ApplicationDbContext(optionsBuilder.Options);
    }
}

Then got another error when Entity Framework was moved to its own class library.

Your target project 'FunctionApp1.FunctionApp' doesn't match your migrations assembly 'FunctionApp1.EntityFramework'. Either change your target project or change your migrations assembly. Change your migrations assembly by using DbContextOptionsBuilder. E.g. options.UseSqlServer(connection, b => b.MigrationsAssembly("FunctionApp1.FunctionApp")). By default, the migrations assembly is the assembly containing the DbContext. Change your target project to the migrations project by using the Package Manager Console's Default project drop-down list, or by executing "dotnet ef" from the directory containing the migrations project.

Solved this by setting Default Project in PMC as the Entity Framework project and Startup project as the project containing local.settings.json.