Click here to Skip to main content
15,897,291 members
Search within:


How to make nested json object?
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
API
MVC4
ASP.NET5
Hi Guys,

I am trying to make a nested JSON Object in Web API, but i am facing some issues, please anyone can help

I want the same in below format

{
"country": [{
"id": "",
"name": "",
"Location": [{
"id": "",
"name": "",
"sublocation": [{
"id": "",
"name": ""
}]
}]
}]
}

What I have tried:

Below is the my DataTable and Linq query

C#
DataTable _dt = new DataTable();
                _balL = new DALLocation();
                _dt = _balL.GetLocations();
                _gloc.country = (from DataRow _dr in _dt.Rows
                                 select new Country()
                                 {
                                     country_id = Convert.ToInt16(_dr["Country_Id"].ToString()),
                                     country_name = _dr["Country_Name"].ToString(),
                                     location = (from DataRow _drloc in _dr.ItemArray
                                                 where _drloc["country_id"].ToString() == _dr["country_id"].ToString()
                                                 select new Location()
                                                 {
                                                     addedon = _drloc.IsNull("Location_Addedon") ? (DateTime?)null : Convert.ToDateTime(_drloc["Location_Addedon"].ToString()),
                                                     createdby = _drloc["Location_CreatedBy"].ToString(),
                                                     id = _drloc.IsNull("Location_ID") ? Convert.ToInt16(_drloc["Location_ID"].ToString()) : (Int16?)null,
                                                     location_lat = _drloc["Location_Lat"].ToString(),
                                                     location_long = _drloc["Location_Long"].ToString(),
                                                     name = _drloc["Location_Name"].ToString(),
                                                     sub_location = (from DataRow _drsubloc in _drloc.ItemArray
                                                                     where _drsubloc["Location_ID"].ToString() == _drloc["Location_ID"].ToString() && !_drsubloc.IsNull("Location_ID")
                                                                     select new Sub_Location()
                                                                     {
                                                                         addedon = _drsubloc.IsNull("SubLocation_AddedOn") ? (DateTime?)null : Convert.ToDateTime(_drsubloc["SubLocation_AddedOn"].ToString()),
                                                                         id = _drsubloc.IsNull("SubLocation_ID") ? (Int16?)null : Convert.ToInt16(_drsubloc["SubLocation_ID"].ToString()),
                                                                         sublocation_name = _drsubloc["SubLocation_Name"].ToString()
                                                                     })
                                                 })
                                 }).ToList();




How I am getting the data from DB

