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 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:



Step 2

Provide the name of the FE model and 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="Fasther 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 />

</div>

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 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.Tri    m().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 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.