Sum and Group By in Linq to Entities

I'm trying to get sum of some values by grouping and I'm stuck. This is the sample structure of my table:

What I want to get is:

User name, project name, total work time of project users, grouped by user name and project name.

The trick is that I need to display a zero or a null value if a user is in a project and the user doesn't have any work time yet.

Example result should be like this:

Username  Project  Total Time
User1     Project1 15
User1     Project2 --
User2     Project1 10

How can I accomplish this?

Answers


This scary select would work, once you add references (navigational properties)

db.Users.SelectMany(
    x=> x.Projects
           .Select(i=>
                  new
                  {
                     User = x.Name,
                     Project = i.Name,
                     WorkTime = (int?)i.UserWorkTimes.Sum(t=>t.WorkTime)
                   })
    ); 

My assumptions are

  1. WorkTime is of int type.

  2. Nav.property from Projects To ProjectUserWorkTimes is called UserWorkTimes

  3. Nav.property from Users to Projects is called Projects


Need Your Help

C pthreads: Start a Thread from main

c multithreading pthreads

I am using an ARM processor with an Embedded Linux (C). My main program has to perform real-time operations and after a certain time duration (determined by the main program) I have to send data vi...

CakePHP 2.1 Custom Pagination

cakephp pagination cakephp-2.1 sql-limit

I am using cakephp 2.1 and defined 3 tables as follows.