EF Core migration - byte[] to base64 in SQLite without sqlean-crypto

40 views Asked by At

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

  1. Is there anyway of making a sqlite extension available to EF Core so the above approach can be used?
  2. If not, is there anyway of accessing the database programmatically using SqlCommand etc 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.

0

There are 0 answers