How to bind datatable in nested json using LINQ in C#

Question

Hello I want to try datatable to nested json using c# but not getting result according to me. Output Should be: [{ Semesters: [{ Semester: Semester I CourseList: [{ Coursename: Accounting for Managers Credit: 4 CourseCode: ANK001 Specialization:  } { Coursename: Business Communication Credit: 4 CourseCode: ANK001 Specialization:  } ] } { Semester: Semester II CourseList: [{ Coursename: Accounting for Managers Credit: 4 CourseCode: ANK001 Specialization:  } { Coursename: Business Communication Credit: 4 CourseCode: ANK001 Specialization:  } ] } { Semester: Semester III CourseList: [{ Coursename: Accounting for Managers Credit: 4 CourseCode: ANK001 Specialization:  } { Coursename: Business Communication Credit: 4 CourseCode: ANK001 Specialization:  } ] } { Semester: Semester IV CourseList: [{ Coursename: Accounting for Managers Credit: 4 CourseCode: ANK001 Specialization:  } { Coursename: Business Communication Credit: 4 CourseCode: ANK001 Specialization:  } ] } ] Specializations: [{ Specialization Name: Specialization Name Secialization Semesters: [{ Semester: Semester I CourseList: [{ Coursename: Accounting for Managers Credit: 4 CourseCode: ANK001 Specialization:  } { Coursename: Business Communication Credit: 4 CourseCode: ANK001 Specialization:  } ] } { Semester: Semester II CourseList: [{ Coursename: Accounting for Managers Credit: 4 CourseCode: ANK001 Specialization:  } { Coursename: Business Communication Credit: 4 CourseCode: ANK001 Specialization:  } ] }] }] }] I have tried: var semesterData = dt.AsEnumerable().GroupBy(r => new { Semester = r[Semester] }).Select(c => new { Semester = c.Key.Semester CourseList = c.Select(s => new { CourseName = s.Field (Coursename) Credit = s.Field (Credit) CourseCode = s.Field (NewCourseProgarmcode) Specialization = s.Field (Specialization) }).Where(x => x.Specialization == ).Distinct().ToList() //Specialization = c.Select(s => new { Specialization = s.Field (Specialization) Coursename = s.Field (Coursename) }).Where(x => x.Specialization != ).Distinct().ToList() }).Distinct().ToList(); var specializationData = dt.AsEnumerable().GroupBy(r => new { SpecializationName = r[Specialization]} ).Select(c => new { SpecializationName = c.Select(s => new { SpecializationName = s.Field (Specialization) }).Where(x => x.SpecializationName != ).Distinct().ToList() //CourseName = c.Select(s => new { Coursename = s.Field (Coursename) Specialization = s.Field (Specialization) }).Where(x => x.Specialization == ).Distinct().ToList() }).Distinct().ToList(); var result = semesterData +  + specializationData; return Request.CreateResponse(HttpStatusCode.OK result); Please help me.

Sachin Singh · Answer

Test this public class Course { public string Coursename { get; set; } public string Credit { get; set; } public string CourseCode { get; set; } public string Specialization { get; set; } } public class Semester { public string SemesterName { get; set; } public List CourseList { get; set; } } var res=dt.AsEnumerable().GroupBy(x => x.Field ( Semester )).Select(g=> new Semester(){ SemesterName =g.Key; CourseList=g.Select(y=> new Course() { Coursename =y[ Coursename ].ToString() Credit =y[ Credit ].ToString() CourseCode =y[ CourseCode ].ToString() Specialization =y[ Specialization ].ToString() }).ToList(); }).ToList();

Ankit Agarwal · Answer

Sorry this is wrong answer. I have a datatable already i want to convert data table to Nested Json with desire format.

Arun Raja · Answer

