Entity Framework : two database schemas, one without db context

Question

I've been trying to make a connection of two schemas in my application.

I'll explain to you: I have an application that uses its own tables, so I created those with a migration and models and DbContext, then I hosted the tables in a SQL Server database that has other schemas from other applications and everything is ok. The single application connects and receive data.

But my application needs to be connected to one of the other schemas that I have in my SQL Server database, the other schema has the same connection string because they are in the same server.

I also wrote the 2 db context in startup.cs / ConfigureServices

services.AddDbContext<ApplicationDbContext>(options => options.UseSqlServer(_config.GetConnectionString("TrialOrdersConnectionString"), x => x.MigrationsHistoryTable("__MyMigrationsHistory", "trials")));
services.AddScoped(p => new ApplicationDbContext(p.GetService<DbContextOptions<ApplicationDbContext>>()));
//services.AddDbContext<AppDbContext_serie>(options => options.UseSqlServer(_config.GetConnectionString("Serie0ConnectionString")));

But of course, as I don't really have the applicationDbContext of the other schema is not recognized.

I tried to repeat the application db context of the other schema to have the models and call them but in my migration it creates again the database :( and I don´t want that.

I am using .Net Core and Angular.

Edward · Accepted Answer · 2018-12-05 03:13:39Z

For accessing the table in the database, but not in DbContext, you could try Query.

Eg. Database has a table named PersonNotInDbContext which is not exist in DbContext.

Table in database

CREATE TABLE [dbo].[PersonNotInDbContext] (
 [Id]   INT        NOT NULL,
 [Name] NCHAR (10) NULL,
 PRIMARY KEY CLUSTERED ([Id] ASC)
);

Define a new model which mapped the return columns from sql query.

public class TableNotInDbContext
{
     public int Id { get; set; }
     public string Name { get; set; }
}

Configure Query in DbContext

public class ApplicationDbContext : IdentityDbContext<ApplicationUser>
{
public ApplicationDbContext(DbContextOptions<ApplicationDbContext> options)
    : base(options)
{
}
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{

}
public DbSet<TodoItem> TodoItem { get; set; }

protected override void OnModelCreating(ModelBuilder builder)
{
    base.OnModelCreating(builder);

    builder.Query<TableNotInDbContext>();
}
}

Useage

    public async Task<IActionResult> DbQuery()
{
    var result = await _context.Query<TableNotInDbContext>()
                               .FromSql($"Select * From PersonNotInDbContext")
                               .ToListAsync();
    return Ok(result);
}

By Query Types, you will be able to run raw query from DbContext.

hiiru · Accepted Answer · 2018-12-04 19:16:16Z

The way you configure the DI, you have 2 different instances configured for the type ApplicationDbContext. As far as I know the behavior for this will be:

injection for ApplicationDbContext will inject the first one (AddDbContext definition).
injection for IEnumerable<ApplicationDbContext> will inject both contexts.

Note: Not sure if registering a type acts the same way as registering a interface in this case, could also be that the 2nd register overrides the first one.

A workaround might be to create a 2nd DbContext class that inherits from ApplicationDbContext, then setup the 2nd class with the 2nd connectionstring. But you have to know which one you want to use when using DI.

Otherwise a custom DbContextProvider might be a solution that lets you choice which DbContext you use. I posted an example code for one in on this question:

Handling multiple connection strings in asp.net core web api which came as a parameter

Collectives™ on Stack Overflow

Entity Framework : two database schemas, one without db context

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related