C#
1	7	Abkhazia	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
2	93	Afghanistan	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
3	355	Albania	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
4	213	Algeria	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
5	1684	American Samoa	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
6	376	Andorra	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
7	244	Angola	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
8	1264	Anguilla	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
9	1268	Antigua and Barbuda	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
10	54	Argentina	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
11	374	Armenia	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
12	297	Aruba	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
13	61	Australia	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
14	43	Austria	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
15	994	Azerbaijan	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
16	1242	Bahamas	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
17	973	Bahrain	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
18	880	Bangladesh	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
19	1246	Barbados	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
20	375	Belarus	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
21	32	Belgium	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
22	501	Belize	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
23	229	Benin	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
24	1441	Bermuda	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
25	975	Bhutan	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
26	591	Bolivia	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
27	387	Bosnia and Herzegovina	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
28	267	Botswana	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
29	55	Brazil	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
30	673	Brunei Darussalam	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
31	359	Bulgaria	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
32	226	Burkina Faso	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
33	257	Burundi	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
34	855	Cambodia	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
35	237	Cameroon	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
36	1	Canada	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
37	238	Cape Verde	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
38	1345	Cayman Islands	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
39	236	Central African Republic	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
40	235	Chad	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
41	56	Chile	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
42	86	China	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
43	57	Colombia	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
44	269	Comoros	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
45	242	Congo	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
46	243	Congo, Democratic Republic	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
47	682	Cook Islands	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
48	506	Costa Rica	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
49	225	Cote d'Ivoire	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
50	385	Croatia	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
51	53	Cuba	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
52	357	Cyprus	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
53	420	Czech Republic	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
54	45	Denmark	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
55	253	Djibouti, Republic of	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
56	1767	Dominica, Commonwealth of	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
57	1	Dominican Republic	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
58	593	Ecuador	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
59	20	Egypt	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
60	503	El Salvador	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
61	240	Equatorial Guinea	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
62	372	Estonia	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
63	251	Ethiopia	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
64	500	Falkland Islands	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
65	298	Faroe Islands	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
66	679	Fiji	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
67	358	Finland	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
68	33	France	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
69	594	French Guiana	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
70	689	French Polynesia	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
71	241	Gabon	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
72	220	Gambia	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
73	995	Georgia	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
74	49	Germany	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
75	233	Ghana	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
76	350	Gibraltar	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
77	30	Greece	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
78	299	Greenland	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
79	1473	Grenada	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
80	590	Guadeloupe	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
81	1671	Guam	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
82	502	Guatemala	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
83	44	Guernsey	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
84	224	Guinea	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
85	245	Guinea-Bissau	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
86	592	Guyana	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
87	509	Haiti	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
88	504	Honduras	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
89	852	Hong Kong	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
90	36	Hungary	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
91	354	Iceland	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
92	91	India	2	Powai	19.0330° N	73.0297° E	2016-12-05 16:58:36.490	20051367	NULL	NULL	NULL
92	91	India	3	Vadodara	19.0760° N	72.8777° E	2016-12-05 16:58:36.490	20051367	NULL	NULL	NULL
92	91	India	7	Chennai	19.0330° N	73.0297° E	2016-12-09 18:04:31.670	20051367	NULL	NULL	NULL
92	91	India	8	B'lore	19.0330° N	73.0297° E	2016-12-09 18:06:12.357	20051367	NULL	NULL	NULL
92	91	India	14	Faridabad	19.0330° N	73.0297° E	2016-12-13 11:41:09.350	20051367	NULL	NULL	NULL
92	91	India	15	MFF Hazira	19.0330° N	73.0297° E	2016-12-13 11:41:17.393	20051367	NULL	NULL	NULL
92	91	India	16	MFF Kattupalli	19.0330° N	73.0297° E	2016-12-13 11:41:35.847	20051367	NULL	NULL	NULL
92	91	India	17	MFY Sohar	19.0330° N	73.0297° E	2016-12-13 11:41:57.000	20051367	NULL	NULL	NULL
92	91	India	18	Project Site Onshore	19.0330° N	73.0297° E	2016-12-13 11:42:02.080	20051367	NULL	NULL	NULL
92	91	India	19	Project Site CS	19.0330° N	73.0297° E	2016-12-13 11:42:13.700	20051367	NULL	NULL	NULL
92	91	India	20	Project Site Offshore	19.0330° N	73.0297° E	2016-12-13 11:42:19.813	20051367	NULL	NULL	NULL
93	62	Indonesia	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
94	98	Iran	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
95	964	Iraq	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
96	353	Ireland	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
97	44	Isle of Man	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
98	972	Israel	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
99	39	Italy	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
100	1876	Jamaica	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
101	81	Japan	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
102	44	Jersey	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
103	962	Jordan	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
104	7	Kazakhstan	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
105	254	Kenya	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
106	82	Korea, Republic of	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
107	965	Kuwait	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
108	996	Kyrgyzstan	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
109	856	Laos	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
110	371	Latvia	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
111	961	Lebanon	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
112	266	Lesotho	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
113	231	Liberia	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
114	218	Libya	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
115	423	Liechtenstein	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
116	370	Lithuania	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
117	352	Luxembourg	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
118	853	Macau	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
119	389	Macedonia	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
120	261	Madagascar	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
121	265	Malawi	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
122	60	Malaysia	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
123	960	Maldives	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
124	223	Mali	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
125	356	Malta	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
126	596	Martinique	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
127	222	Mauritania	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
128	230	Mauritius	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
129	52	Mexico	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
130	373	Moldova	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
131	377	Monaco	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
132	976	Mongolia	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
133	382	Montenegro	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
134	1664	Montserrat	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
135	212	Morocco	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
136	258	Mozambique	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
137	95	Myanmar	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
138	264	Namibia	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
139	674	Nauru	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
140	977	Nepal	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
141	31	Netherlands	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
142	599	Netherlands Antilles	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
143	687	New Caledonia	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
144	64	New Zealand	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
145	505	Nicaragua	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
146	227	Niger	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
147	234	Nigeria	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
148	672	Norfolk Island	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
149	1670	Northern Mariana Islands	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
150	47	Norway	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
151	968	Oman	6	PDO office	19.0760° N	72.8777° E	2016-12-05 16:58:36.490	20051367	NULL	NULL	NULL
152	92	Pakistan	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
153	680	Palau	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
154	970	Palestinian Territory	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
155	507	Panama	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
156	675	Papua New Guinea	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
157	595	Paraguay	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
158	51	Peru	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
159	63	Philippines	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
160	48	Poland	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
161	351	Portugal	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
162	1	Puerto Rico	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
163	974	Qatar	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
164	262	Reunion	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
165	40	Romania	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
166	7	Russian Federation	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
167	250	Rwanda, Republic of	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
168	1869	Saint Kitts and Nevis	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
169	1758	Saint Lucia	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
170	1784	Saint Vincent and The Grenadines	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
171	685	Samoa	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
172	378	San Marino, Republic of	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
173	239	Sao Tome and Principe	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
174	966	Saudi Arabia	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
175	221	Senegal	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
176	381	Serbia	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
177	248	Seychelles	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
178	232	Sierra Leone	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
179	65	Singapore	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
180	1721	Sint Maarten	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
181	421	Slovakia	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
182	386	Slovenia	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
183	677	Solomon Islands	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
184	252	Somalia	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
185	27	South Africa	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
186	7	South Ossetia	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
187	211	South Sudan	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
188	34	Spain	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
189	94	Sri Lanka	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
190	249	Sudan	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
191	597	Suriname	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
192	268	Swaziland	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
193	46	Sweden	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
194	41	Switzerland	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
195	963	Syria	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
196	886	Taiwan	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
197	992	Tajikistan	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
198	255	Tanzania	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
199	66	Thailand	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
200	670	Timor L'este	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
201	228	Togo	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
202	676	Tonga	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
203	1868	Trinidad and Tobago	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
204	216	Tunisia	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
205	90	Turkey	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
206	993	Turkmenistan	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
207	1649	Turks and Caicos Islands	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
208	256	Uganda	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
209	380	Ukraine	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
210	971	United Arab Emirates	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
211	44	United Kingdom	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
212	1	United States	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
213	598	Uruguay	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
214	998	Uzbekistan	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
215	678	Vanuatu	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
216	58	Venezuela	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
217	84	Vietnam	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
218	1284	Virgin Islands, British	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
219	1340	Virgin Islands, U.S.	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
220	967	Yemen	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
221	260	Zambia	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
222	263	Zimbabwe	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
223	0	NA	1	NA	0	0	2016-12-05 16:58:36.490	20051367	NULL	NULL	NULL
Posted
Updated 13-Dec-16 4:36am
Add a Solution

