Read database during an entity framework migration (select query)

Question

I know I can use the Sql method to update data during a migration, and it works great for simple things that can be expressed in pure SQL.

I also know I could use a Seed method but that would feel like a hack (the code I want to write has to be executed once, when the migration is executed).

In my current case, I need to strip HTML tags from a column, and write that to a new column added in the migration. I already have a C# method that does exactly that. What I want to do, is iterate over each row in C#, and generate a SQL statement for each row that will update the row with the corresponding HTML-stripped text.

More generally, I think being able to read the database in C# during a migration could be handy in a lot of situations. Doing so within the migration transaction would be perfect, but for that I need to retrieve the SQL connection that Entity Framework internally uses for the migration.

So far I've found no way to execute a SQL query that returns results. Is this possible?

Answer 1

I prefer also to transform the data in migration files over the Seed method.

Updating the data should not be a problem, you can just do as shown in the line code below and you can also passing the parameters from C# in the SQL string:

Sql("UPDATE TableName SET MyValueInMinutes= -DATEDIFF(MINUTE, CurrentTime, 0)";

The problem by data reading! In reality you will never need to handle the data tranformation in C#, normally you create everything in SQL server (Stored procedure and functions and SQL stametents) and you can just call them from the migration file at the time point when you need them or when the data are ready to be trasformed. But I think you are not a database programmer, that why you are trying to handle the data in C#.

Here is an example how to iterate over the rows and I will search for a given text and find for you a free available 'AnyText'+Counter.

CREATE PROCEDURE sp_FindFreeName  
@toBeFindName nvarchar(MAX) OUT  
AS 
BEGIN 
DECLARE @LoopCounter INTEGER 
SET @LoopCounter = 1 
WHILE EXISTS (SELECT @toBeFindName FROM dbo.MyTable WHERE Name = @toBeFindName) 
  BEGIN 
      SET   @toBeFindName = 'AnyText', @LoopCounter) 
      SET @LoopCounter = @LoopCounter + 1 
  END 
END 

Then you can called form C#:

var cSharpName = string.Empty;
Sql("exec sp_FindFreeName @toBeFindName=@"+ cSharpName +" OUTPUT");

where cSharpName given from C#.

The third why to do that you can also write your own Code First Migration Operations. You have to define your SqlServerMigrationSqlGenerator as described in Rowan blog:

https://romiller.com/2013/02/27/ef6-writing-your-own-code-first-migration-operations/

Conclusion:

If you really want to read the data in the migration files then you have to use for example: SqlDataReader and you have to read also the connection string from App.config.

In most use cases you can do everything inside the SQL Server. Just create your data tranfsormation SQL scripts and execute them from the desired location.

You can use Extensions and SqlServerMigrationSqlGenerator to do that in a cleaner way then the DataReader.