DotNetCurry Logo

Querying JSON using LINQ

Posted by: Mahesh Sabnis , on 7/4/2016, in Category C#
Views: 36166
Abstract: JSON.NET is a great framework for working with JSON data. We will perform JSON Serialization and Deserialization operations and query data using LINQ.

JSON is not natively supported by .NET, so when we are developing applications using a .NET language like C#, it is important to make use of JSON.NET. JSON.NET is a high-performance JSON framework for .NET. We can download JSON.NET via the NuGet Package Manager with Visual Studio.

In this article, we will use Netwonsoft.Json package to work with JSON Serialization and Deserialization operations using LINQ.

The application is implemented using Visual Studio 2015, but this sample can be developed with Visual Studio 2013 too.

Step 1: Open Visual Studio and create a Console Application with name as JSON_LINQ_TOJSON. In this project, add Netwonsoft.Json using NuGet Package Manager. Right-click on the project name and select Manage NuGet Packages, this will open the NuGet Packages Windows, search for Netwonsoft.Json and install the package.

Step 2: In the project, add a new class file with name as ModelClasses.cs and add the following code into it.

using System.Collections.Generic;

namespace JSON_LINQ_TOJSON
{
public class Course
{
    public int CourseId { get; set; }
    public string CourseName { get; set; }
}
public class Student
{
    public int StudentId { get; set; }
    public string StudentName { get; set; }
    public string AcadmicYear { get; set; }
    public List<Course> Courses { get; set; }
}

public class StudentsDatabase : List<Student>
{
    public StudentsDatabase()
    {
        Add(new Student()
        {
            StudentId = 1,
            StudentName = "MS",
            AcadmicYear = "First",
            Courses = new List<Course>()
            {
                 new Course() {CourseId=101,CourseName="C++" },
                 new Course() {CourseId=102,CourseName="C" },
                 new Course() {CourseId=103,CourseName="Visual Basic" }
            }
        });

        Add(new Student()
        {
            StudentId = 2,
            StudentName = "LS",
            AcadmicYear = "Second",
            Courses = new List<Course>()
            {
                 new Course() {CourseId=101,CourseName="C++" },
                 new Course() {CourseId=104,CourseName="MVC" },
                 new Course() {CourseId=105,CourseName="AngularJS" }
            }
        });
        Add(new Student()
        {
            StudentId = 3,
            StudentName = "TS",
            AcadmicYear = "Third",
            Courses = new List<Course>()
            {
                 new Course() {CourseId=102,CourseName="C" },
                 new Course() {CourseId=104,CourseName="MVC" },
                 new Course() {CourseId=103,CourseName="Visual Basic" }
            }
        });
        Add(new Student()
        {
            StudentId = 4,
            StudentName = "VB",
            AcadmicYear = "First",
            Courses = new List<Course>()
            {
                 new Course() {CourseId=101,CourseName="C++" },
                 new Course() {CourseId=102,CourseName="C" },
                 new Course() {CourseId=103,CourseName="Visual Basic" }
            }
        });

        Add(new Student()
        {
            StudentId = 5,
            StudentName = "PB",
            AcadmicYear = "Second",
            Courses = new List<Course>()
            {
                 new Course() {CourseId=104,CourseName="MVC" },
                 new Course() {CourseId=105,CourseName="AngularJS" },
                 new Course() {CourseId=106,CourseName="KnockoutJS" }
            }
        });
        Add(new Student()
        {
            StudentId = 6,
            StudentName = "AB",
            AcadmicYear = "Third",
            Courses = new List<Course>()
            {
                 new Course() {CourseId=101,CourseName="C++" },
                 new Course() {CourseId=102,CourseName="C" },
                 new Course() {CourseId=103,CourseName="Visual Basic" }
            }
        });
    }
}

public class Employee
{
    public int EmpNo { get; set; }
    public string EmpName { get; set; }
    public string DeptName { get; set; }
}

public class EmployeesDatabase : List<Employee>
{
    public EmployeesDatabase()
    {
        Add(new Employee() {EmpNo=1,EmpName="A",DeptName="IT" });
        Add(new Employee() { EmpNo = 1, EmpName = "B", DeptName = "Systems" });
        Add(new Employee() { EmpNo = 1, EmpName = "C", DeptName = "IT" });
        Add(new Employee() { EmpNo = 1, EmpName = "D", DeptName = "Systems" });
        Add(new Employee() { EmpNo = 1, EmpName = "E", DeptName = "IT" });
        Add(new Employee() { EmpNo = 1, EmpName = "F", DeptName = "Systems" });
    }
}

}

