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
![Comma Delimited Comma Delimited](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEizVRWoVeZLuzVpkx5PNGpYSyTZyn-FNzHBOy2V_E-NKiNh90TdKjYqcpZ1UgDjstnVna7ASlZSQXCA6VyOtVEtwyt_T6hfzCWhxJFUQ1dwUmnnoVqc8cEguFKoI2kD2YlFRgJrF3KKvV4/s16000/Capture.JPG) |
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
![Comma Delimited Comma Delimited](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEitmLYWmUX4rTYlTh79VAGBVVWJSCDNa5YVBJZvRKzuptMY-cKNlnw1W5IF2Df_HwuzWQWKs854zoG8yitMa8TEvGw_Uxoll6J4m0xu0asyMjQ9PfBUcY9QuxgzHP9r7uUhrnfxMUjWEw0/s16000/Capture.JPG) |
DEPARTMENT TABLE |
CREATE TABLE [dbo].[Department](
[DepartmentId] [int] IDENTITY(1,1) NOT NULL,
[DepartmentName] [varchar](100) NULL,
)
Employee Table
![Comma Delimited Comma Delimited](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhQVvNiP5e3F7m8cK92i5cj0Aoqi9WlMdO5LWYdicrW6hS18ZSwfg5YJH79jkc8UlirMGAteOM7cW9LPkwEkIgpHNGOxx-unyuVnKheJS5LStLNwJU3MVASjbskQcn5jZWurJS3e0i_PWw/w320-h143/Capture.JPG) |
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,
)