sql-server asp.net-core-mvc entity-framework-6 dbcontext .net-7.0

ASP.NET Core 7 MVC, EF Core, SQL Server : DbContext connection issue upgrade from .NET 4.8 EF6

I am having problems with a SQL Server connection while upgrading an ASP.NET 4.8 MVC application to an ASP.NET Core 7 MVC application.

When a controller references the MainDbContext, it's value is null. The connection string worked fine with Microsoft.EntityFramework.SqlServer but does not seem to work with Microsoft.EntityFrameworkCore.SqlServer (even with the addition of TrustServerCertificate=True).

Here is the structure. The TestDbConnection was added later to isolate the issue specific to the SQL Server rather than the code.

launchSettings.json with connection strings:

{
    "Logging": {
        "LogLevel": {
            "Default": "Information",
            "Microsoft.AspNetCore": "Warning"
        }
    },
    "AllowedHosts": "*",
    "ConnectionStrings": {
        "TestDbConnection": "Server=(localdb)\\MSSQLLocalDB;Database=TestDb;Trusted_Connection=True;MultipleActiveResultSets=true",
        "MainDbConnection": "data source=MyServer;initial catalog=MainDb;persist security info=True;user id=sa;password=***;MultipleActiveResultSets=True;App=EntityFramework;TrustServerCertificate=True"
    }
}

program.cs with dependency injection

...

var builder = WebApplication.CreateBuilder(args);

var connectionString = builder.Configuration.GetConnectionString("MainDbConnection");

builder.Services.AddDbContext<MainDbContext>(options =>
   options.UseSqlServer(connectionString));

connectionString = builder.Configuration.GetConnectionString("TestDbConnection");

builder.Services.AddDbContext<TestDbContext>(options =>
   options.UseSqlServer(connectionString));
...

MainDbContext.cs inherited DbContext class:

public class MainDbContext: DbContext
{
    public MainDbContext(DbContextOptions<MainDbContext> options) : base(options){}

    public virtual DbSet<Client> Clients { get; set; }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Client>().ToTable("Client");
    }
}

Controller using MainDbContext connection:

...

private readonly MainDbContext db; // returns null

public async Task<IActionResult> Index()
{
    var tmpList = await db.Clients.ToListAsync() // NullReferenceException

    return View(tmpList);
}
...

In order to get some kind of feedback for why the service injection was quietly failing, I made a Microsoft.Data.SqlClient connection in a controller to try and catch some info in case there was more to it than a DbContext issue.

The catch returned the error The certificate chain was issued by an authority that is not trusted, so I set the trust property TrustServerCertificate=True.

After that, the Microsoft.Data.SqlClient was successful.

using Microsoft.Data.SqlClient;

...
using (var conn = new SqlConnection("data source=MyServer;initial catalog=MainDb;persist security info=True;user id=sa;password=***;MultipleActiveResultSets=True;App=EntityFramework;TrustServerCertificate=True"))
{
    try
    {
        conn.Open();
                       
        try
        {
            string queryString = "SELECT * FROM Client";

            using (SqlCommand command = new SqlCommand(queryString, conn))
            {
                SqlDataReader reader = command.ExecuteReader();

                while (reader.Read())
                {
                    Debug.WriteLine(string.Format("{0}", reader[0])); 
                    // successful output...
                    // 1
                    // 2
                    // 3
                    // etc.
                }
            }
        }
        catch (Exception ex)
        {
            Debug.WriteLine("Could not retrieve Clients");
            Debug.WriteLine("  Exception: " + ex.Message);
            Debug.WriteLine("    Inner Exception: " + ex.InnerException);
        }
    }
    catch (Exception ex)
    {
        Debug.WriteLine("Could Not Open Database");
        Debug.WriteLine("  Exception: " + ex.Message);
        Debug.WriteLine("    Inner Exception: " + ex.InnerException);
    }
}

The trust property TrustServerCertificate=True was then set in MainDbConnection and MainDbContext was tested again.

The service injection still returned null.

I was then wondering if there would be a problem connecting to my localdb using the DbContext class, so I duplicated the client table from the SQL Server and put it in a localdb.

Then placed the connection string in the launchSettings.json and created a new TestDbContext.

TestDbContext.cs inherited DbContext class:

...

public class TestDbContext: DbContext
{
    public TestDbContext(DbContextOptions<TestDbContext> options) : base(options) 
    {}

    public virtual DbSet<Client> Clients { get; set; }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Client>().ToTable("Client");
    }
}

Made the call from the new test controller.

Success.

Controller using TestDbContext connection

