Search code examples
mysqlentity-framework-corepomelo-entityframeworkcore-mysql

Pomelo & EFCore not ignoring views in migrations

I have a dotnet core project that uses EFCore and Pomelo.EntityFrameworkCore. I've just updated from I think EFCore 3.1.0 to 5.0.7 (the latest at the time of writing this) and simultaneously Pomelo to 5.0.0

In my project, I use EFCore to call stored procedures and map them to views. I've added the views to the context as follows:

modelBuilder.Entity<NameOfView>().HasNoKey().ToView(null);

What I've now noticed since updating EFCore and Pomelo is that whenever I try to run a migration, it no longer ignores these views. I keep getting CreateTable or DropTable scripts in the migration depending on whether the views are in the database snapshot. Before the update these views were simply ignored.

Is there some property I need to set for the views in the context to ensure that they are ignored?

Solution

  • You are experiencing the following EF Core 5.0 breaking change ToView() is treated differently by migrations, in particular the first migration after upgrade behavior explained here

    New behavior

    Now ToView(string) marks the entity type as not mapped to a table in addition to mapping it to a view. This results in the first migration after upgrading to EF Core 5 to try to drop the default table for this entity type as it's not longer ignored.

    I could understand the reasoning for changing the fluent API behavior, but not the migration behavior (hello EF Core, backward compatibility?). And the suggested "mitigation"

    Use the following code to mark the mapped table as excluded from migrations:

    protected override void OnModelCreating(ModelBuilder modelBuilder)
{
   modelBuilder.Entity<User>().ToTable("UserView", t => t.ExcludeFromMigrations());
}

    While it seems to fix the first migration after upgrade behavior, it requires specifying fake table name. And unfortunately ToTable method overload with ExcludeFromMigrations capability does not accept null table name.

    The workaround I would suggest, which seems to be working for both exiting and new "unmapped" entities, is to replace

    .ToView(null)

    with

    .HasAnnotation(RelationalAnnotationNames.IsTableExcludedFromMigrations, true)

    Note: This is EF Core general issue, not database provider specific, hence has nothing to do with MySQL or Pomelo.