JSON to LINQ to JSON

It is about 9:30pm and I am sitting on the train home. It has been quite tough organizing my life in the last months. This is not the promised follow-up on OpenGL. There has been no time to read any book.

Anyway, this post shows how to run LINQ queries on JSON. The use can be quite broad. Imagine a shell command executing your LINQ on any JSON file. You can query into depth and build complex return tree structures with 2 or more levels. LINQ itself is highly flexible. And – just to give it the right flair – it returns results in the good old JSON format.

You could even go a bit further and insert more than just a LINQ query. In theory, you could add any C# code at run-time. The changes to this program would be minuscule.

I have sufficiently commented the code. There is a minimum overhead to get the job done.

 

How it works:

1. The JSON data is imported using the JavaScriptSerializer, which is part of the System.Web.Extensions library reference. We try to force the result into a Dictionary<string, object>. Do not lazily use ‘object’. Some type information would get lost. I played with this and got eg. arrays instead of ArrayLists.
2. The resulting structure is then used to build classes. These are converted to legible C# source code.
3. The LINQ command is inserted. You can see the result in the TextBox titled ‘C# source code output’.
4. A compiler instance is created and some library references are added. We compile the generated source code at run-time and then execute the binary.
5. A standard JSON object is returned, rudimentary formatted and displayed as the final result.

Btw. I have inserted a horizontal and a vertical GridSplitter into the WPF XAML. You can easily change the size of the TextBoxes while playing with this tool. A few JSON examples were also added.

<Window         xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"         xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"         xmlns:d="http://schemas.microsoft.com/expression/blend/2008" xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006" mc:Ignorable="d" x:Class="LinqJson.MainWindow"   Title="JSON LINQ JSON" Height="Auto" Width="876" d:DesignHeight="695">
  <Grid>
    <Grid.RowDefinitions>
      <RowDefinition Height="1*"/>
      <RowDefinition Height="43*"/>
      <RowDefinition Height="30"/>
      <RowDefinition Height="150*"/>
      <RowDefinition Height="30"/>
      <RowDefinition Height="150*"/>
      <RowDefinition Height="10*"/>
    </Grid.RowDefinitions>
    <Grid.ColumnDefinitions>
      <ColumnDefinition Width="10*"/>
      <ColumnDefinition Width="250*"/>
      <ColumnDefinition Width="10"/>
      <ColumnDefinition Width="250*"/>
      <ColumnDefinition Width="10*"/>
      <ColumnDefinition/>
    </Grid.ColumnDefinitions>
    <Button Content="convert" Width="50" Height="30" Click="Button_Click" Grid.Row="1" HorizontalAlignment="Left" Grid.Column="1" Margin="0,8,0,7"/>

    <StackPanel Grid.Column="1" Grid.Row="2" VerticalAlignment="Bottom" HorizontalAlignment="Left" Orientation="Horizontal" Height="27" >
      <Label Content="JSON input" VerticalAlignment="Bottom" HorizontalAlignment="Left" Margin="0,1" />
      <ComboBox Width="240" Margin="50,3,3,3" SelectionChanged="ComboBox_SelectionChanged" VerticalAlignment="Bottom">
        <ComboBoxItem  Name="ex1">Example 1</ComboBoxItem>
        <ComboBoxItem  Name="ex2">Example 2</ComboBoxItem>
        <ComboBoxItem  Name="ex3">Example 3</ComboBoxItem>
        <ComboBoxItem  Name="ex4">Example 4</ComboBoxItem>
      </ComboBox>
    </StackPanel>
    <TextBox  x:Name="JsonIn" HorizontalScrollBarVisibility="Auto" VerticalScrollBarVisibility="Auto" Grid.Column="1" Grid.Row="3"/>

    <GridSplitter Grid.Column="2" Grid.RowSpan="999" HorizontalAlignment="Stretch" Width="10" Background="Transparent" ResizeBehavior="PreviousAndNext"/>
    <GridSplitter Grid.Row="4" Grid.ColumnSpan="999" VerticalAlignment="Top" HorizontalAlignment="Stretch"  Height="10" Background="Transparent" ResizeBehavior="PreviousAndNext" />

    <Label Content="JSON output" Grid.Column="3" Grid.Row="2"  VerticalAlignment="Bottom" />
    <TextBox x:Name="JsonOut" VerticalAlignment="Stretch" HorizontalScrollBarVisibility="Auto" VerticalScrollBarVisibility="Auto" Grid.Column="3"  Grid.Row="3"  />

    <Label Content="C# source code output" Grid.Column="3" Grid.Row="4"  VerticalAlignment="Bottom" />
    <TextBox x:Name="CSharpSourceCode" HorizontalScrollBarVisibility="Auto" VerticalScrollBarVisibility="Auto" Grid.Column="3" Grid.Row="5" />
    <Label Content="LINQ input" Grid.Column="1" Grid.Row="4"  VerticalAlignment="Bottom"/>
    <TextBox x:Name="LinqIn" HorizontalScrollBarVisibility="Auto" VerticalScrollBarVisibility="Auto" Grid.Column="1" Grid.Row="5" />

  </Grid>

