Get Comma Separated Values From SQL Server Using Entity Framework (EF)

This article describes how to get column values in one comma separated string using Entity Framework (EF) .

Overview

Here I will explain a scenario in which a student can join multiple courses. To do this I am creating three tables, two master and one transaction. The tables are named Student, Course and trnjCourse_Studnet. The Student table has student information. The Course table has a course name and course ID. The transaction table contains the information about a student and courses is a one-to-many relationship. For Table scripts click here.

Now I need to write a LINQ query for students joining multiple courses, then the entire course name that is joined by that student should be displayed, separated by a comma with the unique student information. Let's see how to do it.

Step 1

Right-click on the project then select Add new item then select EF model as in the following:

add new item

Step 2

Provide the name of the FE model and click add and select Generate from DB.

FE Model

Step 3

Click Next.

Entity data model wizard

Step 4

Select the table.

choose your database objects

Step 5

After clicking Finish we will get an entity model like:

Class Diagram

Now right-click on the project and add a web form having 1 TextBox, 1 button and a grid view for displaying the data. Copy and paste the following code to your web form for that.

  1. <div style ="font-family:Arial ">  
Student Name
  1. <asp:TextBox ID="txtstudentName" runat="server"></asp:TextBox>  
  2.    
  3. <asp:Button ID="Search" runat="server" onclick="Button1_Click" Text="Search" />  
  4. <br />  
  5. <br />  
  6.   
  7. <asp:GridView ID="gvstudents" runat="server"  
  8. CellPadding="4" ForeColor="#333333" GridLines="None"  
  9. AutoGenerateColumns="False">  
  10. <AlternatingRowStyle BackColor="White" ForeColor="#284775" />  
  11. <Columns >  
  12. <asp:BoundField DataField="StudentId" HeaderText="Id" />  
  13. <asp:BoundField DataField="StudentName" HeaderText="Name" />  
  14. <asp:BoundField DataField="FatherName" HeaderText="Fasther Name" />  
  15. <asp:BoundField DataField="ContactNo" HeaderText="Contact No" />  
  16. <asp:BoundField DataField="Address" HeaderText="Address" />  
  17. <asp:BoundField DataField="courseName" HeaderText="Courses" />  
  18. </Columns>  
  19. <EditRowStyle BackColor="#999999" />  
  20. <FooterStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />  
  21. <HeaderStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />  
  22. <PagerStyle BackColor="#284775" ForeColor="White" HorizontalAlign="Center" />  
  23. <RowStyle BackColor="#F7F6F3" ForeColor="#333333" />  
  24. <SelectedRowStyle BackColor="#E2DED6" Font-Bold="True" ForeColor="#333333" />  
  25. <SortedAscendingCellStyle BackColor="#E9E7E2" />  
  26. <SortedAscendingHeaderStyle BackColor="#506C8C" />  
  27. <SortedDescendingCellStyle BackColor="#FFFDF8" />  
  28. <SortedDescendingHeaderStyle BackColor="#6F8DAE" />  
  29. </asp:GridView>  
  30.   
  31. <br />  
  32.   
  33. </div>  
Then add a new class for storing student information as in the following:
  1. public class StudentResult  
  2. {  
  3.     public int StudentId { getset; }  
  4.     public string StudentName { getset; }  
  5.     public string FatherName { getset; }  
  6.     public string ContactNo { getset; }  
  7.     public string Address { getset; }  
  8.     public string courseName { getset; }  
  9. }  
Then in the code behind write the following function to get the student info with a comma separated course list.
  1. private IEnumerable <StudentResult> GetStudentsWithCourseList()  
  2. {  
  3.     using (DbContext1 db = new DbContext1())  
  4.     {  
  5.         IEnumerable<StudentResult> studentsList =  
  6.         db.trnjCourse_Studnet  
  7.         .Join(  
  8.         db.Students,  
  9.         tCS => tCS.StudentId,  
  10.         s => s.StudentId,  
  11.         (tCS, s) =>  
  12.         new  
  13.         {  
  14.             tCS = tCS,  
  15.             s = s  
  16.         }  
  17.         )  
  18.         .Join(  
  19.         db.Courses,  
  20.         x => x.tCS.CourseId,  
  21.         c => c.CourseId,  
  22.         (x, c) =>  
  23.         new  
  24.         {  
  25.             x = x,  
  26.             c = c  
  27.         }  
  28.         )  
  29.         .GroupBy(  
  30.         y =>  
  31.         new  
  32.         {  
  33.             StudentId = y.x.tCS.StudentId,  
  34.             StudentName = y.x.s.StudentName,  
  35.             FatherName = y.x.s.FatherName,  
  36.             Address = y.x.s.Address,  
  37.             MobileNo = y.x.s.MobileNo  
  38.         },  
  39.         y => y.c.courseName  
  40.         )  
  41.         .Select(  
  42.         g =>  
  43.         new  
  44.         {  
  45.             StudentId = g.Key.StudentId,  
  46.             StudentName = g.Key.StudentName,  
  47.             FatherName = g.Key.FatherName,  
  48.             Address = g.Key.Address,  
  49.             ContactNo = g.Key.MobileNo,  
  50.             courseName = g.Select(e => e).Distinct()  
  51.         }  
  52.         ).ToList().Select(l =>  
  53.         new StudentResult()  
  54.         {  
  55.             StudentId = l.StudentId,  
  56.             StudentName =l.StudentName,  
  57.             FatherName = l.FatherName,  
  58.             Address = l.Address,  
  59.             ContactNo = l.ContactNo,  
  60.             courseName = string.Join(", ", l.courseName.ToArray())  
  61.         });  
  62.         return studentsList;  
  63.     }  
  64. }  
Write another function to bind this result to the grid view when the form loads.
  1. private void GetCStudentWithCourse()  
  2. {  
  3.     gvstudents.DataSource = GetStudentsWithCourseList().ToList();  
  4.     gvstudents.DataBind();  
  5.     gvstudents.EmptyDataText = "No Data Found";  
  6. }  
Call this function on form load and we will get the student list.

student list
Now create a function to search for a student depending on name and call that function on the button click of the search button.
  1. private void SearchStudentByName()  
  2. {  
  3.     IEnumerable<StudentResult> result = GetStudentsWithCourseList().Where(x => x.StudentName.ToUpper().StartsWith(txtstudentName.Text.Tri    m().ToUpper())).ToList();  
  4.     gvstudents.DataSource = result;  
  5.     gvstudents.DataBind();  
  6.     gvstudents.EmptyDataText = "No Data Found";  
  7. }  
That's all for entering the name of the student in a TextBox and click Search.

Summary

This article showed how to separate a record with commas from a SQL Server table having a 1-to-many relationship using Entity Framework (EF).

Thanks.

I would like to have feedback from my readers. Please post your feedback, question, or comments about this article.