Get Comma Separated Values From SQL Server 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 information about a student and courses in 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. <asp:Button ID="Search" runat="server" onclick="Button1_Click" Text="Search" />  
  3. <br />  
  4. <br />  
  5. <asp:GridView ID="gvstudents" runat="server" CellPadding="4" ForeColor="#333333" GridLines="None" AutoGenerateColumns="False">  
  6.     <AlternatingRowStyle BackColor="White" ForeColor="#284775" />  
  7.     <Columns>  
  8.         <asp:BoundField DataField="StudentId" HeaderText="Id" />  
  9.         <asp:BoundField DataField="StudentName" HeaderText="Name" />  
  10.         <asp:BoundField DataField="FatherName" HeaderText="Fasther Name" />  
  11.         <asp:BoundField DataField="ContactNo" HeaderText="Contact No" />  
  12.         <asp:BoundField DataField="Address" HeaderText="Address" />  
  13.         <asp:BoundField DataField="courseName" HeaderText="Courses" />  
  14.     </Columns>  
  15.     <EditRowStyle BackColor="#999999" />  
  16.     <FooterStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />  
  17.     <HeaderStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />  
  18.     <PagerStyle BackColor="#284775" ForeColor="White" HorizontalAlign="Center" />  
  19.     <RowStyle BackColor="#F7F6F3" ForeColor="#333333" />  
  20.     <SelectedRowStyle BackColor="#E2DED6" Font-Bold="True" ForeColor="#333333" />  
  21.     <SortedAscendingCellStyle BackColor="#E9E7E2" />  
  22.     <SortedAscendingHeaderStyle BackColor="#506C8C" />  
  23.     <SortedDescendingCellStyle BackColor="#FFFDF8" />  
  24.     <SortedDescendingHeaderStyle BackColor="#6F8DAE" />  
  25. </asp:GridView>  
  26. <br />  
  27. </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 the 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.