The above class defines a Student class containing the Course list in it. The StudentsDatabase stores some values in it. This also has an  Employee class with EmployeesDatabase and some predefined values.

Step 3: In the Program.cs add the following code

using System;
using System.Linq;

using Newtonsoft.Json;
using Newtonsoft.Json.Linq;

namespace JSON_LINQ_TOJSON
{
class Program
{
    static void Main(string[] args)
    {
        //1. Get the Data in JSON Serialized Form
        string StudentsData = JsonConvert.SerializeObject(new StudentsDatabase(),Formatting.Indented);
        //Console.WriteLine(StudentsData);  

        //2. Convert the JSON string into an Array
        JArray studentsArray = JArray.Parse(StudentsData);


       //3. Read all Student Names

        var resStudents = (from s in studentsArray
                           select s["StudentName"]).ToList();

        Console.WriteLine("Only Student Names");
        foreach (var item in resStudents)
        {
            Console.WriteLine(item.Value<string>().ToString());
        }

        //4. Get only Course Details 
        Console.WriteLine();
        var result = (from s in studentsArray.Children()["Courses"]
                      select s).ToList();

        Console.WriteLine("Course Details");
        foreach (var item in result.Children().ToList())
        {
            Console.WriteLine(item.ToObject<Course>().CourseId + "\t" + item.ToObject<Course>().CourseName);
        }


        //5. Get the Data in JSON Serialized Form
        string employeesData = JsonConvert.SerializeObject(new EmployeesDatabase(), Formatting.Indented);

        //6. Convert the JSON string into an Array
        JArray employeesArray = JArray.Parse(employeesData);

        //7.
        var empGroupByDeptName = from e in employeesArray
                                 group e by e["DeptName"] into deptGroup
                           select new
                           {
                               DeptName = deptGroup.Key,
                               EmpCount = deptGroup.Count()
                               
                           };

        Console.WriteLine("Deptrtment \t\t Total Employees Employee");
        foreach (dynamic deptGrp in empGroupByDeptName)
        {
            Console.WriteLine(deptGrp.DeptName + " \t\t " + deptGrp.EmpCount);
        }

        Console.ReadLine();
    }
}
}

The above code has the following behavior.

(Node: Following line numbers matches with comments applied on code.)

1. Using JsonConvert.SerializeOnbject(), serialize the StudentsDatabase in JSON format.

2. Using JArray.Parse, store Student Data in JSON array.

3. Fire the JSON to LINQ Query to read all Student Names. The query specifies the name of JToken in []. E.g. s[“StudentName”].

4. The JSON data has Students which has Courses as Child array for one-to-many relationship. To read this the query use the Children() method of the JToken collection and read all Courses. The Course data is displayed on console by converting each JToken into the Course object using ToObject<T> generic method, here T is the name of the class in which JToken will be converted.

5. As explained in point 1, the Employee data is serialized into JSON string.

6. The Employee data is parsed into JArray.

7. The query is fired to calculate the Count of Employees in each department using GroupBy Caluse.

Run the Application, the following result will be shown

res-formatted

As we saw, JSON.NET offers an excellent approach for working with JSON data using LINQ support.

Was this article worth reading? Share it with fellow developers too. Thanks!
Share on LinkedIn
Share on Google+
Further Reading - Articles You May Like!
Author
Mahesh Sabnis is a DotNetCurry author and Microsoft MVP having over 17 years of experience in IT education and development. He is a Microsoft Certified Trainer (MCT) since 2005 and has conducted various Corporate Training programs for .NET Technologies (all versions). Follow him on twitter @maheshdotnet


Page copy protected against web site content infringement 	by Copyscape




Feedback - Leave us some adulation, criticism and everything in between!