Linq to Sql - Populate JOIN result into a List

Question

I am not sure if this can be done, but here's the scenario.

I want to turn this sql into linq:

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

Department - Employee is 1 to many relationship.

I have created a custom object that I would like to populate the result into.

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

The Linq I came up with is

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 it out and it's not working. Can anyone shed some light for me please? I would like to perform an inner join between Department and Employee. For each Department in the resultset, I would like to create one DepartmentSummary object which holds that department and a list of employees belonging to that department.

Does Linq provides an ad hoc solution for this or must I iterates through the result set and create a list of DepartmentSummary manually?

Thanks,

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

Sounds like you're looking to get around lazy loading?

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;
}

Now, if you don't have a relationship defined between Department and Employees (the Linq2Sql designer will do this for you if you have database relationships setup) then you should look into doing that. It makes it all dramatically easier. In fact, you don't even need your campaign summary.

Answer 2

This problem is due to the nature of the query. When you join Department to Employee, you'll get back one record for every Employee. This means that your ToList() statement is expecting multiple employees per department, but due to the join, always getting one.

Change your query to

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

I've tested this and it works.

What it does differently is selects only one record per Department (not per employee) then it gets the zero to many corresponding employees for each dept and converts them to a list.

Good luck!

EDIT As requested, here is the generated 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]

The only modification is that LINQ will not do [t0].*, instead it will enumerate each field. Since I had to guess at the fields, I left them out to make the SQL clearer.

Answer 3

LINQ to SQL doesn't understand your ToList() call, but you might be able to select the sequence of joined elements and then use LINQ to Objects (via AsEnumerable()) to map to your DepartmentSummary object:

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

The thing is that you're not really taking one SQL and trying to create a Linq-query out of it.

If you were, you'd notice that your SQL query does not really produce one row per department, but it will repeat the department information for each employee in that department.

Now, an initial naive look would suggest you use a group-by clause, since that would allow you to split the data into individual groupings for each department, but groupings in SQL does not really give you a key+all-matching-rows type of result, rather it allows you to do aggregate calculations, like "for each department, how many employees do I have".

So, in order to do what you want, you need to basically do a normal join, which will give you each employee, coupled with the appropriate department information (ie. each employee will be linked to his/her department), and then you need to construct the rest of the data structure yourself.

Now, having said that, if you have the proper relationships set in your data context related classes, each department should already have some kind of property that contains all employees in that department, so perhaps the simple query is just "give me all departments", and then you can, for each department, retrieve the employees?

Of course, doing that would likely execute one SQL for each department, but in this case, you're back to "give me all employees with their department information" and you have to build code to handle the rest.