Building linq query to build json response object

I am having a hard time writing LINQ query for building JSON response. Can some one help me with this?
Requirement: A movie can be produced by two productions and many assistant directors but movie will have a single director.
I am fetching this data from DB

PRODUCTIONID MOVIENAME DID PLACE DNAME AID ANAME

-----------------------------------------------------------------------------------------------------------------
1 DDLJ 11 DELHI RAJ       111       Name 1
1 DDLJ 11 DELHI RAJ 112       Name 2
1 DDLJ 11 DELHI RAJ       113       Name 3
1 DDLJ 11 DELHI   RAJ 114       NAME 4

2 DDLJ 11 DELHI RAJ       111       Name 1
2 DDLJ 11 DELHI RAJ 112       Name 2
2 DDLJ 11 DELHI RAJ       113       Name 3
2 DDLJ 11 DELHI   RAJ 114       NAME 4

2 DON2 22 NOIDA SAM       221       Name 5
2 DON2 22 NOIDA SAM       222       Name 6
2 DON2 22 NOIDA   SAM       223       Name 7
2 DON2       22 NOIDA   SAM       224       NAME 8

My JSON response should be like:

{

"Movies": [
{
"ProductionID": "string",
"MovieInfo": [
{
"MovieName": "string",
"Directorinfo": [
{
"Did": "string",
"Dname": "string",
"Place": "string"
}
],
"AssistantDirectors": [
{
"Aid": "string",
"Aname": "string",
}
]
}
]
}
]
}

}

Sample output that is expected:

"content": [
{
"Movies": [
{
"ProductionID”: “1”,
"MovieInfo": [
{
"MovieName": "DDLJ",
"Directorinfo": [
{
"Did": "11",
"Dname": "RAJ",
"Place": "delhi"
}
],
"AssistantDirectors": [
{
"Aid": "111",
"Aname": "NAME 1"
},
{
"Aid": "112",
"Aname": "NAME 2"
},
{
"Aid": "113",
"Aname": "NAME 3"
},
{
"Aid": "114",
"Aname": "NAME 4"
}
]
}
]
},
{
"ProductionID": "2",
"MovieInfo": [
{
"MovieName": "DDLJ",
"Directorinfo": [
{
"Did": "11",
"Dname": "RAJ",
"Place": "delhi"
}
],
"AssistantDirectors": [
{
"Aid": "111",
"Aname": "NAME 1"
},
{
"Aid": "112",
"Aname": "NAME 2"
},
{
"Aid": "113",
"Aname": "NAME 3"
},
{
"Aid": "114",
"Aname": "NAME 4"
}
]
},

{
"Moviename": "DON",
"Directorinfo": [
{
"Did": "22",
"DName": "SAM",
"Place": "NOIDA"
}
],
"AssistantDirectors ": [
{
"AID": "221",
"aName": "NAME 5"
},
{
"AID": "222",
"aName": "NAME 6"
},
{
"AID": "223",
"aName": "NAME 7"
},
{
"AID": "224",
"aName": "NAME 8"
},

]
}
]
}
]
}
],

}
}

create a class as shown below and you are all set

// Root myDeserializedClass = JsonConvert.DeserializeObject(myJsonResponse);
public class Directorinfo
{
[JsonProperty("Did")]
public string Did { get; set; }
[JsonProperty("Dname")]
public string Dname { get; set; }
[JsonProperty("Place")]
public string Place { get; set; }
}
public class AssistantDirector
{
[JsonProperty("Aid")]
public string Aid { get; set; }
[JsonProperty("Aname")]
public string Aname { get; set; }
}
public class MovieInfo
{
[JsonProperty("MovieName")]
public string MovieName { get; set; }
[JsonProperty("Directorinfo")]
public List Directorinfo { get; set; }
[JsonProperty("AssistantDirectors")]
public List AssistantDirectors { get; set; }
}
public class Movy
{
[JsonProperty("ProductionID")]
public string ProductionID { get; set; }
[JsonProperty("MovieInfo")]
public List MovieInfo { get; set; }
}
public class Root
{
[JsonProperty("Movies")]
public List Movies { get; set; }
}

now just retrieve data by joining the tables and feel the root class Movies object it will be automatically translated to your desired JSON.

1 Reply

Know the answer? Post it — somebody with the same question will find it here.

Sachin Singh5y ago
create a class as shown below and you are all set
1. // Root myDeserializedClass = JsonConvert.DeserializeObject(myJsonResponse);
2. public class Directorinfo
3. {
4. [JsonProperty("Did")]
5. public string Did { get; set; }
7. [JsonProperty("Dname")]
8. public string Dname { get; set; }
10. [JsonProperty("Place")]
11. public string Place { get; set; }
12. }
14. public class AssistantDirector
15. {
16. [JsonProperty("Aid")]
17. public string Aid { get; set; }
19. [JsonProperty("Aname")]
20. public string Aname { get; set; }
21. }
23. public class MovieInfo
24. {
25. [JsonProperty("MovieName")]
26. public string MovieName { get; set; }
28. [JsonProperty("Directorinfo")]
29. public List Directorinfo { get; set; }
31. [JsonProperty("AssistantDirectors")]
32. public List AssistantDirectors { get; set; }
33. }
35. public class Movy
36. {
37. [JsonProperty("ProductionID")]
38. public string ProductionID { get; set; }
40. [JsonProperty("MovieInfo")]
41. public List MovieInfo { get; set; }
42. }
44. public class Root
45. {
46. [JsonProperty("Movies")]
47. public List Movies { get; set; }
48. }
now just retrieve data by joining the tables and feel the root class Movies object it will be automatically translated to your desired JSON.
0