CODEWITHSUNDEEP
c#sqlentity-frameworkentity-framework-4ef-code-first
ChevCast
ChevCast

Reputation: 59234

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

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.

Upvotes: 59

Views: 53433

Answers (5)

Bill
Bill

Reputation: 2748

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

Upvotes: 7

BehrouzMoslem
BehrouzMoslem

Reputation: 9713

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: enter image description here

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

Upvotes: 9

Eranga
Eranga

Reputation: 32447

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);
    } 
}

Upvotes: 39

petrosmm
petrosmm

Reputation: 550

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

Upvotes: 4

ferhrosa
ferhrosa

Reputation: 1792

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");
    }
}

Upvotes: 151

Related Questions