EntityFramework code-first with custom default schema database script generation - references dbo

Question

I have a DbContext implementation (data repository pattern), in which I change the default schema, as:

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

        base.OnModelCreating(modelBuilder);
    }

I run the following commands in the Package Manager Console, to generate a .sql script for database creation:

    Enable-Migrations -ConnectionStringName "EfDataRepository"
    Add-Migration Initial -ConnectionStringName "EfDataRepository"
    Update-Database -ConnectionStringName "EfDataRepository" -Script -SourceMigration:0

.. which gives me the following script:

DECLARE @CurrentMigration [nvarchar](max)

IF object_id('[dbo].[__MigrationHistory]') IS NOT NULL
    SELECT @CurrentMigration =
        (SELECT TOP (1) 
        [Project1].[MigrationId] AS [MigrationId]
        FROM ( SELECT 
        [Extent1].[MigrationId] AS [MigrationId]
        FROM [dbo].[__MigrationHistory] AS [Extent1]
        WHERE [Extent1].[ContextKey] = N'xxxxxxxx'
        )  AS [Project1]
        ORDER BY [Project1].[MigrationId] DESC)

IF object_id('[tm].[__MigrationHistory]') IS NOT NULL
    SELECT @CurrentMigration =
        (SELECT TOP (1) 
        [Project1].[MigrationId] AS [MigrationId]
        FROM ( SELECT 
        [Extent1].[MigrationId] AS [MigrationId]
        FROM [tm].[__MigrationHistory] AS [Extent1]
        WHERE [Extent1].[ContextKey] = N'xxxxxxxx'
        )  AS [Project1]
        ORDER BY [Project1].[MigrationId] DESC)

IF @CurrentMigration IS NULL
    SET @CurrentMigration = '0'

IF @CurrentMigration < '201607151403491_Initial'
BEGIN
    IF schema_id('tm') IS NULL
        EXECUTE('CREATE SCHEMA [tm]')
    CREATE TABLE [tm].[Settings] (
        [Id] [int] NOT NULL IDENTITY,
        [Key] [nvarchar](max) NOT NULL,
        [Value] [nvarchar](max),
        CONSTRAINT [PK_tm.Settings] PRIMARY KEY ([Id])
    )

    ... and so on

It's all good... EXCEPT, why does it generate a block, which inspects the __MigrationHistory within the dbo schema?! And then it correctly proceeds to inspect the __MigrationHistory from my custom schema (correct behavior).

At the moment, I am just deleting the dbo related block, because I really want to isolate my schema from any interaction with the default one. When I generate a script, I don't want it to do anything outside the 'tm' schema.

Why does EF generate the script in this way? Is there really any reason to query the __MigrationHistory from within the dbo, when the rest of the script never puts anything there? Am I misunderstanding something?

Steve Greene · Accepted Answer · 2016-07-15 15:32:31Z

1

There is a technique for moving __MigrationHistory. That table has it's own context (System.Data.Entity.Migrations.History.HistoryContext) that you can override and then change the schema in OnModelCreating:

public class MyHistoryContext : HistoryContext 
{ 
    public MyHistoryContext(DbConnection dbConnection, string defaultSchema) 
        : base(dbConnection, defaultSchema) 
    { 
    } 

    protected override void OnModelCreating(DbModelBuilder modelBuilder) 
    { 
        base.OnModelCreating(modelBuilder); 
        modelBuilder.Entity<HistoryRow>().ToTable(tableName: "MigrationHistory", schemaName: "admin"); 
        modelBuilder.Entity<HistoryRow>().Property(p => p.MigrationId).HasColumnName("Migration_ID"); 
    } 
}

Then you need to register it:

public class ModelConfiguration : DbConfiguration 
{ 
    public ModelConfiguration() 
    { 
        this.SetHistoryContext("System.Data.SqlClient", 
            (connection, defaultSchema) => new MyHistoryContext(connection, defaultSchema)); 
    } 
}

answered Jul 15, 2016 at 15:32

Steve Greene

12.4k2 gold badges36 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

hyankov Over a year ago

It may have an own context, but the __MigrationHistory table still belongs to a schema. It should belong to my schema. Why is it leaking out to the dbo, outside of my schema? If it were only manipulating the _MH table in the dbo, I would understand - but what really confuses me is the duplicate block for _MH - once in my schema and once in the dbo. I can't wrap my hand around this.

Steve Greene Over a year ago

It's definitely buggy with the schema handling - we have hacked it several times to get it the way we need it.

hyankov Over a year ago

Thanks @Steve Greene, I also think it's a bug. I have reported it.

hyankov · Accepted Answer · 2016-07-21 05:17:32Z

0

I have logged this as an issue on the EF codeplex project page. They have closed the issue with the following explanation:

It is looking for a previous version of the history table that may have been in the dbo schema, this is by design to help support moving it.

answered Jul 21, 2016 at 5:17

hyankov

4,1362 gold badges34 silver badges50 bronze badges

Collectives™ on Stack Overflow

EntityFramework code-first with custom default schema database script generation - references dbo

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related