>VS 2017 and ASP.Net Core 2.1 and Entity framework - I don't wan't to hard code the connection string

Question

I have an ASP.Net Core 2.1 using Entity framework with an Angular 5 front-end and Web Api controller for the back-end.

It works fine as is but now I wan to change it so the database connection string is NOT hard coded.

I am following this: https://learn.microsoft.com/en-us/ef/core/miscellaneous/connection-strings

But it does not work. I get:

An unhandled exception occurred while processing the request. InvalidOperationException: No database provider has been configured for this DbContext. A provider can be configured by overriding the DbContext.OnConfiguring method or by using AddDbContext on the application service provider. If AddDbContext is used, then also ensure that your DbContext type accepts a DbContextOptions object in its constructor and passes it to the base constructor for DbContext.

'((Microsoft.EntityFrameworkCore.Internal.InternalDbSet)db.TblEmployee).Local' threw an exception of type 'System.InvalidOperationException'

The logic paths is:

1. The home page appears. I then click on the "Current Employees" menu item.
2. It goes into the Angular service and executes the getEmployees() method which executes the web api method.
3. It goes to the Web api controller and executes the - GetAllEmployee() method which executes the employee data access layers method.
4. It goes to the employee data access layer class (I instantiate the dbContext here). I have a break point on the return statement. If I hover over the return statement I see the error. And of course when I continue, the app fails.

My database context class is:

namespace Angular5NetcoreEF.Models
{
public partial class DBAngular5NetcoreEFContext : DbContext
{
    public DBAngular5NetcoreEFContext()
    {
    }

    public DBAngular5NetcoreEFContext(DbContextOptions<DBAngular5NetcoreEFContext> options)
        : base(options)
    {
    }

    public virtual DbSet<TblCities> TblCities { get; set; }
    public virtual DbSet<TblEmployee> TblEmployee { get; set; }

    //protected override void OnConfiguring(DbContextOptionsBuilder 
    optionsBuilder)
    //{
    //    if (!optionsBuilder.IsConfigured)
    //    {
    //        optionsBuilder.UseSqlServer("Server=  
    //        (localdb)\\mssqllocaldb;Database=DBAngular5NetcoreEF;
    //        Trusted_Connection=True; MultipleActiveResultSets=true");
    //    }
    //} 

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<TblCities>(entity =>
        {
            entity.HasKey(e => e.CityId);

            entity.ToTable("tblCities");

            entity.Property(e => e.CityId).HasColumnName("CityID");

            entity.Property(e => e.CityName)
                  .IsRequired()
                  .HasMaxLength(20)
                  .IsUnicode(false);
        });

        modelBuilder.Entity<TblEmployee>(entity =>
        {
            entity.HasKey(e => e.EmployeeId);

            entity.ToTable("tblEmployee");

            entity.Property(e => e.EmployeeId).HasColumnName("EmployeeID");

            entity.Property(e => e.City)
                  .IsRequired()
                  .HasMaxLength(20)
                  .IsUnicode(false);

            entity.Property(e => e.Department)
                  .IsRequired()
                  .HasMaxLength(20)
                  .IsUnicode(false);

            entity.Property(e => e.Gender)
                  .IsRequired()
                  .HasMaxLength(6)
                  .IsUnicode(false);

            entity.Property(e => e.Name)
                  .IsRequired()
                  .HasMaxLength(20)
                  .IsUnicode(false);
        });
      } 
   }
}

So per the instructions, I commented out the OnConfiguring method above where I was doing the hard coding.

I added to the appsettings.json file:

{
  "Logging": {
    "LogLevel": {
      "Default": "Warning"
   }
  },
  "ConnectionStrings": {
    "DBAngular5NetcoreEFDatabase": "Server=(localdb)\\mssqllocaldb;Database=DBAngular5NetcoreEF;Trusted_Connection=True;MultipleActiveResultSets=true"
  },
  "AllowedHosts": "*"
}

I added to my Startup.cs - ConfigureServices method :

using Angular5NetcoreEF.Models;
using Microsoft.EntityFrameworkCore;

public void ConfigureServices(IServiceCollection services)
{
    services.AddMvc().SetCompatibilityVersion(CompatibilityVersion.Version_2_1);

    // In production, the Angular files will be served from this directory.
    services.AddSpaStaticFiles(configuration =>
    {
        configuration.RootPath = "ClientApp/dist";
    });

    // I added this.
    services.AddDbContext<DBAngular5NetcoreEFContext>(options => options.UseSqlServer(Configuration.GetConnectionString("DBAngular5NetcoreEFDatabase")));
}

Answer 1

You should not instantiate a new instance of DBAngular5NetcoreEFContext inside EmplyeeDataAccessLayer. Instead, you should inject it.

You also need to register EmployeeDataAccessLayer in DI container and inject it to EmployeeController.

Basically, you let the DI container resolves the dependencies for you.

public class Startup
{
    ...
    public void ConfigureServices(IServiceCollection services)
    {
        services.AddDbContext<DBAngular5NetcoreEFContext>
            (options => options.UseSqlServer(Configuration.GetConnectionString("DBAngular5NetcoreEFDatabase")));

        services.AddScoped<EmployeeDataAccessLayer>();
        ...        
    }
    ...
}

public class EmployeeController : Controller
{
    private readonly EmployeeDataAccessLayer objemployee;

    public EmployeeController(EmployeeDataAccessLayer employeeDataAccessLayer)
    {
        objemployee = employeeDataAccessLayer;
    }
}

public class EmployeeDataAccessLayer
{
    private readonly DBAngular5NetcoreEFContext _db;

    public EmployeeDataAccessLayer(DBAngular5NetcoreEFContext db)
    {
        _db = db;
    }
    ...
}

Another thought is to use interface instead of concrete implementation. It'll make your life easier when you implement unit tests.

Answer 2

the problem you are facing is fact, that you're not using Dependency Injection pattern. First of all you need to insert DbContext from services via dependency injection by constructor by

public class EmployeeDataAccessLayer 
{
   private DBAngular5NetcoreEFContext _db;

   public EmployeeDataAccessLayer(DBAngular5NetcoreEFContext db)
   {
      _db = db;
   }
}

Second, all references should also be injected, so in every layer of the application, for each class like your EmployeeDataAccessLayer you should First: Register it with dependency injection by using in Startup.cs -> ConfigureServices(): i.e.services.AddScoped<EmployeeDataAccessLayer>();, Then inject it into constructor of Controller like in case above.

You can learn about dependency injection and example scopes (Scoped,Transient,Singleton...) from i.e. Doc

For security, in your current scenario to check when you have unconfigured context, you can do something like:

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        if (!optionsBuilder.IsConfigured)
        {
             throw new Exception("Context not configured");
        }
    }

As well as "temporary disable" empty constructor