Saturday, August 10, 2013

Join Multiple DataTables using LINQ

Let us say we have three DataTables Student Details, Student Marks and Subjects. And we need to see the Marks of the Students in Each Subjects, so now we need to Join these three Tables with the Primary and Foreign key of these tables.

Student Details
StudentName
StudentID (Primary Key)
AAA
111
BBB
222

Student Marks
StudentID (Foreign Key)
SubjectID (Foreign Key)
Marks
111
1
89
111
2
90
222
1
95
222
2
93

 Subjects
SubjectID (Primary Key)
SubjectName
1
Chemistry
2
Physical Science

 Expected Results
StudentID
StudetName
Subject
Marks
111
AAA
Chemistry
89
111
AAA
Physical Science
90
222
BBB
Chemistry
95
222
BBB
Physical Science
93




Let us consider all the three tables are populated with the data mentioned above and now the below code will join these three DataTables and results the expected results.

List<summary> sum = (from dataRows1 in StudentDetails.AsEnumerable()
                                 join dataRows2 in StudentMarks.AsEnumerable()
                                 on Convert.ToString(dataRows1["StudentID"]).ToUpper() equals Convert.ToString(dataRows2["StudentID"]).ToUpper() into position1
                                 from dataRows2 in position1
                                 join dataRows3 in SubjectDetails.AsEnumerable()
                                 on dataRows2["SubjectID"] equals dataRows3["SubjectID"] into position2
                                 from dataRows3 in position2.DefaultIfEmpty()
                                 select new Summary
                                 {
                                     StudentID = dataRows1 != null ? Convert.ToString(dataRows1["StudentID"]) : "",
                                     StudentName = dataRows1 != null ? Convert.ToString(dataRows1["StudentName"]) : "",
                                     Subject = dataRows3 != null ? Convert.ToString(dataRows3["SubjectName"]) : "",
                                     Mark = dataRows2 != null ? Convert.ToString(dataRows2["Marks"]) : ""
                                 }).ToList();



Output of the above code Executed in Console Application.
Related Posts Plugin for WordPress, Blogger...