ModelBuilder: QueryTypeBuilder.ToView continuously generates EnsureSchema in migrations.

[sergeitemkin]

When binding a QueryType to a view or table using QueryTypeBuilder.ToView and specifying a schema, an EnsureSchema statement is created for the QueryType in every subsequent migration. It doesn't break anything, but it does get a little bit tedious to keep picking the rogue EnsureSchemas out of migration scripts.

Steps to reproduce

1. Create a DbContext with a QueryType.

public class SomeType
{
    public string Column1 { get; set; }
}

public class SampleContext : DbContext
{
    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Query<SomeType>(builder =>
        {
            builder.ToView(schema: "SomeSchema", name: "Things");
        });
    }
}

2. Run dotnet ef migrations add Test
   • I'm expecting the model snapshot to have some reference to EnsureSchema or to maybe even just have an empty migration since ensuring that the schema exists doesn't really help if the query is relying on existence of some SQL table or view that isn't being auto-generated by the migration
3. Run dotnet ef migrations add TestAgain
   • Both migrations contain an EnsureSchema statement

   protected override void Up(MigrationBuilder migrationBuilder)
   {
       migrationBuilder.EnsureSchema(
           name: "SomeSchema");
   }

EfCoreViewWithSchema.zip

Further technical details

EF Core version: 2.2.0
Database Provider: Microsoft.EntityFrameworkCore.SqlServer
Operating system: Windows 10
IDE: Visual Studio 2019 16.0.0 Preview 1.1

[smitpatel]

https://github.com/aspnet/EntityFrameworkCore/blob/af13b52bfc390346551926ca56d5b0703c1eb0e7/src/EFCore.Relational/Migrations/Internal/MigrationsModelDiffer.cs#L1955-L1959

GetSchemas function needs to ignore QueryTypes when getting schemas since migrations don't deal with query types.

[Marusyk]

Hello, I would try to prepare PR for this, if nobody is against it

[ajcvickers]

@Marusyk Sure, go for it.

[Marusyk]

I don't understand how to ignore QueryTypes in GetSchemas (it's not so easy as I thought). So, it would be better to assign this task to someone else. Sorry. It is up-for-grabs again

[bricelam]

No worries, @Marusyk; We'll get to it sometime during the 3.0.0 release.

[HaoK]

Verified that both migrations do still contain EnsureSchema.

Steps I did: (VS 2019 Enterprise 16.1.3)

1. File New MVC (Individual auth to pull in EF) (3.0.0-preview6.19304.10)
2. Copy SampleContext into app (and configure it to use DefaultConnection SqlServer)
3. In Package Manager console run: Add-Migration test -Context SampleContext
4. Add-Migration testAgain -Context SampleContext

Both still contain EnsureSchema

@ajcvickers this doesn't appear fixed, I'm not sure what labels you want me to set for this, so I will just unassign this bug for now

[HaoK]

Note: I also tried fixing the obsolete warnings and used:

            modelBuilder.Entity<SomeType>().HasNoKey().ToTable(schema: "SomeSchema", name: "Things");

Which still has EnsureSchema in both migrations

[bricelam]

Thanks for checking. I have another related issue that may also fix this one. Applied the label too soon. 😉