Issues with target model producing error: "X could not be found in target model"

[jasekiw]

It seems that migration code depends on the snapshot in the designer file. If it isn't specified in the designer file then it can't perform the operation.

This is an error I get when I try to run the squashed migration:

The column '`UserAlertPreference`.`SiteId`' could not be found in the target model. Make sure the table name exists in the target model and check the order of all migration operations. Generally, rename tables first, then columns.

I tracked it down to this statement in the squashed migration:

  migrationBuilder.RenameColumn(
      name: "SystemProcessId",
      table: "UserAlertPreference",
      newName: "SiteId");

It seems to be erroring because the designer file for the migration did not include the SiteId column. So Ef is depending on the target state in the designer file to perform it's operations.

So I manually modified the designer file of the squashed migration and updated to match the latest migration.

Then I got another error.

The column '`Quote`.`Contact`' could not be found in the target model. Make sure the table name exists in the target model and check the order of all migration operations. Generally, rename tables first, then columns.

I tracked this error down to:

    migrationBuilder.RenameColumn(
        name: "ContractType",
        table: "Quote",
        newName: "Contact");

This is a similar error because this column no longer exists in the latest snapshot.

As a person creates migrations they might be adding or removing tables/columns. If the target snapshot does not contain the information for it then it can't perform the operation. This is unfortunate because this mean's we can't squash without altering ef core's source code. I ran into this issue in the past and decided to just take an export of the data structure of my database and slap it into a migrationBuilder.Sql("") statement like so:

migrationBuilder.Sql(@"

-- MariaDB dump 10.19  Distrib 10.6.12-MariaDB, for debian-linux-gnu (x86_64)
--
-- Host: ***    Database: 
-- ------------------------------------------------------
-- Server version	5.6.47.0
...
");

I'm using ef core with mysql if that matters.

The migration project is specific to each database anyways so another approach might be to execute the migrations to a script and put all the statements in one migrationBuilder.Sql("") call.

[pdevito3]

All this tool does is compile all the migrations you ad into one file vs however many you had before -- if your snapshot isn't matching them then that's an issue with migrations that were built not the tool itself.

Also, I'd recommend tweaking the migration vs the snapshot. I did call out that manual resolutions may still have to be made fwiw.

The current approach seems to be the least obtrusive option because it doesn't modify anything, just aggregates it into one file. Some other solutions like the one you called out make more assumptions and carry more risk I think

[pdevito3]

As a person creates migrations they might be adding or removing tables/columns. If the target snapshot does not contain the information for it then it can't perform the operation.

This part in particular -- why would your snapshot not reflect changes from the migration?

[jasekiw]

if your snapshot isn't matching them then that's an issue with migrations that were built not the tool itself.

No it's an inherit logical issue with this tool. There is nothing wrong with the snapshots other than you are removing them and they are required to perform the migrations. This tool will work only if you never have any rename column statements that are later dropped or renamed again. I tried this approach a year ago and ran into the same problem and decided it was not the way to go for any real sized project.

[jasekiw]

@pdevito3 After thinking about this more. I believe only RenameColumn statements are the issue. AddColumn contains all the information needed in the statement to construct the column. DropColumn contains all the information it needs to drop the column. RenameColumn however does not provide the type information of the column so it refers to the Designer file to get that information. If the information is not found in the designer file, the error is thrown.

The information is always in the designer file before squashing. After squashing however that information can be lost since designer files are deleted. Does that make sense?

I apologize if I'm not being clear.

[pdevito3]

Honestly I'm not sure I'm fully following -- I have numerous column renames in one example project I've already tried and it had no issues.

In practice your migrations should have an initial column creation, then a later one renaming the column. The snapshot should reflect this change. When you squash it will start by adding a column then renaming it in that order and will end up at that same snapshot view.

It sounds like the issue is the snapshot itself? Or something odd with the migration order?

Hard to tell without an example to diagnose 😕

[jasekiw]

@pdevito3 I can work on creating a reproduction repo. I didn't realize how complicated it would be to explain. It might also be database provider related.

[jasekiw]

@pdevito3
I created two repositories, one for sqlite and another for mysql. Both repositories are committed in the pre-squash state so you should be able to test the squash procedure yourself.

The reproduction steps should be

• Squash
• Ensure the database is deleted so the squashed migration will run
• Run dotnet ef database update

The sqlite got this error which is a new error we haven't discussed yet:

The migration operation 'PRAGMA foreign_keys = 0;
' from migration 'InitialCreate' cannot be executed in a transaction. If the app is terminated or an unrecoverable error occurs while this operation is being executed then the migration will be left in a partially applied state and would need to be reverted manually before it can be applied again. Create a separate migration that contains just this operation.

The mysql project got the error we were discussing above. The mysql one assumes you are running a mysql database the user root and no password.

The column '`Posts`.`Description`' could not be found in the target model. Make sure the table name exists in the target model and check the order of all migration operations. Generally, rename tables first, then columns.

It seems to be database provider specific depending if it needs to write a modify column statement or a simple rename in newer versions.

Edit: Forgot to link them:

https://github.com/jasekiw/efcore-mysql-squash-migrations

https://github.com/jasekiw/efcore-sqlite-squash-migrations

[pdevito3]

Roger, thanks! To be fair mine were all Postgres so maybe the db provider is indeed the issue. I'll try and take a look soon, thanks again!

[pdevito3]

So I just tried sqlite and it worked just fine 🤔 setup series of migrations, ensure one has a rename, setup db to reflect this, squash with steward, rerun and try again, no issues. Same when wiping the db and running it fresh.

Tried with MySQL, but couldn't get the EF config to be happy, so I'll come back again to that one when I have more time

[cremor]

@pdevito3 Did you get that part (emphasis mine)?

rename column statements that are later dropped or renamed again

[pdevito3]

@pdevito3 Did you get that part (emphasis mine)?

rename column statements that are later dropped or renamed again

I overlooked this, but just tried with it and it still worked fine, which doesn't surprise me.

The migrations are distinct actions that get you to a certain end state (add column, then rename, then delete). The snapshot stores the most recent final state to know what changes. Squashing all those steps into 1 file vs ## of files should make no difference when applying your migrations.

[jasekiw]

@pdevito3 Did you use the repos I posted? I did the same exact steps and got an error. Can you publish the repo where yours worked so I can compare?

Also I think we ran into some confusion. Before When I mentioned the snapshot, I was referring to the .Designer file, not the ModelSnapshot.cs file.

[pdevito3]

The sqlite one is empty and the MySQL doesn't actually have db plumbing hooked up, but I used the migrations in that project as a reference to make a dupe example that can set up with any of the 3 dbproviders. I'll post it later