</Window>

using Microsoft.CSharp;
using System;
using System.CodeDom.Compiler;
using System.Collections.Generic;
using System.Reflection;
using System.Text;
using System.Windows;
using System.Windows.Controls;

namespace LinqJson {
  public partial class MainWindow : Window {

    public MainWindow() {
      InitializeComponent();
    } // constructor

    private void Button_Click(object sender, RoutedEventArgs e) {

      // --------------------------------------------------
      // get the JSON input
      // --------------------------------------------------

      string lJsonIn = JsonIn.Text;

      // --------------------------------------------------
      // construct the C# source code (class hierarchy)
      // --------------------------------------------------

      Converter lConverter = new Converter();
      Dictionary<string, object> lTree = lConverter.JsonToDictionary(lJsonIn);
      lConverter.DictionariesToClasses("root", 0, lTree);

      var lStringBuilder = new StringBuilder();
      lConverter.BuildClasses(lStringBuilder);
      string lCSharpSourceCode = lConverter.GetUsings() + lStringBuilder.ToString();

      // --------------------------------------------------
      // add the LINQ command to the source code
      // --------------------------------------------------

      string lLinq = LinqIn.Text;
      string lEntryPoint = "\n\n";
      lEntryPoint += "public class baseClass {\n";
      lEntryPoint += "  public static object executeLinq(string xJson) {\n";
      lEntryPoint += "    xJson = xJson.Trim();";
      lEntryPoint += "    if (xJson[0] == '[') xJson = \"{home: \" + xJson + \"}\";";
      lEntryPoint += "    var lSerializer = new JavaScriptSerializer();\n";
      lEntryPoint += "    var root = lSerializer.Deserialize<Class_root>(xJson);\n";
      lEntryPoint += "    var lResult = " + lLinq.Replace("\n", "\n        ") + ";\n";
      lEntryPoint += "    return lSerializer.Serialize(lResult);\n";
      lEntryPoint += "  }\n";
      lEntryPoint += "}\n";
      lCSharpSourceCode += lEntryPoint;

      // --------------------------------------------------
      // display the source code
      // --------------------------------------------------

      CSharpSourceCode.Text = lCSharpSourceCode;

      // --------------------------------------------------
      // compile the source code
      // --------------------------------------------------

      var lProviderOptions = new Dictionary<string, string>();
      lProviderOptions.Add("CompilerVersion", "v4.0");
      var lCSharpCodeProvider = new CSharpCodeProvider(lProviderOptions);
      var lCompilerParameters = new CompilerParameters();

      lCompilerParameters.ReferencedAssemblies.Add("System.dll");
      lCompilerParameters.ReferencedAssemblies.Add("System.Core.dll");
      lCompilerParameters.ReferencedAssemblies.Add("System.Data.Linq.dll");
      lCompilerParameters.ReferencedAssemblies.Add("System.Threading.dll");
      lCompilerParameters.ReferencedAssemblies.Add("System.Web.Extensions.dll");
      lCompilerParameters.ReferencedAssemblies.Add("System.Xml.Linq.dll");
      lCompilerParameters.GenerateInMemory = true;
      lCompilerParameters.GenerateExecutable = false;  // not required, we don't have a Main() method
      lCompilerParameters.IncludeDebugInformation = true;
      var lCompilerResults = lCSharpCodeProvider.CompileAssemblyFromSource(lCompilerParameters, lCSharpSourceCode);

      if (lCompilerResults.Errors.HasErrors) {
        var lError = new StringBuilder();

        foreach (CompilerError lCompilerError in lCompilerResults.Errors) {
          lError.AppendLine(lCompilerError.ErrorNumber + " => " + lCompilerError.ErrorText + Environment.NewLine);
        }

        JsonOut.TextWrapping = TextWrapping.Wrap;
        JsonOut.Text = lError.ToString();
        return;
      }
      JsonOut.TextWrapping = TextWrapping.NoWrap;

      // --------------------------------------------------
      // execute the compiled code
      // --------------------------------------------------

      Assembly lAssembly = lCompilerResults.CompiledAssembly;
      Type lProgram = lAssembly.GetType("baseClass");
      MethodInfo lMethod = lProgram.GetMethod("executeLinq");

      object lQueryResult = lMethod.Invoke(null, new object[] { lJsonIn });   // returns a JSON string object

      // --------------------------------------------------
      // rudimentary JSON output formatting
      // --------------------------------------------------

      string lJsonOut = lQueryResult.ToString();
      lJsonOut = lJsonOut.Replace(",", ",\n");
      lJsonOut = lJsonOut.Replace(",{", ",{\n");
      lJsonOut = lJsonOut.Replace("]", "]\n");
      JsonOut.Text = lJsonOut;
    } //

