This problem is occurring with a Web API project developed with Visual Studio 2022 in C# and.NET6. The database is PostgreSQL hosted in Azure and I'm using EF Core. Specifically:
Microsoft.EntityFrameworkCore v7.0.2
Npgsql.EntityFrameworkCore.PostgreSQL v7.0.1
I followed the code-first approach to scaffolding the database schema and seeding it with sample data. That all worked fine locally as well. Program.cs looked like this:
var builder = WebApplication.CreateBuilder(args);
// Add services to the container.
builder.Services.AddControllers();
// Learn more about configuring Swagger/OpenAPI at https://aka.ms/aspnetcore/swashbuckle
builder.Services.AddEndpointsApiExplorer();
builder.Services.AddSwaggerGen();
builder.Services.AddDbContext<ContentManagerContext>(options => options.UseNpgsql(builder.Configuration.GetConnectionString("ContentManagerConnection")));
builder.Services.AddScoped<IUserDataFeedRepository, UserDataFeedRepository>();
builder.Services.AddScoped<IUserAppRepository, UserAppRepository>();
var app = builder.Build();
// run db migrations
using (var scope = app.Services.CreateScope())
{
var services = scope.ServiceProvider;
var context = services.GetRequiredService<ContentManagerContext>();
context.Database.Migrate();
}
// Configure the HTTP request pipeline.
if (app.Environment.IsDevelopment())
{
app.UseSwagger();
app.UseSwaggerUI();
}
else
{
app.UseDefaultFiles();
app.UseStaticFiles();
}
app.UseHttpsRedirection();
app.UseAuthorization();
app.MapControllers();
app.Run();
The DbContext class shown above is injected into each of the repository classes, which are in turn injected into the controllers. All dependency injection is a fairly boiler-plate approach.
The connection string referenced above in Program.cs was pulled straight from the database's page in Azure, specifically the "Connection Strings" blade. I chose the one for ADO.NET and it worked like a charm.
Server={my_server};Database={my_database};Port=5432;User Id={my_user_id};Password={my_password};Ssl Mode=VerifyFull;
It was also necessary to add a firewall rule for my local IP address in order to connect to the PostgreSQL database in Azure.
I've had zero issues running the entire solution locally. The API launches in debug mode in VS2022 and functions normally. I can call it's controller endpoints via Postman and a React app without any errors. Data pulled from the database is returned as expected.
Before deploying the API to Azure I added the ADO.NET connection string created by Azure as an Application Setting in the App Services configuration blade.
After a successful publish of the API I got errors calling out the line in Program.cs where EF migrations are applied. As a temporary work-around, I commented this out:
var context = services.GetRequiredService<ContentManagerContext>();
//context.Database.Migrate();
That resolved the first error, but then I got errors indicating the database server wasn't accessible. I had forgotten to add the App Service's external IP addresses (all 7 of them) as firewall rules for the database. But after completing this step the app service was still unable to connect to the database server.
Researching online I saw that some people have Ssl Mode=Require in their connection string whereas I have Ssl Mode=VerifyFull. I changed the connection string in Azure only to be Ssl Mode=Require and subsequently got a successful result in the Diagnostics Tools blade -> Check Connection Strings:
However, even though it's telling me it can connect using the provided connection string, when I make requests to the API I'm immediately getting the following error:
Exception:
System.InvalidOperationException: The ConnectionString property has not been initialized.
at Npgsql.NpgsqlConnection.Open(Boolean async, CancellationToken cancellationToken)
at Npgsql.NpgsqlConnection.Open()
at Microsoft.EntityFrameworkCore.Storage.RelationalConnection.OpenDbConnection(Boolean errorsExpected)
at Microsoft.EntityFrameworkCore.Storage.RelationalConnection.OpenInternal(Boolean errorsExpected)
at Microsoft.EntityFrameworkCore.Storage.RelationalConnection.Open(Boolean errorsExpected)
at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReader(RelationalCommandParameterObject parameterObject)
at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.Enumerator.InitializeReader(Enumerator enumerator)
at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.Enumerator.<>c.<MoveNext>b__21_0(DbContext _, Enumerator enumerator)
at Npgsql.EntityFrameworkCore.PostgreSQL.Storage.Internal.NpgsqlExecutionStrategy.Execute[TState,TResult](TState state, Func`3 operation, Func`3 verifySucceeded)
at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.Enumerator.MoveNext()
at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
I don't understand why Azure would tell me it is able to connect in Diagnostics but then I get a runtime exception saying the connection string couldn't be initialized.
I have tried switching it back to Ssl Mode=VerifyFull in Azure, but when I do that, I get this error in the Diagnostics output:
Currently the only difference between local (where it works) and Azure (where it doesn't) is the Ssl Mode portion of the connection string.
Since the Ssl Mode values supported locally and in Azure are inconsistent, I am wondering what else in the connection string is not supported in Azure? Could it be a forbidden character somewhere, possibly in the password? The connection string's password has multiple special characters.
Thanks for any help you can offer!
