选择值tolist()，当orderby不同的日期。

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.

由于ID 1在1月20日有一个值，而ID 3在2月1日有一个值，我希望这两个日期被选中到我的不同日期值列表中。但是对于ID 5和6都是0。如果value是0，我也想加0。

Now my linqquery looks like this

现在我的linqquery看起来是这样的。

        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

但是这个查询给出的结果是35 0 10 0 0。虽然我想让它给我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.

我不想选择不同的价值观，比如2月1日和2月2日有同样的价值观。我要把这两个值都加进去。

3 个解决方案

#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.

在上面我假设d。时间对应于示例中的值字段。

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

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

#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

将选择并决定结果。我们正在塑造一个新事物。n。关键是团队的关键，所以这将是日期。第二个属性是时间的集合，所以每个不同的日期都有它的时间总和。我们正在使用新的关键字，所以结果被放入一个新的自定义对象。我们已经定义了新对象的属性的名称，因此将有两个日期和小时。

#3

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

#1