public DataTable GetDataTableFromJsonString(string json) { DataTable dt = new DataTable(); try { dt.Columns.AddRange(new DataColumn[] { new DataColumn(firstName) new DataColumn(middleName) new DataColumn(lastName) new DataColumn(npi) new DataColumn(nationalProviderIdentifierType) //new DataColumn(state) new DataColumn(firstApprovedDate) new DataColumn(yearsInMedicare) new DataColumn(pecosEnrollmentDate) new DataColumn(newlyEnrolled) new DataColumn(organizations) new DataColumn(specialty) new DataColumn(prvdrOrgName) new DataColumn(addressLineOne) new DataColumn(addressLineTwo) new DataColumn(zip) new DataColumn(state) new DataColumn(individualScenario)}); var jsonLinq = JObject.Parse(json); JToken organizations; string firstName = string.Empty; string middleName = string.Empty; string lastName = string.Empty; string npi1 = string.Empty; string nationalProviderIdentifierType = string.Empty; // string state = string.Empty; string firstApprovedDate = string.Empty; string yearsInMedicare = string.Empty; string pecosEnrollmentDate = string.Empty; string newlyEnrolled = string.Empty; string organizations1 = string.Empty; string specialty = string.Empty; string prvdrOrgName = string.Empty; string addressLineOne = string.Empty; string addressLineTwo = string.Empty; string city = string.Empty; string zip = string.Empty; string state = string.Empty; string individualScenario = string.Empty; string lowVolumeStatusReasons = string.Empty; string lowVolumeStatusReasons1 = string.Empty; string npi = String.Empty; // string firstName = string.Empty; List results = jsonLinq.Children().ToList(); foreach (JProperty item in results) { item.CreateReader(); //if (item.Name == data) //{ organizations = item.Value; foreach (JObject jProp in organizations.Children().ToList()) { organizations = jProp[organizations].ToString(); foreach (JProperty jitem in jProp[organizations].Children().ToList()) { if (jitem.Name == firstName) { firstName = jitem.Value.ToString(); } if (jitem.Name == middleName) { middleName = jitem.Value.ToString(); } if (jitem.Name == lastName) { lastName = jitem.Value.ToString(); } if (jitem.Name == npi) { npi = jitem.Value.ToString(); } if (jitem.Name == nationalProviderIdentifierType) { nationalProviderIdentifierType = jitem.Value.ToString(); } if (jitem.Name == firstApprovedDate) { firstApprovedDate = jitem.Value.ToString(); } if (jitem.Name == yearsInMedicare) { yearsInMedicare = jitem.Value.ToString(); } if (jitem.Name == pecosEnrollmentDate) { pecosEnrollmentDate = jitem.Value.ToString(); } if (jitem.Name == newlyEnrolled) { newlyEnrolled = jitem.Value.ToString(); } //if (jitem.Name == organizations) //{ // organizations = jitem.Value.ToString(); //} if (jitem.Name == organizations) { if (jitem.Value.Type == JTokenType.Array) { JArray items = (JArray)jitem.Value; prvdrOrgName = items[0].SelectToken(value).ToString(); } if (jitem.Name == specialty) { specialty = jitem.Value.ToString(); } } } foreach (JProperty jitem in jProp[organizations].Children().ToList()) { if (jitem.Name == prvdrOrgName) { prvdrOrgName = jitem.Value.ToString(); } if (jitem.Name == addressLineOne) { addressLineOne = jitem.Value.ToString(); } if (jitem.Name == addressLineTwo) { addressLineTwo = jitem.Value.ToString(); } if (jitem.Name == city) { city = jitem.Value.ToString(); } if (jitem.Name == zip) { zip = jitem.Value.ToString(); } if (jitem.Name == state) { state = jitem.Value.ToString(); } if (jitem.Name == individualScenario) { individualScenario = jitem.Value.ToString(); } if (jitem.Name == lowVolumeStatusReasons) { if (jitem.Value.Type == JTokenType.Array) { JArray items = (JArray)jitem.Value; lowVolumeStatusReasons = items[0].SelectToken(value).ToString(); } } if (jitem.Name == lowVolumeStatusReasons) { if (jitem.Value.Type == JTokenType.Array) { JArray items = (JArray)jitem.Value; lowVolumeStatusReasons1 = items[0].SelectToken(value).ToString(); } } } } dt.Rows.Add(firstName middleName lastName npi1 nationalProviderIdentifierType firstApprovedDate yearsInMedicare pecosEnrollmentDate newlyEnrolled organizations prvdrOrgName addressLineOne addressLineTwo city zip state individualScenario); //} } return dt; } catch (Exception ex) { return dt; } }