Updating a column type and updating data with non-SQL code

Question

I have a table of Result objects (called Results). Currently, one column of this table is a string type called ResultsData, and represents a Json serialization of a C# object (using Json.NET).

Due to problems with the size of these objects leading to database queries timing out, I want to change this column to a byte[], holding a Bson serialization of the object. However, when I change the type, add a migration and run it, it understandably crashes, as string objects have no implicit conversion to byte[]. The Up() method for the migration, for reference, looks like this:

public override void Up()
{
    AlterColumn("dbo.Results", "ResultsData", c => c.Binary());
}

I should mention that any data loss here is unacceptable.

Now I can handle the code to convert individual strings to byte arrays, and it's in a method that we'll call byte[] ConvertResult(string resultsData). What I'm not sure about is how I'd call this method on each row of the database.

The closest answer that I could find on here is the first answer of this question: EF5 Code First - Changing A Column Type With Migrations. However, the conversion code in that case is done entirely in SQL, which obviously does not apply here.

If anyone has any ideas as to how I might go about this, it'd be greatly appreciated. The version of Entity Framework I'm running is 6.1.3, if that helps.

Edit

Out of desperation, I tried putting the actual conversion code into the SQL method: didn't expect it to work, and it didn't, producing the error Incorrect syntax near 'END':

public override void Up()
{
    AddColumn("dbo.Results", "ResultsDataTmp", c => c.Binary());
    Sql(@"
    UPDATE dbo.Results
    SET ResultsDataTmp = Service.ConvertOldResultsToBinary(ResultsData)
        END
    ");
    DropColumn("dbo.Results", "ResultsData");
    RenameColumn("dbo.Results", "ResultsDataTmp", "ResultsData");
}

Edit 2

Here's the implementation of Serial.ConvertOldResultsToBinary, as per DrewJordan's request.

public byte[] ConvertOldResultsToBinary (string oldResultsData)
{
    var serializer = new JsonSerializer
    {
        ConstructorHandling = ConstructorHandling.AllowNonPublicDefaultConstructor,
        TypeNameHandling = TypeNameHandling.Auto,
        MissingMemberHandling = MissingMemberHandling.Ignore
    };

    IResultsWrapper result;

    using (var reader = new JsonTextReader(new StringReader(oldResultsData)))
    {
        try
        {
            result = serializer.Deserialize<ResultsWrapper>(reader);
        }
        catch (JsonSerializationException)
        {
            using (var trialReader = new JsonTextReader(new StringReader(oldResultsData)))
                {
                    result = serializer.Deserialize<TrialResultsWrapper>(trialReader);
            }
        }
    }

    var stream = new MemoryStream();
    using (var writer = new BsonWriter(stream))
    {
        serializer.Serialize(writer, result);
    }
    return stream.ToArray();
}

The specific error given, if it helps, is: `Implicit conversion from data type nvarchar(max) to varbinary(max) is not allowed. Use the CONVERT function to run this query.` — Dan McElroy, Apr 03 '15 at 11:03
what is done in `Service.ConvertOldResultsToBinary`? can you post it? — DrewJordan, Apr 06 '15 at 13:58
@DrewJordan sorry for the late reply, just edited the question to include this code. Thanks for your interest. — Dan McElroy, Apr 07 '15 at 09:21

score 0 · Answer 1 · answered Apr 08 '15 at 15:24

Your first edit was on the right track, you need to convert to binary in SQL, however you can't call C# code inside of the call to Sql() since you're telling it to simply run the string you pass as a SQL command.

Try something like this:

public override void Up()
{
    AddColumn("dbo.Results", "ResultsDataTmp", c => c.Binary());
    Sql(@"
    UPDATE dbo.Results
    SET ResultsDataTmp = CAST(ResultsData AS VARBINARY(MAX))
        END
    ");
    DropColumn("dbo.Results", "ResultsData");
    RenameColumn("dbo.Results", "ResultsDataTmp", "ResultsData");
}

Updating a column type and updating data with non-SQL code

Edit

Edit 2

1 Answers1