How to get the data from two different connection string using same context class

Question

I have the following connection string in my appsettings.json file:

{

"ConnectionStrings": {

"DefaultConnection": "Server=testServer;TrustServerCertificate=True;Initial Catalog=test;uid=test;pwd=test;"

},

"Logging": {

"LogLevel": {

  "Default": "Information",

  "Microsoft.AspNetCore": "Warning"

}

},

"AllowedHosts": "*"

}

This is what I have in my Program.cs file:

using Microsoft.EntityFrameworkCore;

var builder = WebApplication.CreateBuilder(args);

// Add services to the container.

builder.Services.AddControllersWithViews();

builder.Services.AddDbContext<test.Models.testContext>(options =>

{

options.UseSqlServer(builder.Configuration.GetConnectionString("DefaultConnection"));

});

The above connection string works fine and I can get the data using entityframework core. I need to get some data from a different database and same server using the same context class. I defined that connection string in my appsettings.json file like this:

{
  "ConnectionStrings": {
    "DefaultConnection": "Server=test;TrustServerCertificate=True;Initial Catalog=test;uid=test;pwd=test;"
    "DefaultConnection1": "Server=test1;TrustServerCertificate=True;Initial Catalog=test1;uid=EmpRecUser;pwd=test1;"
  },
  "Logging": {
    "LogLevel": {
      "Default": "Information",
      "Microsoft.AspNetCore": "Warning"
    }
  },
  "AllowedHosts": "*"
}

How can I get the data from this different connection string using the same context. I have the following context class:

using System;
using System.Collections.Generic;
using Microsoft.EntityFrameworkCore;

namespace test.Models;

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

    public testContext (DbContextOptions<testContext > options)
        : base(options)
    {
    }
   public virtual DbSet<Category> Categories { get; set; }

Answer 1

the context does not support two databases even if the same server. you need two dbcontext instances initialized with different connect strings. you can either use an injected factory, or have the second inherit from the first to have two type.

as you are using sqlserver, you can create a view in the primary database that selects from the secondary. you could than add a dbset for this view(s). this has the advantage of cross database joins being done by sqlserver rather than the client.