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.

@rickard - so if there is a value other than 0 (id 1) and then another value that is 0 (id 2) you want just the non zero one ? Could it happen that id 1 = 35, id 2 = 50 ? If so, do you want both ? — Joanna Derks, May 02 '12 at 09:29
@Joanna if value is equal for the same date (in this case 0) i want also the value 0 to be added to the list(). — rickard, May 02 '12 at 09:31
@Joanna no, that cant happen. But it can happend that both values are 0 — rickard, May 02 '12 at 09:32

Joanna Derks · Accepted Answer · 2012-05-02T12:28:06.810

5

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

edited May 02 '12 at 12:28

answered May 02 '12 at 09:47

Joanna Derks

4,033
3
26
32

Do i want to return disticntValues after i have done these steps? When i try to i get this error "Cannot implicitly convert type 'System.Collections.Generic.IEnumerable' to 'System.Collections.Generic.List'. An explicit conversion exists (are you missing a cast?)" – rickard May 02 '12 at 10:00
And if i put .ToList() after .Select(g => g.First()).ToList(); i get "Cannot implicitly convert type 'System.Collections.Generic.List' to 'System.Collections.Generic.List'" – rickard May 02 '12 at 10:10
@rickard - Yes, the `distinctValues` should be what you are looking for. If you want just one property from the anonymous type then change the last line to `.Select(g => g.First().Hour)` (or any other property you need). The ToList() call should work just fine then and give you a list of `int`s - see the update – Joanna Derks May 02 '12 at 11:19
Fabulous!! It's working, i just had to remove .OrderbyDescdending( Max) since i dont want to order it by highest value! Thank you – rickard May 02 '12 at 11:35
@rickard - I believe there's a misunderstanding - the `OrderByDescending` won't order the values in your resulting list of integers. It's necessary to get the highest value within the group - i.e. if you had a group with `{0, 35}` and the 0 was the first value, without the `OrderByDescending` you would get the zero instead of 35 in the resulting list. So I would recomment putting the ordering back in. – Joanna Derks May 02 '12 at 12:10
@rickard - You're right. See update 2 - I think this should do it. – Joanna Derks May 02 '12 at 12:28
the problem with using OrderByDescending is that the result is ordered by highest value, instead of date. Maybe i am missing something. And if i remove the `OrderByDescending` the problem is, as you are saying the result returns `{0, 35}` the 0 value – rickard May 02 '12 at 12:29
@rickard - you mean the update2 version or the previous one ? – Joanna Derks May 02 '12 at 12:30
sorry u answeared before i made my correction. I just tried the update2 version. AND.... wait for it.... It works perfectly!!! Thanks – rickard May 02 '12 at 12:32

Crab Bucket · Answer 2 · 2012-05-03T07:51:01.813

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

edited May 03 '12 at 07:51

answered May 02 '12 at 09:40

Crab Bucket

6,219
8
38
73

Yes, it is a result like that i am after. But i dont understand everything in this code and it gives me errors so i'm not able to test it correctly. – rickard May 02 '12 at 09:51
"A query body must end with a select clause or a group clause" and "Cannot implicitly convert type 'System.Collections.Generic.List' to 'System.Collections.Generic.List'" and "The type of the expression in the select clause is incorrect. Type inference failed in the call to 'Select'." – rickard May 02 '12 at 09:58
OK - getting the group by to work with joins is the wrinkle. This post gives pointers http://stackoverflow.com/questions/525194/linq-inner-join-vs-left-join. If i get time I will rework – Crab Bucket May 02 '12 at 10:06
Working with .GroupBy(n => ) does not give me anything on n.Date – rickard May 02 '12 at 10:06
With a few correction this code now only gives me one errorcode "Cannot implicitly convert type 'System.Collections.Generic.List' to 'System.Collections.Generic.List'" – rickard May 02 '12 at 11:14
Change the last clause to select new { Hour = grouped.Sum(x => x.Hour) }).ToList(); – Crab Bucket May 02 '12 at 11:24

score 0 · Answer 3 · answered May 02 '12 at 09:20

0

 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).Distinct().OrderByDescending( n => n ).ToList();

answered May 02 '12 at 09:20

Romil Kumar Jain

20,239
9
63
92

I dont want to pick out distinct values, only distinct date. So if multiple dates, say 1 of february and 2 of february has same value. This method only give me one of these days. – rickard May 02 '12 at 09:25

select values tolist() when orderby distinct date

3 Answers3