CODEWITHSUNDEEP
entity-frameworkentity-framework-4ef-code-first
Bob Bland
Bob Bland

Reputation: 123

Entity Framework Code First ignoring specific schema

Using Entity Framework 4.3.1 CodeFirst and having no luck getting the migrations or scripts to respect the schema that I want my tables to end up in.

It seems the default behavior (the one that I'm seeing regardless of what I do) is to omit the schema completely from the SQL that actually runs causes tables to be created in the default schema for the user running the migration or script.

My DBAs are telling me that they cannot change my default schema due to the fact that I'm part of an AD group and not a local user, so changing the default schema for the user running (an often recommended workaround) the script is not an option at all.

I've tried using the annotations like this:

[Table("MyTable", Schema = "dbo")]
public class MyTable
{
    public int Id { get; set; }

    public string MyProp1 { get; set; }

    public string MyProp2 { get; set; }
}

And I've also tried using the fluent variant of the same thing:

modelBuilder.Entity<YourType>().ToTable("MyTable", "dbo");

The resultant script (and migrations) ignore the fact that I tried to specify a schema. The script looks like this:

CREATE TABLE [MyTable] (
    [Id] [int] NOT NULL IDENTITY,
    [MyProp1] [nvarchar](max),
    [MyProp2] [nvarchar](max),
    CONSTRAINT [PK_MyTable] PRIMARY KEY ([Id])
)

When there should be a [dbo] tucked in there like this:

CREATE TABLE [dbo].[MyTable] (
    [Id] [int] NOT NULL IDENTITY,
    [MyProp1] [nvarchar](max),
    [MyProp2] [nvarchar](max),
    CONSTRAINT [PK_MyTable] PRIMARY KEY ([Id])
)

Has anyone else had luck in getting Entity Framework to respect the schema? This behavior pretty much kills our ability to use codefirst at all in our enterprise environment.

Reminder: Changing my user to have a different default schema is not an option at all.

Upvotes: 5

Views: 3034

Answers (2)

Tom Chantler
Tom Chantler

Reputation: 14951

I tried all of the stuff that Bob Bland tried with a similar lack of success (I was also using Entity Framework 4.3.1 CodeFirst). Then I changed the generated migration to look like this and it worked. Maybe this will save somebody a few minutes of pain?

So my solution was to generate the migration as normal, then hack it by hand to include dbo. as shown below.

    public override void Up()
    {
        CreateTable(
            "dbo.UploadedFiles", // See? I have added dbo. to the front of my table name :-)
            c => new
                {
                    UploadedFileId = c.Guid(nullable: false, identity: true),
                    // other columns omitted for brevity...
                    FileData = c.Binary(),
                })
            .PrimaryKey(t => t.UploadedFileId);
    }

and the Down bit looks like this

   public override void Down()
    {
        DropTable("dbo.UploadedFiles"); // See? I have added dbo. to the front of my table name here too :-)
    }

Upvotes: 0

Julie Lerman
Julie Lerman

Reputation: 4622

As my comment seems to have answered the quandary, I am recreating it as an answer.

It seems that because the SQL Server provider uses "dbo" as the default schema, it will not explicitly add "dbo" to the TSQL that creates the tables even if you specify that in your configuration.

This answers the basic problem. But now I am curious if dbo is the default, do you (Bob) still have a reason to specify it? It doesn't hurt to have it in the configuration if you just want the default to be obvious to someone reading the code. But is the behavior creating another side-effect?

ADDED: Aha! FIXED IN EF5! :) (I just updated my test project to use EF5RC (against .NET 4.0) and I got "dbo" explicitly in the TSQL for create table.)

Upvotes: 5

Related Questions