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

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.