Schema ignored when applying migrations during runtime for the first time

Question

Expected/desired behavior: "Members" table is created in "mySchema"

Actual behavior: "Members" table is created in "public" (the default schema)

using System;
using System.Linq;
using Microsoft.EntityFrameworkCore;

namespace Test
{
    class MyContext : DbContext
    {
        readonly string connectionString;

        readonly string schema;

        readonly bool isParameterless;

        public MyContext()
        {
            connectionString = @"Server = 127.0.0.1; Port = 5432; Database = empty; User Id = postgres; Password = 123;";

            isParameterless = true;
        }

        public MyContext(string connectionString, string schema)
        {
            this.connectionString = connectionString;

            this.schema = schema;
        }

        public DbSet<Member> Members { get; set; }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            if (!isParameterless)
                modelBuilder.HasDefaultSchema(schema);
        }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            optionsBuilder.UseNpgsql(connectionString);
        }
    }

    class Member
    {
        public string id { get; set; }
    }

    class Programs
    {
        static void Main(string[] args)
        {
            var context = new MyContext(@"Server = 127.0.0.1; Port = 5432; Database = test; User Id = postgres; Password = 123;", schema: "mySchema");

            context.Database.EnsureDeleted();

            Console.WriteLine(context.Model.GetDefaultSchema()); // "mySchema"

            Console.WriteLine(context.Model.FindEntityType(typeof(Member)).GetSchema()); // "mySchema"

            context.Database.Migrate();

            Console.WriteLine(context.Model.GetDefaultSchema()); // "mySchema"

            Console.WriteLine(context.Model.FindEntityType(typeof(Member)).GetSchema()); // "mySchema"

            context.Database.ExecuteSqlRaw(@"CREATE TABLE public.""Members""()"); // Npgsql.PostgresException: '42P07: relation "Members" already exists'

            context.Database.ExecuteSqlRaw(@"CREATE TABLE ""mySchema"".""Members""()"); // Npgsql.PostgresException: '3F000: schema "mySchema" does not exist'
        }
    }
}

Migrations were generated for this code using the following command:

dotnet ef migrations add init -v

Further technical detail

EF Core version: 3.1.0

Database provider: Npgsql.EntityFrameworkCore.PostgreSQL (3.1.0)

Target framework: NET Core 3.0

VS Project.zip

Shay Rojansky Shay Rojansky · Accepted Answer · 2019-12-18T16:17:52

You haven't posted a full program so it's not possible to be sure.

However, my guess is that you've created a context via the parameterless constructor before doing so with the other constructor. The EF Core model building, which includes running OnModelCreating, runs only once when you first use the context; it isn't redone for every single context you instantiate. As a result, you can't have multiple contexts with different default schemas.

Schema ignored when applying migrations during runtime for the first time

1 Answers