Entity Framework Code Ignoring a Specific Schema First

Question

Using Entity Framework 4.3.1 CodeFirst and no luck getting migration or scripts to adhere to the scheme in which I want my tables to be in.

It seems that the default behavior (the one that I see no matter what I do) is to completely omit the schema from SQL that actually runs, causing the creation of tables in the default schema for the user running the migration or script.

My database administrators tell me that they cannot change my default schema because I am part of an AD group and not a local user, so I am changing the default schema for the user (often a recommended workaround) the script is not at all option.

I tried using 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 also tried using the free version of the same:

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

The result of the script (and migration) ignores 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]) ) 

If there should be [dbo] like this:

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

Is anyone else lucky that the Entity Framework respects the schema? This behavior greatly kills our ability to use codefirst in general in our corporate environment.

Reminder: changing my user to use a different default scheme is not an option.

Answer 1

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

It appears that since the SQL Server provider uses “dbo” as the default schema, it will not explicitly add “dbo” in TSQL, which creates tables even if you specify it in your configuration.

This answers the underlying problem. But now I'm curious if dbo is the default value, do you (Bob) still have a reason to specify it? This will not hurt to have it in the configuration if you just want it to be obvious by default, someone is reading the code. But is behavior creating another side effect?

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

Answer 2

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

So my solution was to generate the migration as usual and then hack it manually to enable 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 is as follows

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