Entity Framework DB First two contexts pointing to the same database schema

Question

In my application, for performance reasons I want to replicate the database to a separate reporting-only database, and keep my transactional work on the primary database and handle updates through synchronization on the SQL Server side. Using this, I want to offload the very intensive data-heavy database operations to the reporting-only (read-only) database. The two database instances are identical in terms of schema. We are developing entity framework database-first, scaffolding the database tables into our application.

I initially set this up where the reporting context used only the reporting tables, which are table schemas created to model output from the stored procedures with which all the reports are generated. So they were distinct names from the transactional database context and everything worked great. Now, however, we've found some other data-heavy queries that we want to offload to the reporting database as well, so we need to scaffold all of the same tables from the transactional database context to the reporting database context.

Here are my two scaffolding commands:

Scaffold-DbContext "Server=SERVERNAME;Database=DBNAME;User ID=ID;Password=PW;" Microsoft.EntityFrameworkCore.SqlServer -NoOnConfiguring -OutputDir Model\DB -Context TransContext -UseDatabaseNames -f -t tablenames...

Scaffold-DbContext "Server=SERVERNAME;Database=DBNAME;User ID=ID;Password=PW;" Microsoft.EntityFrameworkCore.SqlServer -NoOnConfiguring -OutputDir Model\DB\Reporting -Context ReportsContext -UseDatabaseNames -f -t tablenames...

So the tables are scaffolded into a subfolder for the ReportsContext to allow the duplicate tables without overwriting the ones created by the first scaffolding command.

When I scaffold the same tables from the two different databases into the two contexts, I get the error "'TableName' is an ambiguous reference between AppName.Model.DB.TableName and AppName.Model.DB.Reporting.TableName" for every table. The context you use properly identifies the class/table you're referencing, but the instantiation of the table class doesn't know which copy of the table it's supposed to be referencing. I would really hate to have to fully-qualify every database model class throughout the application, is there an easier way that I'm missing?

is there an easier way that I'm missing? - scaff once and vary the conenction string used by whatever context X is being used by the code? — Caius Jard
– Caius Jard, Commented Feb 25, 2022 at 16:27
Is there any way in the scaffolding command to prepend an identifier onto all the generated table names? So my reporting context would now be full of tables like "RptVisit" and the other context would reference "Visit" but they'd be the same tables. — nonesuch
– nonesuch, Commented Feb 25, 2022 at 17:17
Yes, but that sounds like something you'd use a schema for instead (dbo.Visit, rpt.Visit), but why throw that hurdle in your own way? The two DB are different; why not let them have the same table names, make life easier — Caius Jard
– Caius Jard, Commented Feb 25, 2022 at 17:24
Can you give an example of how you'd do that, varying the connection string in the controller depending on what you're trying to do? It sure seems like a cleaner solution. The two databases are identical. — nonesuch
– nonesuch, Commented Feb 25, 2022 at 17:27
I've been trying very similar thing (in terms of having two db contextes) and got some issues that when create/update is made via write context after a short period of time (~200ms or so) changes are still not available via read context, have you faced anything similar? — Grigoryants Artem
– Grigoryants Artem, Commented Feb 1, 2023 at 8:12

Caius Jard · Accepted Answer · 2022-02-25 18:01:22Z

If you take your DB context, make it abstract (not vital, but stops you new'ing it accidentally) and change its constructor signature:

public abstract partial class MyContext: DbContext
{
    public MyContext(DbContextOptions options): base(options) {}

And then make a couple of new do-nothing contexts that extend it and pass the options through to the base constructor:

public class ReportingMyContext: MyContext 
{
    public ReportingMyContext(DbContextOptions<ReportingMyContext> options) : base(options) { }
}

public class MainMyContext: MyContext
{
    public MainMyContext(DbContextOptions<MainMyContext> options) : base(options) { }

}

And register them with different connection strings:

services.AddDbContext<ReportingMyContext>(options =>
    options.UseSqlServer("Data Source=blah blah blah")
);

services.AddDbContext<MainMyContext>(options =>
    options.UseSqlServer("Data Source=blow blow blow")
);

Then you can get the injector to inject the different context depending on what it sees you use

class ReportsController{
  ReportsController(ReportingMyContext x){
    x.SomeTable.Count() //counts reports db
  }

class UsersController{
  UsersController(MainMyContext x){
    x.SomeTable.Count() //counts main db
  }

There are other ways of varying the connection string, but if you're familiar with and happy with the way getting a context via DI works then it can be handy and simple to just change the connected database by varying the Type of the injected context, or injecting both if you have some heavy reporting/querying need but some updates etc to make too. You might eventually start to add some things that were specific to only one context too.. A similar approach could perhaps be taken with interfaces too, though there's a bit more typing involved in specifying which members of the context are members of the interface so that when you say UsersController(IReportingDbContext x) { x.<thing> then <thing> is actually there without a cast

Other methods such as setting the connection string after you get the context are possible with later EFs..

ListenSoftware Louise Ai Agent · Accepted Answer · 2022-02-25 18:08:04Z

0

when you dependency inject in your controller or class the solver will bind to the correct data context. Ensure the connection strings are unique for each datacontext.

answered Feb 25, 2022 at 18:08

ListenSoftware Louise Ai Agent

4,3432 gold badges31 silver badges39 bronze badges

Collectives™ on Stack Overflow

Entity Framework DB First two contexts pointing to the same database schema

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related