Mysql to Linq query

Question

MySQL Query

select MAX(os.aggregate_date) as lastMonthDay,os.totalYTD
from (SELECT aggregate_date,Sum(YTD) AS totalYTD 
FROM tbl_aggregated_tables
WHERE subscription_type = 'Subcription Income'
GROUP BY aggregate_date) as os
GROUP by MONTH(os.aggregate_date),YEAR(os.aggregate_date)
ORDER BY lastMonthDay;

转换为此 LINQ 查询

var income = context.tbl_aggregated_tables
.Where(s => s.subscription_type == "Subcription Income")
.GroupBy(s => s.aggregate_date)
.Select(result => new
{
date = result.Key,
ytdsum = result.Select(x => x.YTD).Sum()
})
.GroupBy(s => new { month = s.date.Month, year = s.date.Year })
.Select(
// select max data and take its ytdsum value 
).ToList();

第二分组的目的是找到一年中每个月的最大日期。 现在，如何在第二次分组后选择每个月的最大日期及其ytdsum？

更新

income = context.tbl_aggregated_tables
.Where(s => s.subscription_type == "Subcription Income")
.GroupBy(s => s.aggregate_date)
.Select(result => new
{
date = result.Key,
ytdsum = result.Select(x => x.YTD).Sum()
})
.GroupBy(s => new { s.date.Month, s.date.Year })
.Select(
x => x.Max(s => s.date)
).ToList()

这样它只返回日期，我无法返回列表的完整对象，包括 ytdSum。

Answer 1

这应该有效：

var income = context.tbl_aggregated_tables
.Where(s => s.subscription_type == "Subcription Income")
.GroupBy(s => s.aggregate_date)
.Select(result => new
{
date = result.Key,
ytdsum = result.Select(x => x.YTD).Sum()
})
.GroupBy(s => new { month = s.date.Month, year = s.date.Year })
.Select(
x => x.OrderByDescending(k => k.date).First() 
).ToList();

现在income是一个对象列表，每个对象都有date和ytdsum

Answer 2

这很好用，但它比原始的MySQL查询花费更多的时间。

var income = context.tbl_aggregated_tables
.Where(s => s.subscription_type == "Subcription Income"
)
.GroupBy(s => s.aggregate_date)
.Select(result => new
{
date = result.Key,
ytdsum = result.Select(x => x.YTD).Sum()
})
.GroupBy(s => new { s.date.Month, s.date.Year })
.Select(
x => x.OrderByDescending(s => s.date)
)
.ToList()
.Select(el => el.FirstOrDefault())
.OrderBy(s=>s.date);