1 solution

Quote:
from DataRow _drloc in _dr.ItemArray

The DataRow.ItemArray property[^] returns an array containing the values from the current DataRow. None of those values will be other DataRows, so that query will throw an InvalidCastException.

NB: When you're asking a question about code that throws an exception, you should always include the full details of the exception!

Based on your sample data, your database query has "flattened" three separate tables - countries, locations, and sub-locations. You'll find it easier to process if you keep the tables separate, and load them into separate DataTables:
C#
DataSet data = new DataSet();

using (var connection = new SqlConnection("..."))
using (var command = new SqlCommand("SELECT ... FROM Countries; SELECT ... FROM Locations; SELECT ... FROM SubLocations;", connection))
{
    var da = new SqlDataAdapter(command);
    da.Load(data);
}

DataTable countries = data.Tables[0];
DataTable locations = data.Tables[1];
DataTable subLocations = data.Tables[2];

DataRelation fkCountryLocations = data.Relations.Add("CountryLocations", countries.Columns["Country_Id"], locations.Columns["Country_Id"]);
DataRelation fkLocationSubLocations = data.Relations.Add("LocationSubLocations", locations.Columns["Location_Id"], subLocations.Columns["Location_Id"]);

You can then navigate between the tables to build your JSON data:
C#
_gloc.country = countries.Rows.Select(row => new Country
{
    country_id = Convert.ToInt16(row["Country_Id"]),
    country_name = row["Country_Name"].ToString(),
    
    location = row.GetChildRows(fkCountryLocations).Select(locationRow => new Location
    {
        addedon = locationRow.IsNull("Location_Addedon") ? (DateTime?)null : Convert.ToDateTime(locationRow["Location_Addedon"]),
        createdby = locationRow["Location_CreatedBy"].ToString(),
        id = locationRow.IsNull("Location_ID") ? (Int16?)null : Convert.ToInt16(locationRow["Location_ID"]),
        location_lat = locationRow["Location_Lat"].ToString(),
        location_long = locationRow["Location_Long"].ToString(),
        name = locationRow["Location_Name"].ToString(),
        
        sub_location = locationRow.GetChildRows(fkLocationSubLocations).Select(subLocationRow => new Sub_Location
        {
            addedon = subLocationRow.IsNull("SubLocation_AddedOn") ? (DateTime?)null : Convert.ToDateTime(subLocationRow["SubLocation_AddedOn"]),
            id = subLocationRow.IsNull("SubLocation_ID") ? (Int16?)null : Convert.ToInt16(subLocationRow["SubLocation_ID"]),
            sublocation_name = subLocationRow["SubLocation_Name"].ToString()
        }).ToList(),
        
    }).ToList(),
    
}).ToList();

