Entity Framework Code First ignoring specific schema

Question

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.

Odd. Looks like TSQL and therefore SQL Server to me. Is that correct? I've done this plenty with no problems. From my code first book: [Table("Locations", Schema="baga")] public class Destination Remember that for Visual Basic — Julie Lerman, May 22 '12 at 15:28
(doesn't feel like answer..) have you tried something other than "dbo"? Wondering if since "dbo" is default for EF/SQL Server that the SQL Server provider doesn't bother explicitly adding it. — Julie Lerman, May 22 '12 at 15:34
Julie - (Yes, SQL Server) Tried with something other than "dbo" and it creates and specifies the schema just fine! Interesting. Problem is, we *want* our tables in "dbo" Interesting that it's just *expecting* "dbo" — Bob Bland, May 22 '12 at 16:30
Just an quick pick http://stackoverflow.com/questions/2663164/changing-schema-name-on-runtime-entity-framework — Harsh Baid, May 22 '12 at 18:57

Julie Lerman · Accepted Answer · 2012-05-22T22:03:06.143

5

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.)

edited May 22 '12 at 22:03

answered May 22 '12 at 16:59

Julie Lerman

4,602
2
22
21

The problem is that "dbo" isn't the default schema for my user when I log in to SQL Server... but EF seems to be assuming that it is. So, there's a disconnect between what EF assumes is a default schema and what my *real* default schema is (domain\bob.bland). When EF assumes that, it makes it impossible to specify that tables should go in the "dbo" schema. Does that make sense? – Bob Bland May 22 '12 at 18:26
oh! Now I see why it's kinda problematic. There may be a known workaround but I wonder if you can specify it in migrations? – Julie Lerman May 22 '12 at 21:26

score 0 · Answer 2 · answered Jul 02 '12 at 15:18

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

Entity Framework Code First ignoring specific schema

2 Answers2