CODEWITHSUNDEEP
.netentity-framework.net-3.5
user424950
user424950

Reputation: 71

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:

alt text

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?

Upvotes: 1

Views: 2191

Answers (1)

The Smallest
The Smallest

Reputation: 5773

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

Upvotes: 1

Related Questions