Can I change the default schema name in entity framework 4.3 code-first?

Question

Currently I am deploying my application to a shared hosting environment and code-first with migrations has been working great except for one minor hiccup. Everytime I want to push the site I have to use the "Update-Database -script" option because I have to prepend every table name with [dbo] because by default the shared host creates a default schema name that is the same name as the database username.

If I log into my shared host and create a database, I then have to create a user. If I name that user admin, then the tables code-first creates while logged in as admin look something like this "[admin].[BlogPosts]". When the application runs all the tables are created but I get an EF exception because it says "[dbo].[BlogPosts]" is invalid. If I rename the table's schema name to "[dbo]" instead of "[admin]" that fixes it.

To get around this I have to generate a migrations script to be executed manually and add "[dbo]" in front of all the table names because the script only references the tables by their name, not by their schema and their name.

Is there an easy way to get around this? It would be so nice if all I had to do was publish the application and everything just worked. If it wasn't for the schema name discrepancy it would be a one click deploy and everything would be glorious.

You could try running an [`ALTER USER`](http://msdn.microsoft.com/en-us/library/ms176060.aspx) for your admin account and specify `dbo` as the default schema. (This doesn't answer the question as posed, but may be a solution) — Damien_The_Unbeliever, Mar 05 '12 at 07:42
I actually like this solution far better than having to manually specify table mappings. — CatDadCode, Mar 05 '12 at 08:10
The title of this question is "Can I change the default schema name in entity framework 4.3 code-first?" The accepted answer is the one that I feel best answers that question. — CatDadCode, Sep 21 '16 at 07:43

score 147 · Answer 1 · answered Feb 27 '14 at 18:24

147

For those using Entity Framework 6, just use the HasDefaultSchema method:

public class Contexto : DbContext
{
    public DbSet<User> Users { get; set; }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.HasDefaultSchema("MyDefaultDbSchema");
    }
}

answered Feb 27 '14 at 18:24

ferhrosa

1,714
2
11
6

4

Yay! I'm glad they finally added native support. – CatDadCode Feb 27 '14 at 18:51
2

This also works for SQLCE, if you want to leave off the schema entirely. Just use `String.Empty`. – InteXX Aug 06 '14 at 22:16
2

For those wondering, yes you can just add this one line of code in, and as long as you haven't explicitly set a schema on tables, they will all just 'move' when you do an 'add-migration', then 'update-database'. – adudley Feb 05 '16 at 14:23
I had a comment on the marked answer... is there a way to get the value of the default schema? without model builder? – one.beat.consumer Mar 29 '16 at 22:11
Yeah although i forgot that's why i searched again, this one is the correct answer as it move even migration table to this schema... – Hassan Faghihi Dec 19 '16 at 06:20
How to change only schema with default table name. Maybe make ToSchema extension method? – Vlado Pandžić Oct 15 '20 at 10:11

score 39 · Accepted Answer · edited Apr 15 '15 at 13:40

39

You could use the ToTable method to specify the schema name. If you do not specify the schema name, EF will by convention use dbo.

public class MyContext
{
    private string schemaName = "Foo";

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
         modelBuilder.Entity<MyEntity>().ToTable("MyTable", schemaName);
    } 
}

edited Apr 15 '15 at 13:40

jamesSampica

12,230
3
63
85

answered Mar 05 '12 at 07:34

Eranga

32,181
5
97
96

4

This is an alright solution. Just sucks that I have to explicitly map every single entity to a table. I have a lot of them. I wish I could just specify a default schema name. Or I wish that if EF has decided to go with "dbo" then it should explicitly add "dbo" in front of the table names when it runs the SQL. – CatDadCode Mar 05 '12 at 07:39
@AlexFord True. Wish there was a facility to add custom conventions. – Eranga Mar 05 '12 at 07:50
7

I accepted this, but I did not use it. It is indeed a solution, but I chose to go with the easy option and just change the database user's default schema using Damien_The_Unbeliever's option `ALTER USER admin WITH DEFAULT_SCHEMA=dbo`. Now new tables that don't specify a schema will be created under dbo by default. – CatDadCode Mar 05 '12 at 18:51
1

Thank you Alex Ford, After researching 1000 pages, and trying 10 diffrent solutions, i finaly found your comment :D It was the only one working for me, and it was the best solution. – BjarkeCK Jun 16 '12 at 18:38
You can over ride the EF conventions and specify your own schema name. – JBeckton Aug 08 '13 at 21:12
1

@JBeckton Not at the time of this question you couldn't. EF 4.3 code-first was a very early code-first implementation. This question isn't relevant to the latest versions of EF. – CatDadCode Nov 08 '13 at 06:09
In EF7, can I control the schema globally? – Shimmy Weitzhandler Jul 24 '15 at 12:22
A related question - is there a quick way to find out what the default schema is? I understand it is usually 'dbo' but in case it has been set different by a DBA, is there a way to find out what it is for the context in question? – one.beat.consumer Mar 29 '16 at 22:10

BehrouzMoslem · Answer 3 · 2017-10-03T14:39:39.040

In EF Code first, by default, everything is set up based on user access with a managerial access "DBO-Schema" in the SQL Server. But if a specific user is defined to work with a database that is common in shared hosting, then there will no longer be Dbo management access. This time the names of our tables are dbo.tableName, for example, someUser.tableName, and inaccuracy of this point makes it impossible to run the program. To modify and explicitly assign a user connected to a database. If you use metadata, the following method should be used:

[Table("MyTableName", Schema="MySchemaName")]
public class MyClassName
{
 //Other Lines...
}

Or (Whether or not Fluent API is customizable as follows:)

modelBuilder.Entity<Blog>().ToTable("MyTableName", schemaName:"MySchemaName");

Notice the following:

a good reference for study: http://www.c-sharpcorner.com/article/fluent-api-in-code-first-approach/

score 7 · Answer 4 · edited Jan 29 '21 at 02:21

7

For database-first implementations, it's easy. Open the edmx file, right click -> Properties and set the default database schema.

For code-first, this article seems most promising: https://web.archive.org/web/20150210181840/http://devproconnections.com/entity-framework/working-schema-names-entity-framework-code-first-design

edited Jan 29 '21 at 02:21

Cody Gray - on strike

239,200
50
490
574

answered Nov 08 '13 at 02:38

Bill

2,382
2
24
27

2

This question isn't really relevant with the latest versions of EF code-first. This was an issue in EF 4.3 code-first only. I believe 5 and above no longer have this problem. – CatDadCode Nov 08 '13 at 06:15

petrosmm · Answer 5 · 2014-12-30T21:51:24.520

4

I would like to add since this is for C#, I have written one below for VB

Public Class ClientDbContext
Inherits DbContext
Public Property Clients As DbSet(Of Client)

Protected Overrides Sub OnModelCreating(modelBuilder As DbModelBuilder)
    modelBuilder.HasDefaultSchema("dbo")
End Sub
End Class

edited Dec 30 '14 at 21:51

answered Dec 30 '14 at 20:20

petrosmm

528
9
23

Can I change the default schema name in entity framework 4.3 code-first?

5 Answers5

Linked