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

Ankit Agarwal

4y2.9kLINQ

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.

3 Replies

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

Sachin Singh4y ago
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();
+1
Ankit Agarwal4y ago
Sorry, this is wrong answer.,

I have a datatable already, i want to convert data table to Nested Json with desire format.
+1
Arun Raja4y ago
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;
}
}
+1