I'm trying to create a migration in EF Core 6.0 which changes a column from the type byte[] to a base64 encoded string (yes I know, but for ... reasons). This migration must preserve the existing data.
Just changing the CLR datatype and adding a migration results unsurprisingly in a migration which just drops the old column and adds a new one and looses the data.
An easy solution would of course be create a new temporary column, migrate the data to that column using migrationBuilder.Sql(...), then drop the old column and rename the temp one.
The trouble I have with that though is doing the base64 conversion entirely in SQL. Easy enough with a real database but SQLite doesn't have a base64 encoding function without loading the sqlean-crypto extension.
Trying update table set tempCol=encode(oldCol, 'base64') just throws an error "unknown function encode". It works from the SQLite terminal but you must first download sqlean-crypto and do .load ./crypto
My questions
- Is there anyway of making a sqlite extension available to EF Core so the above approach can be used?
- If not, is there anyway of accessing the database programmatically using
SqlCommandetc within an EF Core migration? All the reading I've done so far on that seems to suggest not and you basically limited to code-first edits or raw SQL to do your migrations.
If not I guess I'm stuck doing the "migration" outside of a migration as part of the seeding process and I'd have to keep the old column around forever.