Blog Archives
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:
- 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.
- The resulting structure is then used to build classes. These are converted to legible C# source code.
- The LINQ command is inserted. You can see the result in the TextBox titled ‘C# source code output’.
- 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.
- 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
C# Hardcore Programming 