How to Query and count an Employees table and Project table in one linq query in silverlight?

Question

I have two tables an employee table and a project table and I am looking to bring back a count of the number of employees assigned to each project. In the employee table I have employeeIDs and projectIDs, and in the projects table I have projectID, name, department, notes, etc. I am looking to bring back the following information and display it in one single DataGrid in silverlight with the number of employees assigned to each project as a Count.

Name Department Notes Count

Answer 1

There are many ways to do this, but the simplest is probably to do it like this:

var x =
    from proj in db.Projects
    select new
    {
     Name = proj.Name,
     Department = proj.Department,
     Notes = proj.Notes,
     Count = db.Employees.Where(emp => emp.ProjectID == proj.ProjectID).Count()
    };

Alternatively, if you created a foreign key between Projects and Employees on ProjectID (if not, you should strongly consider doing so), LINQ will kindly provide even cleaner syntax:

var x =
    from proj in db.Projects
    select new
    {
     Name = proj.Name,
     Department = proj.Department,
     Notes = proj.Notes,
     Count = proj.Employees.Count()
    };

It doesn't get any better than that!

Answer 2

I think you want something like this....

dbContext dbCon = new dbContext();
            var projects = from project in dbCon.Projects
                           join employee in dbCon.Employees on project.ProjectID equals employee.projectid
                           group employee by new { employee.projectid, project.Name, project.Department, project.Notes } into grouping
                           select new { 
                               //projectID = grouping.Key.projectid, 
                               projectName = grouping.Key.Name,
                               projectDepartment = grouping.Key.Department, 
                               projectNotes = grouping.Key.Notes, 
                               count = grouping.Count() 
                           };