Pomelo & EFCore not ignoring views in migrations

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.

This doesn't seem to an issue with Pomelo or EF Core, since the following does work as expected:

1. Create a project with the following content:

Project.csproj:

<Project Sdk="Microsoft.NET.Sdk">

    <PropertyGroup>
        <OutputType>Exe</OutputType>
        <TargetFramework>net5.0</TargetFramework>
        <Nullable>disable</Nullable>
    </PropertyGroup>

    <ItemGroup>
        <PackageReference Include="Microsoft.EntityFrameworkCore.Design" Version="5.0.7">
            <PrivateAssets>all</PrivateAssets>
            <IncludeAssets>runtime; build; native; contentfiles; analyzers; buildtransitive</IncludeAssets>
        </PackageReference>
        <PackageReference Include="Microsoft.Extensions.Logging.Console" Version="5.0.0" />
        <PackageReference Include="Microsoft.EntityFrameworkCore.Relational" Version="5.0.7" />
        <PackageReference Include="Pomelo.EntityFrameworkCore.MySql" Version="5.0.0" />
    </ItemGroup>

</Project>

Program.cs:

using System;
using System.Diagnostics;
using System.Linq;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Logging;

namespace IssueConsoleTemplate
{
    public class IceCream
    {
        public int IceCreamId { get; set; }
        public string Name { get; set; }
        public DateTime BestServedBefore { get; set; }
    }

    public class ExpiredIceCream
    {
        public int IceCreamId { get; set; }
        public string Name { get; set; }
        public DateTime BestServedBefore { get; set; }
        public int DaysExpired { get; set; }
    }

    public class Context : DbContext
    {
        public DbSet<IceCream> IceCreams { get; set; }
        public DbSet<ExpiredIceCream> ExpiredIceCreams { get; set; }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            if (!optionsBuilder.IsConfigured)
            {
                var connectionString = "server=127.0.0.1;port=3306;user=root;password=;database=So67919519_01";
                var serverVersion = ServerVersion.AutoDetect(connectionString);
                optionsBuilder.UseMySql(connectionString, serverVersion)
                    .UseLoggerFactory(
                        LoggerFactory.Create(
                            configure => configure
                                .AddConsole()
                                .AddFilter(level => level >= LogLevel.Information)))
                    .EnableSensitiveDataLogging()
                    .EnableDetailedErrors();
            }
        }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.Entity<IceCream>()
                .HasData(
                    new IceCream {IceCreamId = 1, Name = "Vanilla", BestServedBefore = new DateTime(2021, 1, 1)},
                    new IceCream {IceCreamId = 2, Name = "Chocolate", BestServedBefore = new DateTime(2021, 5, 15)},
                    new IceCream {IceCreamId = 3, Name = "Matcha", BestServedBefore = DateTime.Today.AddYears(5)});

            modelBuilder.Entity<ExpiredIceCream>(
                entity =>
                {
                    entity.ToView("ExpiredIceCreams");
                    entity.HasNoKey();
                });
        }
    }

    internal static class Program
    {
        private static void Main()
        {
            using var context = new Context();

            var expiredIceCreams = context.ExpiredIceCreams.ToList();
            
            Trace.Assert(expiredIceCreams.Count == 2);
        }
    }
}

Create an initial migration:

dotnet ef migrations add Initial

Add the following code to the end of the Up() method in the generated Migrations/..._Initial.cs file:

migrationBuilder.Sql(@"
    CREATE VIEW `ExpiredIceCreams` AS
        SELECT *, DATEDIFF(CURDATE(), `BestServedBefore`) AS DaysExpired
        FROM `IceCreams`
        WHERE `BestServedBefore` <= CURDATE();");

Add the following code to the start of the Down() method in the generated Migrations/..._Initial.cs file:

migrationBuilder.Sql(@"DROP VIEW `ExpiredIceCreams`;");

Update your database:

dotnet ef database update

Run the project to ensure it works as expected.

Then add a second migration:

dotnet ef migrations add Second

Take a look at the generated Migrations/..._Second.cs file. It should not contain any operations regarding your ExpiredIceCreams view.

Where to go from here

I would start by diffing the old and new ...<MigrationName>.Design.cs files, to see what is going on.

Feel free to post your migration files content, so we can take a look at it.

(Maybe this is connected to some charset or collation issue/change.)