SqlServer Entityframework connectionstring not working as expected

[SaMaxNet]

Heads up: .Net Aspire and WebApps are new to me. So i might make some assumptions which are wrong.

I am trying to create a .Net Aspire application which should connect to an existing SqlServer database via EntityFramework. However, I have the problem that the configured connection string is not passed on correctly or the communication between database manager and API does not work properly. Since there is no 100% example for my use case, I have taken parts from various examples and documentation and tried to use them.
eShopLite Sample
MSDN Documenation .NET Aspire SqlServer Entity Framework Core component

All my code examples refer to a simple test app that I have created: TestApp
To execute this, only the ConnectionString in the project "AspireDBTestApplication.PeopleDbManager" in the file "appsettings.Development.json" has to be changed.

As I understand it, by passing the DBManager to the API service via ".WithReference", the API should either be able to interact directly with the DBContext that has already been created. Or the API should have access to the connection string.

Code from my test app:

var builder = DistributedApplication.CreateBuilder(args);

var dbmanager = builder.AddProject<Projects.AspireDBTestApplication_PeopleDbManager>("dbmanager");

var apiService = builder.AddProject<Projects.AspireDBTestApplication_ApiService>("apiservice")
    .WithReference(dbmanager);

builder.AddProject<Projects.AspireDBTestApplication_Web>("webfrontend")
    .WithReference(apiService);

builder.Build().Run();

However, during execution, an attempt is made to create a new DBContext with the connection string defined in the API project in the "appsettings.Development.json" file. However, as in the eShopLite-Sample appsettings, this is only a placeholder.

I hope someone can push me in the right direction or clear up some misunterstandings.

Maybe the discussion at #1525 is relevant. But i think the problem they have is something different.

[christiannagel]

@SaMaxNet see the documentation about the SQL Server component:
https://learn.microsoft.com/en-us/dotnet/aspire/database/sql-server-component
or the EF Core SQL Server component:
https://learn.microsoft.com/en-us/dotnet/aspire/database/sql-server-entity-framework-component

With the AppHost, configure SqlServer, with the service use AddSqlServerDbContext or AddSqlServer, the configuration from the apphost is forwarded.

You can also check my sample code using SQL Server with EF Core, and Azure Cosmos DB:
https://github.com/PacktPublishing/Pragmatic-Microservices-with-CSharp-and-Azure/blob/main/ch11/Codebreaker.AppHost/Program.cs

[SaMaxNet]

@christiannagel I used the 2 documentations you provided. But they are not that helpful because they create a new sql-container. Im trying to connect to a not container based static sqlserver. As by my testsproject it seems the configuration is not forwarded.

Or am i getting something wrong and using VolumenMount like in your example will still connect to my static sqlserver

.WithVolumeMount("volume.codebreaker.sql", "/var/opt/mssql", VolumeMountType.Named)
        .AddDatabase("CodebreakerSql");

[christiannagel]

@SaMaxNet - I see. You're right, WithVolumeMount is the configuration for a Docker container running SQL Server.
To connect to an external SQL Server, use this with AppHost:

var sql = builder.AddSqlServer("sql")
                 .AddDatabase("sqldata");

And in the AppHost project, specify the connection string with appsettings.json:

{
  "Aspire": {
    "Microsoft": {
      "EntityFrameworkCore": {
        "SqlServer": {
          "ConnectionString": "YOUR_CONNECTIONSTRING",
          "DbContextPooling": true,
          "HealthChecks": false,
          "Tracing": false,
          "Metrics": true
        }
      }
    }
  }
}

Running this code with the AppHost, you can check the dashboard and look into the environment variable passed to the apiservice. The connection string is available here.

var builder = DistributedApplication.CreateBuilder(args);

var sql = builder.AddSqlServer("sql").AddDatabase("sqldata");var sql = builder.AddSqlServer("sql")
                 .AddDatabase("sqldata");

var apiService = builder.AddProject<Projects.AspireDBTestApplication_ApiService>("apiservice")
    .WithReference(sql);

builder.AddProject<Projects.AspireDBTestApplication_Web>("webfrontend")
    .WithReference(apiService);

With this, in you apiservice you can use AddSqlServerDbContext which retrieves the connection string.

[SaMaxNet]

@christiannagel Thank you for your response. I tried the codesnippets you provided but they dont seem to work.

var sql = builder.AddSqlServer("sql")
                 .AddDatabase("sqldata");

This code will always create a containered SQL-Server. Eather this is just how this works or i need something else configured so it uses the connectionstring from the appsettings

Also this doesnt provide the connectionstring to my DbManager as the connectionstring is pointing to the container.
This is the environment variable passed to the DbManager

[davidfowl]

Are you trying to use a pre-existing sql server instance?

[SaMaxNet]

Yes im trying to use a pre-existing sqlserver instance

[davidfowl]

In preview 3 you can just set it directly:

var builder = DistributedApplication.CreateBuilder(args);

var sql = new ConnectionString("sql");

var apiService = builder.AddProject<Projects.AspireDBTestApplication_ApiService>("apiservice")
    .WithReference(sql);

builder.AddProject<Projects.AspireDBTestApplication_Web>("webfrontend")
    .WithReference(apiService);

That will look for a connection string in the apphost's configuration.

In the preview 4, it'll look like:

var builder = DistributedApplication.CreateBuilder(args);

var sql = builder.AddConnectionString("sql");

var apiService = builder.AddProject<Projects.AspireDBTestApplication_ApiService>("apiservice")
    .WithReference(sql);

builder.AddProject<Projects.AspireDBTestApplication_Web>("webfrontend")
    .WithReference(apiService);

[SaMaxNet]

@davidfowl Thank you for your reply. This worked for me after another change.
I needed to Change my connectionstring in the appsettings from:

"Aspire": {
    "Microsoft": {
      "EntityFrameworkCore": {
        "SqlServer": {
          "ConnectionString": "Data Source=SOME-SERVER;Initial Catalog=SOME-DATABASE;Persist Security Info=True;User ID=SOME-USER;Password=SOME-PASSWORD;Encrypt=False;Trust Server Certificate=True",
          "DbContextPooling": true,
          "HealthChecks": false,
          "Tracing": false,
          "Metrics": true
        }
      }
    }
  }

to this

"ConnectionStrings": {
  "sql": "Data Source=SOME-SERVER;Initial Catalog=SOME-DATABASE;Persist Security Info=True;User ID=SOME-USER;Password=SOME-PASSWORD;Encrypt=False;Trust Server Certificate=True"
}

Maybe there is then still some issue but i dont know

[davidfowl]

There are multiple app settings. Which one did you set?

Regardless, it seems like you are unblocked?

[SaMaxNet]

I used the appsettings inside the AppHost project.

Yes im unblocked now. This can be marked as resolved or move to discussions