Linq to Sql - fill a JOIN result in a list

Question

I'm not sure if this can be done, but here is the script.

I want to turn this sql to linq:

SELECT * FROM Department d
INNER JOIN Employee e ON e.DepartmentID = d.DepartmentID

Department - Employee from 1 to many relationships.

I created a custom object that I would like to populate.

public class DepartmentSummary
{
    public Department Department { get; set; }
    public List<Employee> Employees {get; set;}
}

In Linq, I came up with

var result = from d in dba.Department
             join e in dba.Employee d.DepartmentID equals e.DepartmentID into j1
             select new DepartmentSummary
             {
                  Department = d,
                  Employees = j1.ToList()
             };

I tried and it does not work. Can someone shed some light on me please? I would like to make an internal connection between the Department and the Employee. For each department in the result set, I would like to create one DepartmentSummary object that contains this department and a list of employees belonging to this department.

Does Linq provide a special solution for this, or should I iterate through the result set and manually create a list from the Management section?

Thanks,

EDIT: It seems like this works for me

var result = from d in dba.Department
             join e in dba.Employee d.DepartmentID equals e.DepartmentID into j1
             where j1.Count() > 0
             select new DepartmentSummary
             {
                  Department = d,
                  Employees = j1.ToList()
             };

Answer 1

, ?

DataLoadOptions dlo = new DataLoadOptions();
dlo.LoadWith<Department>(d => d.Employees);
using (var dba = new MyDataContext())
{
  dba.LoadOptions = dlo;
  var result = from d in dba.Department
      select d;
}

, ( Linq2Sql , ), . . , .

Answer 2

, SQL Linq-.

, , SQL- , .

, group-by, , SQL + - - , , , " , ".

, , , , (.. ), .

, , , , - , , , , - " ", , , ?

, , , SQL , " ", , .

Answer 3

LINQ to SQL ToList(), , LINQ to Objects ( AsEnumerable()) DepartmentSummary:

var qResult = from d in dba.Department
              join e in dba.Employee d.DepartmentID equals e.DepartmentID into j1
              select new
              {
                  Department = d,
                  Employees = j1
              };

var result = from d in qResult.AsEnumerable()
             select new DepartmentSummary()
             {
                 Department = d.Department,
                 Employees = e.Employees.ToList()
             };

Answer 4

. , . , ToList() , - .

var result = 
    from d in dba.Department
    select new tCampaignSummary
    {
        Department = d,
        Employees = dba.Employee.Where(e => e.DepartmentID == 
                        d.DepartmentID).ToList()
    };

, .

, -, ( ), .

!

SQL:

SELECT [t0].*, [t1].*
    (
    SELECT COUNT(*)
    FROM [dbo].[Employee] AS [t2]
    WHERE [t2].[DepartmentID] = [t0].[DepartmentID]
    ) AS [value]
FROM [dbo].[Department] AS [t0]
LEFT OUTER JOIN [dbo].[Employee] AS [t1] 
    ON [t1].[DepartmentID] = [t0].[DepartmentID]
ORDER BY [t0].[DepartmentID], [t1].[IndexID]

, LINQ [t0]. *, . , , SQL .