Auto migrations with GenerateScript

[datnt97]

THE SITUATION:

I am working on a project that has multiple Tenant that has its database.
And I have to update the database when it has new migrations for every tenant.
I also look through issue "Automatic migrations", but I dont' really understand it @@

MY SOLUTION:

I found a solution here.
I get the pending migrations of context, and update if it contains in the pending list.

private Task Test()
{
	using (var context = new SampleContext())
	{
		var migrationStart = nameof(F5_RemoveUnnecessaryProperty);
		var migrationEnd = nameof(F6_Refactor_Topic_V3);

		var migrator = context.GetService<IMigrator>();

		// Error with transaction
		//await migrator.MigrateAsync(migration);

		var scripts = migrator.GenerateScript(migrationStart, migrationEnd, MigrationsSqlGenerationOptions.NoTransaction);
		var lines = scripts.Split($"{Environment.NewLine}GO{Environment.NewLine}").ToList();
		lines = lines.Take(lines.Count - 1).ToList();
		
                foreach (var line in lines)
		{
			using (var trans = context.Database.BeginTransaction(System.Data.IsolationLevel.ReadUncommitted))
			{
				await context.Database.ExecuteSqlRawAsync(line);
				await trans.CommitAsync();
			}
		}
	}
}

THE ERROR:

Sometimes I encounter problems with transaction. If I use await migrator.MigrateAsync(migration);

ASP.NET CORE VERSION:

I am using net5.0 and EntityFramework Core: 5.0.3.

QUESTIONS:

• Has any solutions better than my solution?

[roji]

@datnt97 your error shows a timeout exception while applying the migrations; this isn't something that's related to EF Core, but rather to the time taken to apply the operation at the database side. If you have tables with a large number of rows, then an operation such as adding an index or dropping a column may take a while. You can use ctx.Database.SetCommandTimeout() to increase the timeout.

Note also that instead of generating a script, splitting it on GO and executing it manually as above, you can simply call migrator.Migrate(), passing it the desired target migration as an argument.

[datnt97]

I have many migrations. I worry about SetCommandTimeout() which takes a long time to migrate. Maybe I will keep my solution.

[roji]

@datnt97 whether you use migrator.Migrate() or your custom solution to generate SQL and execute it separately has no bearing on how long your migrations will take. Regardless of how exactly you execute your migrations, you need to make sure your migrations either run quickly enough, or raise the timeout.