.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;
            }

How about you comment out the public ModelContext() method? Keep ModelContext(DbContextOptions<ModelContext> options) only. — Ben Lam
– Ben Lam, Commented Jul 26, 2020 at 2:50
@Bemn I tried commenting that out but it needs it. Couldn't build without it. — Caverman
– Caverman, Commented Jul 26, 2020 at 5:01
There is nothing wrong with your code so far. Please show the code/class, that uses the ModelContext class, including how it is getting the instance of the class, e.g. by using constructor dependency injection, or GetService() or new ModelContext(). It looks like you are not using the same instance of the ModelContext class that you have setup before with your services.AddDbContext<ModelContext>() call. — lauxjpn
– lauxjpn, Commented Jul 26, 2020 at 9:49

Caverman · Accepted Answer · 2020-07-27 02:10:31Z

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;
                    }
                }
}

Guilherme Martin · Accepted Answer · 2020-07-26 03:41:59Z

0

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"]);
        });
    }

answered Jul 26, 2020 at 3:41

Guilherme Martin

8571 gold badge12 silver badges22 bronze badges

1 Comment

Caverman Over a year ago

I like this way of getting the ConnectionString and will probably keep it but it didn't fix the issue.

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related