如何从嵌套选择查询 linq 中读取数据

Question

我有一个类似下面的查询，类型为linq。

var querymiangin = (from t1 in _context.Apiapplicant
join t2 in _context.ApiApplicantHistory on t1.Id equals t2.ApiApplicantId
join t3 in _context.EntityType on t2.LastReqStatus equals t3.Id
where t1.IsDeleted == false && t1.LastRequestStatus == t2.Id && t3.Name == "granted"
select new { A = t1, B = t2, Year = t1.ApiRequestDate.Substring(0, 4), Month = t1.ApiRequestDate.Substring(5, 2) } into joined
group joined by new { joined.Year, joined.Month, joined.B.LastReqStatus } into grouped
select grouped.Select(g => new { ApiReqDate = g.A.ApiRequestDate, ApiDate = g.B.Date, ApiLastReqStatus = g.B.LastReqStatus, ApiYear = g.Year, ApiMonth = g.Month })).ToList();

在选择部分中，ApiReqDate 和 ApiDate 有多个记录。现在我的问题是对于每组月份和年份，我有多个 ApiDate 和 ApiReqDate 记录，我希望根据条件 (t1.上一个请求状态 == t2.Id && t3.名称 == "granted"( 通过使用 GetPersianDaysDiffDate(( 方法，获取每个月的 ApiReqDate 与其相关 ApiDate 记录之间的差异，然后找到它们在该月的平均值。

为此，我编写了这样的代码：

var avgDateDiff = querymiangin.DefaultIfEmpty()
.GroupBy(x => new { x.ApiYear, x.ApiMonth }, (key, g) => new
{
key.ApiYear,
key.ApiYear,
Avg = g.Average(y => GetPersianDaysDiffDate(y.ApiReqDate,y.ApiDate))
})
.ToList();

但问题是每个参数x.ApiYear，x.ApiMonth，y.ApiReqDate，y.ApiDate都是未知的，它向我显示错误。如果有人能为我建议解决方案，我将不胜感激。

Answer 1

1 - 对于第一个请求querymiangin，你不需要group by语句，将代码更改为：

var querymiangin = (from t1 in Apiapplicant
join t2 in ApiApplicantHistory on t1.Id equals t2.ApiApplicantId
join t3 in EntityType on t2.LastReqStatus equals t3.Id
where t1.IsDeleted == false && t1.LastRequestStatus == t2.Id && t3.Name == "granted"
select new
{
ApiReqDate = t1.ApiRequestDate,
ApiDate = t2.Date,
ApiYear = t1.ApiRequestDate.Substring(0, 4),
ApiMonth = t1.ApiRequestDate.Substring(5, 2)
}).ToList();

2 - 对于第二个查询avgDateDiff，使用GroupBybyApiYear和ApiMonth并计算Average，如下所示：

var avgDateDiff = querymiangin
.GroupBy(x => new { x.ApiYear, x.ApiMonth }, (key, g) => new
{
key.ApiYear,
key.ApiMonth,
Avg = g.Average(y => GetPersianDaysDiffDate(y.ApiReqDate, y.ApiDate))
}).ToList();

我希望对您有所帮助。