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

Question

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?

Check here mehdi.me/ambient-dbcontext-in-ef6. You will manage all dbcontexts in one places. — Erkan Demirel
– Erkan Demirel, Commented Apr 12, 2016 at 14:59

Gert Arnold · Accepted Answer · 2016-04-12 20:48:22Z

68

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)

answered Apr 12, 2016 at 20:48

Gert Arnold

110k36 gold badges218 silver badges319 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Gert Arnold Over a year ago

I don't know if it changed in the meantime, or I was blatantly incorrect, but it's not fluent mapping only. You can also use data annotations, [Table("t_Department", Schema = "school")].

Guilherme Bley Over a year ago

It didn't work with MySQL.

Stas Boyarincev · Accepted Answer · 2024-08-20 07:25:43Z

40

In addition to Gert Arnold's response, you could use a Table attribute on your entity:

using System.ComponentModel.DataAnnotations.Schema;

[Table("t_Department", Schema = "school")]
public class Department
{
    public int Id { get; set; }

    public string Name { get; set; }
}

edited Aug 20, 2024 at 7:25

user23529816

answered Feb 4, 2017 at 8:36

Stas Boyarincev

4,11027 silver badges26 bronze badges

Comments

undefined · Accepted Answer · 2017-02-04 09:02:33Z

12

@GertArnold is spot on with his answer. However for pure syntactic candy you can also do this via a convention to pull the schema from the namespace of your models. We found this useful dealing with multiple schemas

modelBuilder.Types().Configure(e => {
        var schema = e.ClrType.Namespace.Split('.').Last().ToLower();
        var name = entity.ClrType.Name;
        return entity.ToTable(name, schema);
});

the above will take the final component of the namespace and use it as the schema name. This avoids the need for customising the table binding for every entity.

answered Feb 4, 2017 at 9:02

undefined

34.4k23 gold badges135 silver badges203 bronze badges

1 Comment

Philippe Over a year ago

Could you elaborate or give a more complete example?

yu yang Jian · Accepted Answer · 2020-03-04 00:59:03Z

1

In my case, it's possibly that I use DB First to generate my EDMX file, so it doesn't invoke OnModelCreating method.

I finally remove all store:Schema="YourSchema" and Schema="YourSchema" in EDMX file, and I do it by write a bat file with powershell command as below, and execute the bat in the Projec Pre-Build Event:

powershell -Command "$varStr='store:Schema=""abcd""""'; $filePath='%~dp0SomeFolder\SomeFile.edmx'; (gc $filePath) -replace $varStr, '' | Out-File $filePath"

edited Mar 4, 2020 at 0:59

answered Oct 7, 2019 at 4:13

yu yang Jian

7,2938 gold badges61 silver badges88 bronze badges

2 Comments

xr280xr Over a year ago

Why does the schema need to be removed? Why does it generate it this way?

yu yang Jian Over a year ago

store:Schema="YourSchema" will specify specific DB name, so it cannot be generally connect to other DB.

Collectives™ on Stack Overflow

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

4 Answers 4

2 Comments

Comments

1 Comment

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

Comments

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related