Support conditional DDL in migrations

[KorpEduardoSikora]

Hello ladies and gentleman..

My company has an old and very large destkop ERP software and now we're making the transition to a microsservices structure..

In this microsservices structure (at least in the begining) , multiple services will have to access the same table on the legacy ERP database..

Because of this, we may have one microsservice that needs to access a table, let's say 'Products', and then a different microsservice that also needs to access the same table..

Our internal microsservice framework, need to create some new columns (ie: Id).

We also, by design choice, DO NOT have a shared entity library. Every microsservice has it's own entities.

The problem: Migrations.

The problem that we're facing is that both microsservices will have the 'Products' entity and both will have a migration adding the Id column.

When we run the second service's migration, an erro occurs of course.

How can we avoid this problem?

Our initial idea was to create a method that accepts 2 parameters, the Legacy table name and the typeOf(Entity), which we would use to check if that table in that database has every column defined on the entity type. If a certain column does not exists, we would like to create it..

We're wondering if somehow we could use/invoke methods from the MigrationBuilder to do this..

Any ideas on how to achieve or resolve such a problem? Is our approach, given our design, viable?

Another idea, is there an event before applying an migration operation? I could check if the operation is a AddColumn and check on the the database, if the column exists i discard the operation..

[ajcvickers]

Triage: discuss with @bricelam

[bricelam]

Currently, you have to use SQL.

migrationBuilder.Sql(@"
    IF NOT EXISTS(SELECT * FROM sys.columns WHERE name = 'Id' AND object_id = OBJECT_ID('Products'))
        ALTER TABLE Products ADD Id int NOT NULL IDENTITY;
");

If all the migrations are exactly the same between projects, you could also give them the same ID. This would ensure it was only applied once to the database.

[ajcvickers]

@bricelam I haven't been able to find the existing issue that you mentioned in triage.

[EduSikora]

Thanks @bricelam.

We solved this issue similar to your answer..

Basically I created a method where the programmer will invoke as the last step of the migration Up and Down methods.

HandleUpLegacyTable and HandleDownLegacyTable as extensions of migrationBuilder which need a dbContext and a string or a list of strings with the legacy table names.

From the migrationBuilder, I get the Operations list and add/remove/edit them the way we need it..

Then I use those table names to execute a select similar to your answer..

Thanks for your attention (:

[bricelam]

@ajcvickers #15725 is the issue I was thinking of.