select values tolist() when orderby distinct date

Question

I want to order the selected values by ascending distinct date.

For example i have these values in my database.

ID | Value | Date 
 1 | 35    | 2012/01/20
 2 | 0     | 2012/01/20
 3 | 10    | 2012/02/01
 4 | 0     | 2012/02/01
 5 | 0     | 2012/03/01
 6 | 0     | 2012/03/01

Since ID 1 has a value on the 20th of January and ID 3 has a value on the 1st of February i want these two dates to be selected to my list of distinct date values. But for ID 5 and 6 both have value 0. So if value is 0 i also want the value 0 to be added.

Now my linqquery looks like this

        var totalHours = (from u in context.Users
                          join r in context.Reports on u.Id equals r.UserId
                          join w in context.Weeks on r.Id equals w.ReportId
                          join d in context.Days on w.DayId equals d.Id
                          orderby d.Date ascending
                          where r.weekNr.Equals(currentWeek)
                          select d.Hour).ToList();

But this query of course gives me 35,0,10,0,0,0 as result. Though I want it to give me 35,10,0

I dont want do pick out distinct values, say if February 1st and February 2nd has the same values. I want both these values to be added.

Answer 1

I would suggest to first select what you need from the first query:

 var totalHours = (from u in context.Users
                          join r in context.Reports on u.Id equals r.UserId
                          join w in context.Weeks on r.Id equals w.ReportId
                          join d in context.Days on w.DayId equals d.Id
                          orderby d.Date ascending
                          where r.weekNr.Equals(currentWeek)
                          select new {id = r.UserId, hour = d.Hour, date = d.Date}).ToList();

In the above I assumed that d.Hour corresponds to the Value field in your example.

Then group by date, order by hour descending and select the first item from each group:

var distinctValues = totalHours
                .GroupBy(th => th.Date)
                .OrderByDescending(v => v.Max(o => o.Hour))
                .Select(g => g.First());

UPDATE

To return just the list of integers for the Hour property use this instead of the above statement:

 var distinctValues = totalHours
                .GroupBy(th => th.Date)
                .OrderByDescending(v => v.Max(o => o.Hour))
                .Select(g => g.First().Hour)
                .ToList();

UPDATE 2

Can you try this ?

var distinctValues = totalHours
                .GroupBy(th => th.Date)
                .Select(g => g.OrderByDescending(e => e.Hour))
                .Select(g => g.First().Hour)
                .ToList();

Answer 2

Do you mean that you want it grouped on date with the hours aggregated. If so would it be something like this maybe?

 var totalHours = (from u in context.Users                           
join r in context.Reports on u.Id equals r.UserId                           
join w in context.Weeks on r.Id equals w.ReportId                           
join d in context.Days on w.DayId equals d.Id                           
orderby d.Date ascending                           
where r.weekNr.Equals(currentWeek)                           
).GroupBy(n => n.Date).Select(n => new { Date = n.Key, Hour = Sum(x => x.Hour) }).ToList(); 

Just to explain

.GroupBy(n => n.Date)

Will group the results by the date so you will get a distinct row per date

.Select(n => new { Date = n.Key, Hour = Sum(x => x.Hour) })

will select and shape the result. We are shaping into a new object. n.Key is the key that the group by worked on so this will be Date. The second property is the aggregate of the hours so each distinct date will have it's hours summed. We are using the new keyword so the results are put into a new custom object. We have defined the names of the properties of the new object so there will be two - Date and Hour