JSON request using LINQ

Question

I have a Json response that I get from an API call. It has several nested levels, as shown below (this is a snippet):

"Items": [ { "Result": { "Id": "191e24b8-887d-e111-96ec-000c29128cee", "Name": "Name", "StartDate": "2012-04-03T00:00:00+01:00", "EndDate": null, "Status": { "Name": "Active", "Value": 5 }, "Client": { "Id": "35ea10da-b8d5-4ef8-bf23-c829ae90fe60", "Name": "client Name", "AdditionalItems": {} }, "ServiceAgreement": { "Id": "65216699-a409-44b0-8294-0e995eb05d9d", "Name": "Name", "AdditionalItems": { "ScheduleBased": true, "PayFrequency": { "Id": "981acb72-8291-de11-98fa-005056c00008", "Name": "Weekly", "AdditionalItems": {} }, "PayCycle": [ { "Name": "Schedule Based", "ScheduleBased": true, "SelfBilling": false, "Id": "a8a2ecc4-ff79-46da-a135-743b57808ec3", "CreatedOn": "2011-09-16T23:32:19+01:00", "CreatedBy": "System Administrator", "ModifiedOn": "2011-09-16T23:32:19+01:00", "ModifiedBy": "System Administrator", "Archived": false } ] } }, } ] ... 

What I want to do is get data from PayCycle node using Linq. For example, I can get elements with a value of true using Result.ServiceAgreement.AdditionalItems.SchedultedBBed using the following Linq in the controller:

 var result = from p in data["Data"]["Items"].Children() where (bool)p["Result"]["ServiceAgreement"]["AdditionalItems"]["ScheduleBased"] == true select new { Name = (string)p["Result"]["Client"]["Name"], Id = (string)p["Result"]["Client"]["Id"] }; 

Now I need to get the Result.ServiceAgreement.AdditionalItems.Paycycle.ScheduleBased and SelfBilling . How to do this if PayCycle is also an array, how can I get the same data as in Data.Items in Linq above so that I can have a where where filter on both of these elements?

Answer 1

You can deserialize JSON into a dynamic object, and then use Linq for the objects:

  [TestMethod] public void TestMethod1() { const string json = @"""Items"": [ { ""Result"": { ""Id"": ""191e24b8-887d-e111-96ec-000c29128cee"", ""Name"": ""Name"", ""StartDate"": ""2012-04-03T00:00:00+01:00"", ""EndDate"": null, ""Status"": { ""Name"": ""Active"", ""Value"": 5 }, ""Client"": { ""Id"": ""35ea10da-b8d5-4ef8-bf23-c829ae90fe60"", ""Name"": ""client Name"", ""AdditionalItems"": {} }, ""ServiceAgreement"": { ""Id"": ""65216699-a409-44b0-8294-0e995eb05d9d"", ""Name"": ""Name"", ""AdditionalItems"": { ""ScheduleBased"": true, ""PayFrequency"": { ""Id"": ""981acb72-8291-de11-98fa-005056c00008"", ""Name"": ""Weekly"", ""AdditionalItems"": {} }, ""PayCycle"": [ { ""Name"": ""Schedule Based"", ""ScheduleBased"": true, ""SelfBilling"": false, ""Id"": ""a8a2ecc4-ff79-46da-a135-743b57808ec3"", ""CreatedOn"": ""2011-09-16T23:32:19+01:00"", ""CreatedBy"": ""System Administrator"", ""ModifiedOn"": ""2011-09-16T23:32:19+01:00"", ""ModifiedBy"": ""System Administrator"", ""Archived"": false } ] } } } } ]"; dynamic data = System.Web.Helpers.Json.Decode("{" + json + "}"); var result = from i in (IEnumerable<dynamic>)data.Items where i.Result.ServiceAgreement.AdditionalItems.ScheduleBased == true select new { i.Result.Client.Name, i.Result.Client.Id }; Assert.AreEqual(1, result.Count()); Assert.AreEqual("client Name", result.First().Name); Assert.AreEqual("35ea10da-b8d5-4ef8-bf23-c829ae90fe60", result.First().Id); } 

Please note that I had to add brackets {and} around your json string example, otherwise he doesn't like the .NET json parser.

Answer 2

To start, I am not familiar with writing LINQ / LAMBDA using this format [[some ”] [" thing "]. My first step was to create some classes / objects for placing data in order to facilitate the creation of any code afterwards.

eg.

 public class Result { public Guid Id { get; set; } public string Name { get; set; }, public DateTime StartDate { get; set; } //you get the idea } 

But maybe try the following:

  var result = from p in data["Data"]["Items"].Children() where (bool)p["Result"]["ServiceAgreement"]["AdditionalItems"]["ScheduleBased"] == true && (p["Result"]["ServiceAgreement"]["AdditionalItems"]["PayCycle"]).Where(o => o.["ScheduleBased"] == true) select new { Name = (string)p["Result"]["Client"]["Name"], Id = (string)p["Result"]["Client"]["Id"] };