    private void ComboBox_SelectionChanged(object xSender, SelectionChangedEventArgs e) {
      var lComboBox = xSender as ComboBox;

      switch (lComboBox.SelectedIndex) {
        case 0:
          JsonIn.Text = "{\n \"number\": 108.541,\n \"datetime\": \"1975-03-13T10:30:00\" ,\n \"serialnumber\": \"SN1234\",\n \"more\": {\n  \"field1\": 123,\n  \"field2\": \"hello\"\n },\n \"array\": [\n  {\"x\": 2.0},\n  {\"x\": 3.0},\n  {\"x\": 4.0}\n ]\n}";
          LinqIn.Text = "from a in root.array\nwhere a.x > 2.0M\nselect a";
          break;
        case 1:
          JsonIn.Text = "[ 1, 9, 5, 7, 1, 4 ]";
          LinqIn.Text = "from a in root.home\nwhere ((a == 4) || (a == 1))\nselect a";
          break;
        case 2:
          JsonIn.Text = "{myLuckyNumbers: [ 1, 9, 5, 26, 7, 1, 4 ]}";
          LinqIn.Text = "from x in root.myLuckyNumbers\nwhere x % 2 == 0\nselect new { simple=x, square=x*x, text=\"Field\"+x.ToString(), classInClass=new {veryOdd=x/7.0, lol=\":D\"}}";
          break;
        case 3:
          string s;
          s = "{\"mainMenu\": {\n";
          s += "  \"info\": \"Great tool.\",\n";
          s += "  \"value\": 100.00,\n";
          s += "  \"menu\": {\n";
          s += "  \"subMenu\": [\n";
          s += "    {\"text\": \"New\", \"onclick\": \"NewObject()\"},\n";
          s += "    {\"text\": \"Open\", \"onclick\": \"Load()\"},\n";
          s += "    {\"text\": \"Close\", \"onclick\": \"ByeBye()\"},\n";
          s += "    {\"text\": \"NNN\", \"onclick\": \"Useless()\"}\n";
          s += "  ]}\n";
          s += "}}\n";
          JsonIn.Text = s;
          LinqIn.Text = "root.mainMenu.menu.subMenu.Where(t => t.text.StartsWith(\"N\")).Last();";

          break;
        default:
          break;
      }
    } //

  } // class
} // namespace

using System;
using System.Collections;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Web.Script.Serialization;  // requires reference to System.Web.Extensions, used by the JavaScriptSerializer

namespace LinqJson {
  public class Converter {

    private Dictionary<string, object> _Classes = new Dictionary<string, object>();

    public string GetUsings() {
      string s;
      s = "using System;\n";
      s += "using System.Collections.Generic;\n";
      s += "using System.Linq;\nusing System.Text;\n";
      s += "using System.Threading.Tasks;\n";
      s += "using System.Collections;\n";
      s += "using System.Web.Script.Serialization;\n\n";
      return s;
    } //

    public Dictionary<string, object> JsonToDictionary(string xJson) {
      xJson = xJson.Trim();
      if (xJson[0] == '[') xJson = "{home: " + xJson + "}";  // nameless arrays cannot be converted to dictionaries

      var lJavaScriptSerializer = new JavaScriptSerializer();
      try { return lJavaScriptSerializer.Deserialize<Dictionary<string, object>>(xJson); }
      catch (Exception) { return null; }
    } //

