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, and 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.

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

Step 3. Click Next.

Step 4. Select the table.

Step 5. After clicking Finish we will get an entity model like.

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.

<div style="font-family: Arial;">

Student Name

<asp:TextBox ID="txtstudentName" runat="server"></asp:TextBox>
<asp:Button ID="Search" runat="server" onclick="Button1_Click" Text="Search" />
<br />
<br />
<asp:GridView ID="gvstudents" runat="server" CellPadding="4" ForeColor="#333333" GridLines="None" AutoGenerateColumns="False">
    <AlternatingRowStyle BackColor="White" ForeColor="#284775" />
    <Columns>
        <asp:BoundField DataField="StudentId" HeaderText="Id" />
        <asp:BoundField DataField="StudentName" HeaderText="Name" />
        <asp:BoundField DataField="FatherName" HeaderText="Father Name" />
        <asp:BoundField DataField="ContactNo" HeaderText="Contact No" />
        <asp:BoundField DataField="Address" HeaderText="Address" />
        <asp:BoundField DataField="courseName" HeaderText="Courses" />
    </Columns>
    <EditRowStyle BackColor="#999999" />
    <FooterStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
    <HeaderStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
    <PagerStyle BackColor="#284775" ForeColor="White" HorizontalAlign="Center" />
    <RowStyle BackColor="#F7F6F3" ForeColor="#333333" />
    <SelectedRowStyle BackColor="#E2DED6" Font-Bold="True" ForeColor="#333333" />
    <SortedAscendingCellStyle BackColor="#E9E7E2" />
    <SortedAscendingHeaderStyle BackColor="#506C8C" />
    <SortedDescendingCellStyle BackColor="#FFFDF8" />
    <SortedDescendingHeaderStyle BackColor="#6F8DAE" />
</asp:GridView>
<br />

Then add a new class for storing student information as in the following.

public class StudentResult
{
    public int StudentId { get; set; }
    public string StudentName { get; set; }
    public string FatherName { get; set; }
    public string ContactNo { get; set; }
    public string Address { get; set; }
    public string CourseName { get; set; }
}

Then in the code behind write the following function to get the student info with a comma-separated course list.

private IEnumerable<StudentResult> GetStudentsWithCourseList()
{
    using (DbContext1 db = new DbContext1())
    {
        IEnumerable<StudentResult> studentsList =
            db.trnjCourse_Studnet
            .Join(
                db.Students,
                tCS => tCS.StudentId,
                s => s.StudentId,
                (tCS, s) =>
                    new
                    {
                        tCS = tCS,
                        s = s
                    }
            )
            .Join(
                db.Courses,
                x => x.tCS.CourseId,
                c => c.CourseId,
                (x, c) =>
                    new
                    {
                        x = x,
                        c = c
                    }
            )
            .GroupBy(
                y =>
                    new
                    {
                        StudentId = y.x.tCS.StudentId,
                        StudentName = y.x.s.StudentName,
                        FatherName = y.x.s.FatherName,
                        Address = y.x.s.Address,
                        MobileNo = y.x.s.MobileNo
                    },
                y => y.c.courseName
            )
            .Select(
                g =>
                    new
                    {
                        StudentId = g.Key.StudentId,
                        StudentName = g.Key.StudentName,
                        FatherName = g.Key.FatherName,
                        Address = g.Key.Address,
                        ContactNo = g.Key.MobileNo,
                        courseName = g.Select(e => e).Distinct()
                    }
            )
            .ToList()
            .Select(l =>
                new StudentResult()
                {
                    StudentId = l.StudentId,
                    StudentName = l.StudentName,
                    FatherName = l.FatherName,
                    Address = l.Address,
                    ContactNo = l.ContactNo,
                    courseName = string.Join(", ", l.courseName.ToArray())
                }
            );
        
        return studentsList;
    }
}

Write another function to bind this result to the grid view when the form loads.

private void GetCStudentWithCourse()
{
    gvstudents.DataSource = GetStudentsWithCourseList().ToList();
    gvstudents.DataBind();
    gvstudents.EmptyDataText = "No Data Found";
}

Call this function on form load and we will get the 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.

private void SearchStudentByName()
{
    IEnumerable<StudentResult> result = GetStudentsWithCourseList()
        .Where(x => x.StudentName.ToUpper().StartsWith(txtstudentName.Text.Trim().ToUpper()))
        .ToList();

    gvstudents.DataSource = result;
    gvstudents.DataBind();
    gvstudents.EmptyDataText = "No Data Found";
}

That's all for entering the name of the student in a TextBox and clicking 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, questions, or comments about this article.