Getting multiple Distinct grouped values in linq

Question

Haven't been able to specifically find, answers to my question..

I need to get a result that display: Project name ONCE, with all the users that have made timeEntries on that project, in a given date range.

And I need to get the timeEntries (hoursEntered) as well, so I can calculate how many hours have been spent on that project in total..

I am new to this, so I hope i have provided enough information..

my code:

var dateStart = new DateTime (2016,09,01);
var dateEnd   = new DateTime (2016,09,19);

var query = (from timeEntry in TimeEntries
        join task in Tasks on timeEntry.TaskID equals task.TaskID
        join project in Projects on task.ProjectID equals project.ProjectID
        where timeEntry.DateEntity >= dateStart && timeEntry.DateEntity <= dateEnd
        select new {
        User = timeEntry.User.FirstName + " "+timeEntry.User.LastName,
        Project = timeEntry.Task.Project.ProjectName,
        ProjectId = timeEntry.Task.ProjectID,
        HoursEntered = timeEntry.HoursEntered
        }).GroupBy (q => q.Project).Distinct();

i have 4 tables :

Projects (ProjectID, ProjectName)
Tasks (TaskID, ProjectID)
TimeEntries (HoursEntered, DateEntity, TaskID)
Users (FirstName, LastName)

Add your `TimeEntries` class to your post, please. – rbr94 Sep 20 '16 at 06:37 — rbr94, Sep 20 '16 at 06:37
my class ? TimeEntries is my table from DB. – andrelange91 Sep 20 '16 at 06:43 — andrelange91, Sep 20 '16 at 06:43
He needs the property/columns, like `hoursEntered` etc – Jeroen van Langen Sep 20 '16 at 06:44 — Jeroen van Langen, Sep 20 '16 at 06:44
added the ones i use in () at each table – andrelange91 Sep 20 '16 at 06:48 — andrelange91, Sep 20 '16 at 06:48

Amy B · Accepted Answer · 2016-09-20T07:06:33.353

2

var query =
  from timeEntry in TimeEntries
  where timeEntry.DateEntity >= dateStart && timeEntry.DateEntity <= dateEnd
  select new {
    User = timeEntry.User.FirstName + " "+timeEntry.User.LastName,
    Project = timeEntry.Task.Project.ProjectName,
    ProjectId = timeEntry.Task.ProjectID,
    HoursEntered = timeEntry.HoursEntered
  }

var localRows = query.ToList();

var groups = localRows.GroupBy(x => x.Project);

var projects = groups.Select(g => new {
  Project = g.Key,
  Hours = g.Sum(x => x.HoursEntered),
  Users = g.Select(x => x.User).Distinct().ToList()
}).ToList();

edited Sep 20 '16 at 07:06

answered Sep 20 '16 at 06:58

Amy B

108,202
21
135
185

this one is really close, but i still get multiple records of the same username.. i need it to display only on of each user that has entered hours into the project. – andrelange91 Sep 20 '16 at 07:03
Fair enough, fixed. – Amy B Sep 20 '16 at 07:06

score 1 · Answer 2 · edited May 23 '17 at 12:19

1

Maybe with:

var query = (from timeEntry in TimeEntries
        join task in Tasks on timeEntry.TaskID equals task.TaskID
        join project in Projects on task.ProjectID equals project.ProjectID
        where timeEntry.DateEntity >= dateStart && timeEntry.DateEntity <= dateEnd
        group timeEntry by new { proyect.ProjectID, proyect.ProjectName } into g
        select new {
        TimeEntries = g.ToArray(),
        Project = g.Key.ProjectName ,
        ProjectId = g.Key.ProjectID,
        HoursEntered = g.Sum(e => e.HoursEntered)
        });

I need more info to adjust it more, but I think this should help you. I did it on the browse, so mybe you need to check names.

Group by in LINQ

edited May 23 '17 at 12:19

Community

1
1

answered Sep 20 '16 at 06:54

Oscar Vicente Perez

847
7
23

Pretty good, have a +1. Also, be careful using this on a database. LinqToSql is likely to do extra queries to fetch the TimeEntries = g.ToArray part. – Amy B Sep 20 '16 at 07:02
this one gets way too much data that i don't need. and doesn't give me the usernames – andrelange91 Sep 20 '16 at 07:05
Well, as you are the only one who knows the data base and what you need, just adjust the query. +1 to the LinqToSql. But YAGNI except for a big Database. – Oscar Vicente Perez Sep 20 '16 at 07:06

Getting multiple Distinct grouped values in linq

2 Answers2