MySQL JSON column and serialization with type

Question

I am using .Net core 2, Newtonsoft.JSON 10.0.3 and a MySql with JSON column. The issue i think is because the json column type orders the elements for efficiency, it moves the "$type" out of position zero, which causes the de-serialization to fail.

using the below serializer setting during serialization:

var jsonSetting = new JsonSerializerSettings() { TypeNameHandling = TypeNameHandling.All, TypeNameAssemblyFormatHandling = TypeNameAssemblyFormatHandling.Simple };

yeilds :

{"$type": "ConsoleApplication.TestItem, ConsoleApplication","id": "1a58830b-addc-48a9-a6a9-358d35d2132e", "name": "Tom Hanks",  "description": null}

This works when using the string in a deserialization call.

However, when pulling the data from a JSON column:

{"id": "1a58830b-addc-48a9-a6a9-358d35d2132e", "name": "Tom Hanks", "$type": "ConsoleApplication.TestItem, ConsoleApplication", "description": null}

This causes the deserialization to fail.

below is an example. Does anyone have an idea of how to address this? (besides keeping a TEXT/BLOB field containing the raw json)

using System;
using System.Collections.Generic;
using Newtonsoft.Json;

namespace TestConsoleApplication
{
    class Program
    {
        static void Main(string[] args)
        {
            var jsonSetting = new JsonSerializerSettings() { TypeNameHandling = TypeNameHandling.All, TypeNameAssemblyFormatHandling = TypeNameAssemblyFormatHandling.Simple };

            // serializing these out ahead of time yeilds these string
            //
            string serializedGoodActor = "{\"$type\":\"TestConsoleApplication.GoodActor, TestConsoleApplication\",\"Name\":\"Marlon Brando\",\"BestMovie\":\"The Godfather\"}";
            string serializedBadActor = "{\"$type\":\"TestConsoleApplication.BadActor, TestConsoleApplication\",\"Name\":\"Tracy Morgan\",\"BestMovie\":\"Cop Out\"}";

            //deserialization works fine
            //
            var goodActorObj = JsonConvert.DeserializeObject<IActors>(serializedGoodActor, jsonSetting);
            var badActorObj = JsonConvert.DeserializeObject<IActors>(serializedBadActor, jsonSetting);


            try
            {
                //this is how it would get read from a mysql JSON column (note the location of type)
                //
                string mySqlJsonValue = "{\"BestMovie\":\"The Godfather\",\"$type\":\"TestConsoleApplication.GoodActor, TestConsoleApplication\",\"Name\":\"Marlon Brando\"}";
                var mySqlGoodActorObj = JsonConvert.DeserializeObject<IActors>(mySqlJsonValue, jsonSetting);
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex);
            }

            Console.ReadLine();
        }
    }


    public interface IActors
    {
        string Name { set; get; }   
    }

    public class BadActor : IActors
    {
        public string Name { set; get; }
        public string WorstMovie { set; get; }
    }

    public class GoodActor : IActors
    {
        public string Name { set; get; }
        public string BestMovie { set; get; }
    }

}

The error is misleading at best, but that is entirely another issue.

Newtonsoft.Json.JsonSerializationException: Could not create an instance of type TestConsoleApplication.IActors. Type is an interface or abstract class and cannot be instantiated. Path 'BestMovie', line 1, position 13.