Using Entity Framework 6 with Multiple DB Schemas but using One DBContext

entity-framework dbcontext db-schema
Apollonas picture Apollonas · Apr 12, 2016 · Viewed 20k times · Source

I have an application using EF as ORM. The database used to have one schema, dbo and everything was working fine. I recently organized my tables into 4 different schemas. Some tables of one schema have dependencies on tables that reside on a different schema. All seems to be valid on the SQL side.

On the app side all db interactions through EF are not working anymore. The code compiles, the schemas are visible in the solution, the model mappings point to the right schemas, but once I try to insert a row to a table it does not work.

I have seen a few posts about using multiple schemas will require using multiple DBContexts but I would rather use one DBContext. All my schemas have the same owner dbo and I do not see a reason of using multiple DBContexts.

Does anyone know if there is a way to achieve this?

Answer

Gert Arnold picture Gert Arnold · Apr 12, 2016

You can map each table to its own schema by fluent mapping only. In your DbContext subtype you should override OnModelCreating (if you haven't done so already) and add statements like this:

modelBuilder.Entity<Department>()  
    .ToTable("t_Department", "school");

Entities that you don't map like this explicitly will be placed in the default dbo schema, or you can provide your own default by

modelBuilder.HasDefaultSchema("sales");

(summarized from here)

Related questions

The entity type <type> is not part of the model for the current context

I am getting into the Entity Framework, but I am unsure if I am missing a critical point in the code-first approach. I am using a generic repository pattern based on the code from https://genericunitofworkandrepositories.codeplex.com/ and have …

Read More
How to fix the datetime2 out-of-range conversion error using DbContext and SetInitializer?

I'm using the DbContext and Code First APIs introduced with Entity Framework 4.1. The data model uses basic data types such as string and DateTime. The only data annotation I'm using in some cases is [Required], but that's not on any …

Read More
Entity Framework: One Database, Multiple DbContexts. Is this a bad idea?

My impression to date has been that a DbContext is meant to represent your database, and thus, if your application uses one database, you'd want only one DbContext. However, some colleagues want to break functional areas out into separate DbContext …

Read More