select values tolist() when orderby distinct date

c#, entity-framework, linq, linq-to-sql, silverlight

Solution

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();

Problem

Original source

Related problems

• LINQ Inner-Join vs Left-Join