Scaffolding is a great way to build an app or microservice around an existing database. Instead of writing "code first" models to match an existing database, you can run dotnet ef dbcontext scaffold ... and get all your tables and views in seconds.

That is, until you get an error like this:

System.InvalidOperationException: A named connection string was used, but the name 'ConnectionStrings:MyConnection' was not found in the application's configuration. Note that named connection strings are only supported when using 'IConfiguration' and a service provider, such as in a typical ASP.NET Core application. See https://go.microsoft.com/fwlink/?linkid=850912 for more information.

I assume you know about scaffolding already, and are using it if you've come here to solve an error. More information can be found here, and all over the web!

There can be multiple causes to the above error, such as:

• your connection string doesn't exist in the configuration provider, or is overwritten by the priority of the providers you use
• you've specified the wrong startup project
• you're using Autofac, and Entity Framework core can't find the connection setup

Let's go into these in a bit more detail.

Connection string and configuration providers

.NET 3 and up use a hierarchical configuration structure with multiple providers. A provider is just the part of the process that gets configuration parameters from some place (such as environment variables, appSettings files, etc.) This all comes for free when you use ConfigureWebHostDefaults() or equivalent in your Program or Startup file.

This is really neat because a simple way to specify a password or secure key without checking it into source code, is to set an environment variable on the server. No more need to use appSettings containing passwords. For example:

appSettings.json

  "ConnectionStrings": {
    "MyConnectionString": "",
  }

appSettings.development.json

  "ConnectionStrings": {
    "MyConnectionString": "Server=.\sqlserver2019;Database=myDevDb;integrated security=True",
  }

appSettings.production.json can be completely empty, because when we run our app:

set ConnectionStrings__MyConnectionString = "Server=prod.sql.server;Database=myProdDb;UID=hello;PWD=world!;integrated security=False"
dotnet run

The advantages to this are:

1. no secrets or passwords in the source code or appSettings files
2. environment variables can often be set securely on Azure or AWS containers and hosts
3. settings can be overridden by changing the environment variable and restarting the app instance, no need to deploy
4. lots more! You can configure this in any way. You could even use a secret in local development to keep your personal password out of the way: dotnet user-secrets set ConnectionStrings:MyConnectionString "Server=..."

So make sure your connection string is not overridden by the hierarchy of the configuration. From highest to lowest that is:

1. Command-line arguments using the Command-line configuration provider.
2. Non-prefixed environment variables using the Non-prefixed environment variables configuration provider.
3. User secrets when the app runs in the Development environment.
4. appsettings.{Environment}.json using the JSON configuration provider. For example, appsettings.Production.json and appsettings.Development.json.
5. appsettings.json using the JSON configuration provider.
6. A fallback to the host configuration

More information here.

Startup Projects

Scaffolding will build and "run" your project so that it gets this configuration order correct. So if you have entity framework models in a separate project to your Program.cs file, you may need to help the scaffold tool out: dotnet ef dbcontext scaffold Name=ConnectionStrings:MyConnectionString Microsoft.EntityFrameworkCore.SqlServer --output-dir Data --force --context MyContext --startup-project ..\My.Api.Project --project ..\My.Database.Project

In your startup code should be some line similar to this:

builder.Services.AddDbContext<MyContext>(options => options.UseSqlServer(connectionString));

Autofac

The final piece to my puzzle was Autofac. I use an Autofac module called from Program.cs to register services and context:

builder.Host.ConfigureContainer<ContainerBuilder>(builder => builder.RegisterModule(new InfrastructureModule { ConnectionString = connectionString }));

And this is enough to throw off the scaffolding tool. To solve this I had to add AddDbContext to my Program.cs, but I don't want to do this for running environments, so I combined the tips above and do this:

scaffold.bat

set MyApp__Scaffold = true
dotnet ef dbcontext scaffold Name=ConnectionStrings:MyConnectionString 
 Microsoft.EntityFrameworkCore.SqlServer --output-dir Data --force --context MyContext --startup-project ..\My.Api.Project --project ..\My.Database.Project`

appSettings.json -- nothing!

Program.cs

    if (builder.Environment.IsDevelopment() && myAppOptions.Scaffold)
    {
        builder.Services.AddDbContext<MyContext >(options => options.UseSqlServer(connectionString));
    }

The scaffold.bat file sets an environment variable which is read in Program.cs, so that when scaffolding, AddDbContext is used, but when running normally, it isn't.

What other errors have you encountered while Scaffolding?