...

private readonly TestDbContext db; // success

public async Task<IActionResult> Index()
{
  var tmpList = await db.Clients.ToListAsync() // success

  return View(tmpList);
}

...

When researching .NET CORE EF6 connection string problems, it seems that the blanket response is to set encrypt=false and/or TrustServerCertificate=True, the latter which I used for the successful connection using Microsoft.Data.SqlClient. However, after the addition of TrustServerCertificate=True in the launchSettings.json for the MainDbContext, it still did not work.

What is breaking when using the connection string with DbContext vs Microsoft.Data.SqlClient?
Is there a way to produce error messages in the service injection?

Solution

When a controller references the MainDbContext, it's value is null. The connection string worked fine with Microsoft.EntityFramework.SqlServer but does not seem to work with Microsoft.EntityFrameworkCore.SqlServer (even with the addition of TrustServerCertificate=True).

Based on your shared code snippet and description, few inconsistencies has been found which causing the null data or unexpected output.

Program.cs:

First and most important point is your program.cs where you have set a single variable and switching them for both context. This is mainly causing the second context not working. Becuase its would always works for first initialized dbcontext. So instead of that you should use as following:

var connectionMainDbConnection = builder.Configuration.GetConnectionString("MainDbConnection");

builder.Services.AddDbContext<MainDbContext>(options =>
   options.UseSqlServer(connectionMainDbConnection));

var connectionTestDbConnection = builder.Configuration.GetConnectionString("TestDbConnection");

builder.Services.AddDbContext<TestDbContext>(options =>
   options.UseSqlServer(connectionTestDbConnection));

Note: Use separate variable for both connetion string.

Appsettings.json:

Another crucial point is that, your appsettings.json for MainDbConnection has incorrect attribute which is App=EntityFramework;. Unlike asp.net classic app in .NET 7 we don't need to specify this. Instead of that, your appsettings.json should be as following:

"ConnectionStrings": {
    "TestDbConnection": "Server=(localdb)\\ProjectModels;Database=TestDb;Trusted_Connection=True;MultipleActiveResultSets=true",
    "MainDbConnection": "Server=(localdb)\\ProjectModels;Database=MainDb;Trusted_Connection=True;MultipleActiveResultSets=true"
  }

DbContext Constructor:

While initiating dbContext within the controller, you should pass the dbContext instance through the controller constructor which is not seen within your code snippet. Your controller should be as following:

public class HomeController : Controller
    {
       
        private readonly MainDbContext _mainDbContext;
        private readonly TestDbContext _testDbContext;
        public HomeController(MainDbContext mainDbContext, TestDbContext testDbContext)
        {
            _mainDbContext = mainDbContext;
            _testDbContext = testDbContext;
        }
       
        public IActionResult GetClientFromMainDbContext()
        {

            List<string> listData = new List<string>();
            using (var conn = new SqlConnection("Data Source=(localdb)\\ProjectModels;Initial Catalog=MainDb;Integrated Security=True;MultipleActiveResultSets=true"))
            {
                try
                {
                    conn.Open();

                    try
                    {
                       
                        string queryString = "SELECT * FROM Client";

                        using (SqlCommand command = new SqlCommand(queryString, conn))
                        {
                            SqlDataReader reader = command.ExecuteReader();

                            while (reader.Read())
                            {
                                Debug.WriteLine(string.Format("{0}", reader[0]));

                                string Name = reader.GetString(1);
                                string Description = reader.GetString(2);
                                listData.Add(Name);
                                listData.Add(Description);
                            }
                        }
                    }
                    catch (Exception ex)
                    {
                        Debug.WriteLine("Could not retrieve Clients");
                        Debug.WriteLine("  Exception: " + ex.Message);
                        Debug.WriteLine("    Inner Exception: " + ex.InnerException);
                    }
                }
                catch (Exception ex)
                {
                    Debug.WriteLine("Could Not Open Database");
                    Debug.WriteLine("  Exception: " + ex.Message);
                    Debug.WriteLine("    Inner Exception: " + ex.InnerException);
                }
                return Ok(listData);
            }

        }
        public IActionResult GetClientFromTestDbContext()
        {
            var getClient = _testDbContext.Clients.ToList();
            return Ok(getClient);
        }
        public IActionResult GetClientFromMainDbContextEfCore()
        {
            var getClient = _mainDbContext.Clients.ToList();
            return Ok(getClient);
        }
    }

Note: First method is demonstrating the ado.net example by using sqlClient. Rest of the method using entity framework core both your test and main db example.

Output: