.Net Core 3 / Entity Framework Core putting connection string in Appsettings.json not working

Question

Still new to .NET Core but I'm trying to put my connection string in the Appsettings.json file. I'm following the Microsoft document on how to do so and I think I have all my settings correct.

The project will compile and run, but when I hit the API I get an error

No database provider has been configured for this DbContext.

I used the connection string that EF Core put in my context and just commented it out so there is effectively nothing in the DbContext's OnConfiguring. This is connecting to an Oracle database.

ModelContext.cs

public partial class ModelContext : DbContext
{
    public ModelContext()
    {
    }

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

    public virtual DbSet<Customer> Customer{ get; set; }
    public virtual DbSet<HubToken> HubToken{ get; set; }
    public virtual DbSet<Token> Token { get; set; } 

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        //if (!optionsBuilder.IsConfigured)
        //{

        //    //optionsBuilder.UseOracle("user id=myusername;password=mypassword;data source=(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=myserver)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=*****)))");
        //}
    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Token>().HasNoKey();

        modelBuilder.HasAnnotation("Relational:DefaultSchema", "MySCHEMA");
     }

Appsettings.json

 "ConnectionStrings": {
    "DbConnection": "user id=myusername;password=mypassword;data source=(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=servername)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=***)))"
  }

Startup.cs

public void ConfigureServices(IServiceCollection services)
{
     services.AddControllersWithViews();

     services.AddDbContext<ModelContext>(options => {
                options.UseOracle(Configuration.GetConnectionString("DbConnection"));
     });
}

UPDATE

My code where the ModelContext is being called. I initially used the commented out "using" statement where I would just create the context. That's how I do it in .Net Framework but Core seems to be different. Then with some suggestions I tried the using statement trying to pass in the connection string in to the ModelContext but it doesn't like that either because the connection string is not available at that point. I've tried getting the connectionstring first before passing it to the ModelContext but something about that didn't work either, I just don't remember what it was since I've tried several things. I will say that at least at the moment it seems it was much simpler in EF for .Net. The DbContext would take care of getting the connection string from the app.config or web.config for me. Now it seems to be more of an effort to do that in Core but it's probably just because I'm so new withe the way Core works.

//using (var ctx = new ModelContext())
            using (var ctx = new ModelContext(configuration.GetConnectionString("DbConnection")))
            {
                //Run the query and see if there are any results returned.
                var results = ctx.Token.FromSqlRaw(sbQuery.ToString()).ToList();

                if (results == null || results.Count == 0)
                {
                    _logger.LogInformation("Api-Token not found in database. Access denied. Customer: {0} | Token: {1}", customer.ToString(), token.ToString());

                    context.Result = new BadRequestObjectResult("Access Denied. Invalid Token");
                    return;
                }

                if (!context.ModelState.IsValid)
                {
                    _logger.LogInformation("Model is in-valid.");
                    context.Result = new BadRequestObjectResult(context.ModelState);
                }

                _logger.LogInformation("Api-Token is valid.");
                return;
            }

Answer 1

After more researching and playing around I finally got it to work. I'm posting my update so that it will hopefully help someone else down the road.

Added my connectionstring to the appsettings.json file

"ConnectionStrings": {
    "DbConnection": "user id=myusername;password=mypassword;data source=(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=servername)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=***)))"
  }

Then add to the context info to the ConfigurationServices in the Startup.cs file. I believe this adds the information to the Dependency Injection container. I'm using Oracle but you just change to options.UseSqlServer(connstring); if needed. You can verify the correct connectionstring info by putting a break point to see it in debug mode.

public void ConfigureServices(IServiceCollection services)
        {
            //setting connstring separately here just to be able confirm correct value. 
            var connstring = Configuration.GetConnectionString("DbConnection");
            services.AddDbContext<ModelContext>(options =>
            {
                options.UseOracle(connstring);
            });
        }

In your Controller/Class add the context to a page property and use the class constructor to get the DbContext from the DI container. Then use the using statement and reference the local property that has the connectionstring information.

    public class TokenAuthorizationFilter : IAuthorizationFilter
        {
        private readonly ModelContext _context;
                private readonly ILogger<TokenAuthorizationFilter> _logger;
                public TokenAuthorizationFilter(ILogger<TokenAuthorizationFilter> logger, ModelContext context)
                {
                    _logger = logger;
                    _context = context;
                }
                
                public void OnAuthorization(AuthorizationFilterContext context)
                {
                    _logger.LogInformation("Authorizing Api-Token.");
        
                    //Get the values from the Request Header
                    context.HttpContext.Request.Headers.TryGetValue("Api-Token", out var token);
                    context.HttpContext.Request.Headers.TryGetValue("Customer", out var customer);
        
                    var sbQuery = new System.Text.StringBuilder();
                    sbQuery.Append("select * from YourUserTable ");
                    sbQuery.Append("where username=customer and password=token");
        
                    using (_context)
                    {
                        //Run the query and see if there are any results returned.
                        var results = _context.Token.FromSqlRaw(sbQuery.ToString()).ToList();
        
                        if (results == null || results.Count == 0)
                        {
                            _logger.LogInformation("Api-Token not found in database. Access denied. Customer: {0} | Token: {1}", customer.ToString(), token.ToString());
        
                            context.Result = new BadRequestObjectResult("Access Denied. Invalid Token");
                            return;
                        }
        
                        if (!context.ModelState.IsValid)
                        {
                            _logger.LogInformation("Model is in-valid.");
                            context.Result = new BadRequestObjectResult(context.ModelState);
                        }
        
                        _logger.LogInformation("Api-Token is valid.");
                        return;
                    }
                }
}

Answer 2

You can use GetSection method from Configuration class to get your connection string

Startup.cs

    public void ConfigureServices(IServiceCollection services)
    {
        services.AddControllersWithViews();

        services.AddDbContext<ModelContext>(options => {
            options.UseOracle(Configuration.GetSection("ConnectionStrings")["DbConnection"]);
        });
    }