CODEWITHSUNDEEP
entity-frameworkef-code-first
hyankov
hyankov

Reputation: 4130

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

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?

Upvotes: 1

Views: 1376

Answers (2)

hyankov
hyankov

Reputation: 4130

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.

Upvotes: 0

Steve Greene
Steve Greene

Reputation: 12304

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

Upvotes: 1

Related Questions