Row wise Comma Delimited (Saperated) string using LINQ Query

In this post, I will explain how to get Row wise Comma Delimited (Separated) string using LINQ Query.

In below example, I have two table Department and Employee. Each department having multiple Employees. In result, I want the List of Department with no of employees and a comma-separated list of employees working in that department.

Result Table 

LINQ QUERY RESULT TABLE

In the below query, I have used string.Join extension method which returns a comma-separated value for a list of employees.

Syntax - string.Join(",", {array})  

Query to get Row wise Comma Delimited (Saperated) string in LINQ Query :

 var employees = db.Departments.GroupJoin(db.Employees,

                d => d.DepartmentId,

                em => em.DepartmentId,

                (d, em) => new { dept = d, emp = em })

                .Select(d =>

                    new

                    {

                        Department = d.dept.DepartmentName,

                        Count = d.emp.Count(),

                        EmployeeList = string.Join(", ", d.emp

                                          .Select(p => p.EmployeeName).Distinct())

                    });

            GridView1.DataSource = employees;

            GridView1.DataBind();

Department Table 

DEPARTMENT TABLE

CREATE TABLE [dbo].[Department](

[DepartmentId] [int] IDENTITY(1,1) NOT NULL,

[DepartmentName] [varchar](100) NULL,

)

Employee Table 

EMPLOYEE TABLE

CREATE TABLE [dbo].[Employee](

[EmployeeId] [int] IDENTITY(1,1) NOT NULL,

[EmployeeName] [varchar](100) NULL,

[DepartmentId] [int] NULL,

[Salary] [decimal](18, 2) NULL,

[Age] [int] NULL,

[ZoneId] [int] NULL,

 )