Clear documentation around best practice for data migrations and using the ORM

[Jstall]

I am planning a data migration strategy for an application and I'm not finding much in the way of documentation around best practices. The application supports multiple data providers so I would like to avoid generating raw SQL if possible.

When I say data migration I am referring to a migration that doesn't do any schema changes data in some way.

A contrived example would be taking a Person object:

public class Person
{
     string FirstName {get; set;}
     string LastName {get; set;}
}

And adding a DisplayName column that is FirstName + " " + LastName . Again this is a contrived example :-).

An approach I would like to take is making a schema change migration:

        // in DisplayNameSchemaMigration.cs
        protected override void Up(MigrationBuilder migrationBuilder)
        {
                migrationBuilder.AddColumn<string>(
                name: "DisplayName ",
                table: "Person",
                nullable: true);
        }

Then doing the data migration in application code. Note: I realize this is not a very efficient solution due to efcore not having a bulk update operation but it's for illustrative purposes:

        // in DisplayNameDataMigration.cs
        protected override void Up(MigrationBuilder migrationBuilder)
        {
                 var people = MyDbContext.People.Where(p => p.DisplayName == null).ToList();
                 people.ForEach(p => o.DisplayName = $"{p.FirstName} {p.LastName }");
                 MyDbContext.SaveChanges();
                  
        }

The point of this being that I could use the ORM and wouldn't have to use raw SQL. That way I could support multiple database providers without having to generate a separate migration for each one and having to retroactively create new migrations if I want to add support for another data provider in the future.

The application lifecycle is state machine driven and the nodes are orchestrated in such a way that I wouldn't have to worry about concurrent data migrations stepping on other anthers toes during the migration application phase.

I'm looking at the documentation and Googling but I'm not seeing much in the way of guidance on this subject. I do see occasional comments on forums or SO saying it's not a good idea to use the orm for a data migration but I don't see anything from an authoritative source. I think the documentation could really benefit with some mention of these scenarios and, certainly if anyone with insight has any suggestions here specifically, I would be grateful as well.

[ajcvickers]

@Jstall The issue tracking data migrations in EF Core is dotnet/efcore#5642. However, the intention is not to allow these to be written using the context/model types. The reason for this is that those types evolve as the application evolves, but migrations need to keep running even if the model types have changed. For example, if FirstName and LastName were removed and replaced with a single Name. And migrations referencing FirstName or LastName would then break.

So the options are:

• Use SQL to your migrations, since this does not have any binding to the C# model types
• Use specific data APIs in your migrations--see Migrations: Data motion APIs efcore#5642
• Limit migrations to only moving forward and update a method as each migration is applied that will do any data motion needed. This is pretty hard to manage and keep right, but I don't know of another way that you can safely use your model types.

[mc0re]

those types evolve as the application evolves

Nevertheless the old migrations can still run, because EF Core, when creating migrations, replaces the property names with strings. The same could be done for data migrations. So if one defines a migration like

UpdateData(u => u.DisplayName, u => $"{u.FirstName} {u.LastName}")

The Add-Migration tool would create a proper set of hardcoded strings.
If later on FirstName gets deleted, the UpdateData operation can be changed or removed alltogether - this will not alter the generated migration.