NB: When you're calling Convert.ToSOMETHING(...), you don't need to call .ToString() on the value you're converting.
 
Share this answer
 
Posted
Comments
nik varma 14-Dec-16 2:17am    
Thanks Richard

I found the second way and i will try your solution if its not possible with the this solution

now i am able to got the same json format but issue is that i have added a rows in sublocation table and now when i am trying to get the json data all working fine but in location of sublocation all the sublocations are coming instend of respective location of sublocation

Please help, i guess m missing something

_gloc.country = _dt.AsEnumerable()
.GroupBy(c => new { country_id = c["country_id"], country_name = c["country_name"] })
.Select(co => new Country
{
country_id = Convert.ToInt16(co.Key.country_id.ToString()),
country_name = Convert.ToString(co.Key.country_name.ToString()),
location = co.Select(l => new Location()
{
name = l["Location_Name"].ToString(),
addedon = l.IsNull("Location_Addedon") ? (DateTime?)null : Convert.ToDateTime(l["Location_Addedon"]),
createdby = l["Location_CreatedBy"].ToString(),
id = l.IsNull("Location_ID") ? (int?)null : Convert.ToInt16(l["Location_ID"]),
location_lat = l["Location_Lat"].ToString(),
location_long = l["Location_Long"].ToString(),
sub_location = co
.Select(subl => new Sub_Location()
{
id = subl.IsNull("SubLocation_ID") ? (Int16?)null : Convert.ToInt16(subl["SubLocation_ID"]),
sublocation_name = subl["SubLocation_Name"].ToString(),
addedon = subl.IsNull("SubLocation_AddedOn") ? (DateTime?)null : Convert.ToDateTime(subl["SubLocation_AddedOn"].ToString())
}).Where(x => x.id != null).ToList()
}).Where(ll => ll.id != null).ToList()
}).ToList();
Richard Deeming 14-Dec-16 7:00am    
You've grouped by country, but you also need to group by location:

_dt.AsEnumerable
    .GroupBy(c => Convert.ToInt16(c["country_id"]))
    .Select(co => new Country
    {
        country_id = co.Key,
        country_name = co.First()["country_name"].ToString(),
        
        location = co
            .Where(l => !l.IsNull("Location_ID"))
            .GroupBy(l => Convert.ToInt16(l["Location_ID"]))
            .Select(l => new Location
            {
                id = l.Key,
                name = l.First()["Location_Name"].ToString(),
                addedon = l.First().IsNull("Location_AddedOn") ? default(DateTime?) : Convert.ToDateTime(l.First()["Location_AddedOn"]),
                createdby = l.First()["Location_CreatedBy"].ToString(),
                location_lat = l.First()["Location_Lat"].ToString(),
                location_long = l.First()["Location_Long"].ToString(),
                
                sub_location = l
                    .Where(s => !s.IsNull("SubLocation_ID"))
                    .Select(s => new Sub_Location
                    {
                        id = Convert.ToInt16(s["SubLocation_ID"]),
                        sublocation_name = s["SubLocation_Name"].ToString(),
                        addedon = s.IsNull("SubLocation_AddedOn") ? default(DateTime?) : Convert.ToDateTime(s["SubLocation_AddedOn"])
                    })
                    .ToList()
            })
            .ToList()
    })
    .ToList()
nik varma 15-Dec-16 8:04am    
Hi Richard, Thank you for the reply, but i case there will be some location id null and it is giving a error message, for the reason Convert.ToInt16(l["Location_ID"]) is coming null

Please help with the same
Richard Deeming 15-Dec-16 8:08am    
You've left out the previous line:
.Where(l => !l.IsNull("Location_ID"))

That excludes any rows with a null Location_ID.

There's a similar line for the sub-locations.
Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month
Related Questions
How to get a specific field value from a nested JSON in C#?
Nested JSON to datatable
How do I get value from nested json object in newtonsoft.json
How to convert a json string to json object?
Serialize json string c# nested objects
Deserializing nested json to C# objects and accessing objects
How to read a nested JSON / array (by keys)
Nested Json Object display in different dropdownlist using jquery
Json serialez object egypt tax
How do I generate nested json using MSSQL database.?

Advertise
Privacy
Cookies
Terms of Use
Last Updated 13 Dec 2016
Layout: fixed | fluid
Copyright © CodeProject, 1999-2024
All Rights Reserved.

Web03 2.8:2024-04-02:1

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900