    public void BuildClasses(StringBuilder xStringBuilder) {
      foreach (var lClass in _Classes) {
        Dictionary<string, object> lMembers = lClass.Value as Dictionary<string, object>;
        if (lMembers == null) continue;
        if (lMembers.Count <= 0) continue;

        xStringBuilder.Append("public class Class_");
        xStringBuilder.Append(lClass.Key);
        xStringBuilder.AppendLine(" {");

        foreach (var lMember in lMembers) {
          object lValue = lMember.Value;
          string lKey = lMember.Key;
          Type lType = (lValue == null) ? typeof(object) : lMember.Value.GetType();

          xStringBuilder.Append(new String(' ', 2));
          xStringBuilder.Append("public ");

          if (lType.IsValueType || (lValue is string)) {
            xStringBuilder.Append(lType.Name);
            xStringBuilder.Append(" ");
            xStringBuilder.Append(lKey);
            xStringBuilder.AppendLine(";");
          }
          else if (lValue is Dictionary<string, object>) {
            xStringBuilder.Append("Class_");
            xStringBuilder.Append(lKey);
            xStringBuilder.Append(" ");
            xStringBuilder.Append(lKey);
            xStringBuilder.AppendLine(";");
          }
          else if (lValue is ArrayList) {
            ArrayList lArrayList = lValue as ArrayList;
            var lMemberType = ArrayListType(lArrayList);   // differentiate between the contents of the list
            if (lMemberType.IsValueType || (lMemberType.Name == "String")) {
              //xStringBuilder.Append(lMemberType.Name.Replace("`2")); // Dictionaries use name "Dictionary`2"
              xStringBuilder.Append(" List<");               xStringBuilder.Append(lMemberType.Name);               xStringBuilder.Append("> ");
              xStringBuilder.Append(lKey);
              xStringBuilder.AppendLine(";");
            }
            else {
              // a class
              xStringBuilder.Append("List<Class_" + lKey + "> ");
              xStringBuilder.Append(lKey);
              xStringBuilder.AppendLine(";");
            }
          }
        }
        xStringBuilder.AppendLine(" }");
        xStringBuilder.AppendLine();
      }
    } //

    public void DictionariesToClasses(string xRootName, int xIndent, object xObject) {
      if (xObject == null) return;
      Type lType = xObject.GetType();

      if (lType.IsValueType || (xObject is string)) return;
      var lDictionary = xObject as Dictionary<string, object>;
      if (lDictionary != null) {
        object lObj;
        if (!_Classes.TryGetValue(xRootName, out lObj)) {
          _Classes.Add(xRootName, lDictionary);
        }
        else {
          foreach (var lKeyValuePair in lDictionary) {
            // This is a weakness of the program.
            // Two JSON objects must not use the same name.
            // We would have to compare the JSON objects and determine wether to create multiple or just one C# class.
            _Classes[lKeyValuePair.Key] = lKeyValuePair.Value;  // object type will be overridden !!!!!!
          }
          return;
        }

        foreach (var lKeyValuePair in lDictionary) {
          DictionariesToClasses(lKeyValuePair.Key, xIndent, lKeyValuePair.Value);
        }
        return;
      }

      var lArrayList = xObject as ArrayList;
      if (lArrayList != null) {
        object lObj;
        if (!_Classes.TryGetValue(xRootName, out lObj)) {
          lDictionary = new Dictionary<string, object>();
          _Classes.Add(xRootName, lDictionary);
        }
        else lDictionary = lObj as Dictionary<string, object>;

        var lElementType = ArrayListType(lArrayList);
        if (lElementType == typeof(Dictionary<string, object>)) {
          var lList = lArrayList.Cast<Dictionary<string, object>>().ToList(); // upgrade our object to have stronger types
          foreach (var lDict in lList) {
            foreach (var lKeyValuePair in lDict) {
              lDictionary[lKeyValuePair.Key] = lKeyValuePair.Value;  // object type will be overridden !!!!!!
            }
          }
          foreach (var lKeyValuePair in lDictionary) {
            DictionariesToClasses(lKeyValuePair.Key, xIndent, lKeyValuePair.Value);
          }
        }
        return;
      }
    } //

    private void Append(StringBuilder xStringbuilder, int xIndent, string xString) {
      xStringbuilder.Append(new String(' ', xIndent));
      xStringbuilder.Append(xString);
    } //

    private void Newline(StringBuilder xStringbuilder) {
      xStringbuilder.AppendLine();
    } //

    private Type ArrayListType(ArrayList xArrayList) {
      var lTypes = new Dictionary<string, Type>();
      Type lType;
      string lTypeName;

      foreach (object o in xArrayList) {
        if (o == null) lType = typeof(object);
        else lType = o.GetType();
        lTypeName = lType.Name;
        if (!lTypes.ContainsKey(lTypeName)) lTypes.Add(lTypeName, lType);
      }

      if (lTypes.Count == 1) return lTypes.Values.First();  // distinct
      return typeof(object);
    } //

  } // class
} // namespace