Where to put connection string when moving database code to separate assembly

Question

I have a Razor Pages website application and I'd like to move my database code to a separate project/assembly, which is referenced from my main project/assembly.

Creating a new class project and moving the files from my Data and Model folders is straight forward. But I'm not clear on where my connection string goes (currently, it's in the appsettings.json for my main project).

How do I specify the connection string for my class library?

Answer 1

The connection string should be configured in the same project where the services connect to the DbContext, so you can leave the appsettings.json as-is.

It is the configuring project (the one that is setting up all the dependencies) that sets the connection to the DB, and the EF migration tool needs the connection to track and apply changes. Any assembly can be used to store migrations, but the EF tool needs to be invoked for a project with an actual connection.

EF Core uses Dependency Injection to configure services for runtime which includes setting the connection string for any DB. The control of how the DB classes interact with their environment is given to the app that is running it, which allows for the same DB code to be used across multiple instances, states and even providers. EF Core uses the current state & provider of the DB where it is deployed to determine changes and scaffolding, so it can only create migrations with a configured instance of a DbContext connected to a DB (usually the development instance).

When a solution is comprised multiple dependent services with a common data model that will need the same migrations, only one of the projects in the solution should be responsible for managing DB state. Whichever project is chosen, it needs to create a DBContext with a valid connection when it starts up for the EF tools to work. ASP.NET Core uses the Microsoft.Extensions.Configuration package and the UseStartup method to make the configuration simple. This can be your existing UI project that would read the connection string from existing settings and pass it to your CustomDbContext at Startup. The EF Tools CLI will use the default profile (the Green run arrow) to get the settings from launchSettings.json and appSettings.json to connect to the DB. The configuration should be in place in each environment you deploy to so that the migrations can be run from the same configuring project and applied as needed.

You can avoid ASP.NET and create a custom startup class in your data package that runs to apply migrations, but this is a lot of extra work for something already in the box. The DbContext class does have an OnConfiguring method that can be used to set the connection. Placing configuration inside of code limits where the software can run, so it should still be set externally.

The DbContext can be referenced from another package:

The DbContext and Models can be defined in a separate 'pure' project, then referenced in the Startup like so:

    using mysolution.Data.CustomDbContext;
...
     services.AddDbContext<CustomDbContext>(options =>
                    options.UseSqlServer(
                        Configuration.GetConnectionString("defaultConnection")));

Configure the DbContext to run and where to store migrations:

The tricky part is applying migrations if you use them. If your Razor assembly was named 'mysolution_UI' and your data project is 'mysolution_Data'. Move the Data, Models, Migrations to mysolution_Data and add this to the startup in mysolution_UI project:

    services.AddDbContext<CustomDbContext>(options =>
        options.UseSqlServer(
            Configuration.GetConnectionString("defaultConnection")
            , b => b.MigrationsAssembly("mysolution_Data")));

Then when adding or deploying the package, the migrations should be invoked from the 'mysolution_UI' project.

   Add-Migration NewMigration -Project mysolution_UI

This allows multiple projects to use the data package for the entities connected to the same DB, but only one is responsible for maintaining the migrations.