时间戳

首页 > 解决方案 > 查找嵌套 linq 查询平均值的问题

linq - 查找嵌套 linq 查询平均值的问题

问题描述

我正在实施 asp.net 核心项目。我有多个名为ApiApp、ApiAppHistory和Entity的表。

对于前者，我编写了如下查询：

var avg = from t1 in _context.Apiapp
          join t2 in _context.ApiAppHistory on t1.Id equals t2.ApiAppId
          join t3 in _context.Entity on t2.LastReqStatus equals t3.Id
          where t1.IsDeleted == false && t1.LastRequestStatus == t2.Id && t3.Name == "Granted"
                group new {t1,t2}
                by new { t2.LastReqStatus, Year = t1.ApiRequestDate.Substring(0, 4), Month = t1.ApiRequestDate.Substring(5, 2) , Applicant = t1.ApplicantId } into g

                    select new
                             {

                                 Year = g.Key.Year, 
                                 Month = g.Key.Month,
                                 Applicant = g.Key.Applicant,
                                 GrantedCount = g.Count()
                             };

var GrantedReqAVG = avg.ToList(); 

public partial class ApiApplicantHistory
    {
        public int Id { get; set; }
        public int? SentType { get; set; }
        public int? Reason { get; set; }
        public int? LastReqStatus { get; set; }
        public int? ApiAppId { get; set; }

        public virtual Apiapp ApiApp { get; set; }
        public virtual EntityType LastReqStatusNavigation { get; set; }
        public virtual EntityType SentTypeNavigation { get; set; }
        public virtual EntityType ReasonNavigation { get; set; }
    }

 public partial class EntityType
    {
        public EntityType()
        {
ApiApplicantHistoryLastReqStatusNavigation = new HashSet<ApiApplicantHistory>();            ApiApplicantHistorySentTypeNavigation = new HashSet<ApiApplicantHistory>();
            ApiApplicantHistoryReasonNavigation = new HashSet<ApiApplicantHistory>();
        }

        public int Id { get; set; }
        public string Name { get; set; }
        public string EntityKey { get; set; }

public virtual ICollection<ApiApplicantHistory> ApiApplicantHistoryLastReqStatusNavigation { get; set; }        public virtual ICollection<ApiApplicantHistory> ApiAppHistorySentTypeNavigation { get; set; }
        public virtual ICollection<ApiApplicantHistory> ApiAppHistoryReasonNavigation { get; set; }

    }
}

public partial class Apiapp
    {
        public Apiapp()
        {
            ApiApplicantHistory = new HashSet<ApiApplicantHistory>();
        }

        public int Id { get; set; }
public bool? IsDeleted { get; set; }

        public int? LastRequestStatus { get; set; }
        public int ApplicantID { get; set; }
        public string ApiRequestDate { get; set; }
        public virtual ICollection<ApiApplicantHistory> ApiApplicantHistory { get; set; }
    }

但是关于发现平均值，我没有任何意见我该怎么做。如果有人帮助我，我将不胜感激。例如，我在ApiApp表
中有以下数据： ApiApp：

ID:1 LastRequestStatus:1 ApplicantID:1 ApiRequestDate:2019/02/03
ID:2 LastRequestStatus:2 ApplicantID:1 ApiRequestDate:2019/02/16

ID:3 LastRequestStatus:10 ApplicantID:2 ApiRequestDate:2019/02/01
ID:4 LastRequestStatus:19 ApplicantID:2 ApiRequestDate:2019/02/20
ID:5 LastRequestStatus:31 ApplicantID:2 ApiRequestDate:2019/02/29

因此平均值应该是：

(2 + 3)/2 这意味着 2 用于 ID：1 和 ID：2，其具有相同的申请人 #1，他们在 2 月请求，3 用于 ID：3 和 ID：4 和 ID：5，其具有相同的申请人 # 2 人在 2 月提出申请，因为 2 月只有两个申请人 #1 和 #2，因此我们必须将分子除以 2。

标签： linqasp.net-core

解决方案

更新后：

1 - 您不需要分组，LastRequestStatus只需和 YearMonthApplicantId

var groupedApplicant = from t1 in _context.Apiapp
                       join t2 in _context.ApiAppHistory on t1.Id equals t2.ApiAppId
                       join t3 in _context.Entity on t2.LastReqStatus equals t3.Id
                       where t1.IsDeleted == false && t1.LastRequestStatus == t2.Id && t3.Name == "Granted"
                       group new { t1, t2 }
                       by new { Year = t1.ApiRequestDate.Substring(0, 4), Month = t1.ApiRequestDate.Substring(5, 2), t1.ApplicantId } into g

                       select new
                       {
                           Year = g.Key.Year,
                           Month = g.Key.Month,
                           Applicant = g.Key.ApplicantId
                           GrantedCount = g.Count()
                       };

该请求将给出：

Year:2019,Month:2,Applicant:1,GrantedCount:2
Year:2019,Month:2,Applicant:2,GrantedCount:3

groupedApplicant2 -按Year和分组Month并选择平均值：

var avg = groupedApplicant
        .GroupBy(x => new { x.Year, x.Month })
        .Select(x => new { x.Key.Year, x.Key.Month, Avg = x.Average(y => y.GrantedCount) })
        .ToList();

该请求将给出：

Year:2019,Month:2,Avg:2.5

我希望你觉得这有帮助。

推荐阅读