SELECT COUNT(ColumnName)Group by Linq C中的[ColumnName]#

c# entity-framework-core linq

我正在嘗試計算記錄並使用C#通過Linq中的特定列對它們進行分組。這是我要轉換為Linq的SQL語句。我想將分組記錄放入IEnumerable<object>List<object>以便我可以將列表傳遞給我的圖表。

SELECT 
G.[Description] AS Grade,
COUNT(CASE WHEN A.ApplicationStatusId = 1 THEN 1 END) AS Pending,
COUNT(CASE WHEN A.ApplicationStatusId = 2 THEN 1 END) AS Accepted,
COUNT(CASE WHEN A.ApplicationStatusId = 3 THEN 1 END) AS Rejected,
COUNT(ApplicationId) AS Total
FROM [Application] A
LEFT JOIN Grade G ON A.GradeId = G.GradeId

GROUP BY G.[Description]

這些是來自上述SQL語句的SQL結果。

在此處輸入圖像描述

我的課程

public class Application : Audit
    {
        [Key]
        [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
        public int ApplicationId { get; set; }
        [DataType(DataType.DateTime)]
        public DateTime? ApplicationDate { get; set; }
        [StringLength(150)]
        public string PreviousSchoolName { get; set; }
        [StringLength(500)]
        public string PreviousSchoolReportUrl { get; set; }
        [StringLength(500)]
        public string PreviousSchoolTransferUrl { get; set; }
        public bool? Deleted { get; set; }

        #region Foreign Keys
        [ForeignKey("Leaner")]
        public int? LeanerId { get; set; }
        [ForeignKey("ApplicationStatus")]
        public int? ApplicationStatusId { get; set; }
        [ForeignKey("Grade")]
        public int? GradeId { get; set; }
        #endregion

        #region Navigation Properties
        public virtual Leaner Leaner { get; set; }
        public virtual ApplicationStatus ApplicationStatus { get; set; }
        public virtual Grade Grade { get; set; }
        #endregion

        public static Application GlobalApplication { get; set; }
        public static IEnumerable<Grade> Grades { get; set; }

        #region Dashboard Preperties - No Database Mapping
        [NotMapped]
        public int TotalApplications { get; set; }
        [NotMapped]
        public string GradeName { get; set; }
        #endregion
    }

public class Grade : LookupBase
    {
        [Key]
        [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
        public int GradeId { get; set; }


    }

public abstract class LookupBase
    {
        [StringLength(50)]
        public string Name { get; set; }
        [StringLength(250)]
        public string Description { get; set; }
    }

public abstract class Audit
    {
        [DataType(DataType.DateTime)]
        public DateTime? DateCreated { get; set; }
        [DataType(DataType.DateTime)]
        public DateTime? DateModified { get; set; }
        public int? CreatedByOnlineUserId { get; set; }
        public int? ModifiedByOnlineUserId { get; set; }
    }

一般承認的答案

expr不能為null (在A.ApplicationStatusId列中就是這種情況), Count(expr != null)Sum(expr != null ? 1 : 0) Count()時,SQL聚合函數COUNT(expr)映射到LINQ Count() Sum(expr != null ? 1 : 0)否則(我更喜歡第二種,因為它似乎被EF更好地翻譯)。

所以等效的LINQ查詢可能是這樣的:

var query =
    from a in db.Application
    group a by a.Grade.Description into g
    select new
    {
        Grade = g.Key,
        Pending = g.Sum(a => a.ApplicationStatusId == 1 ? 1 : 0),
        Accepted = g.Sum(a => a.ApplicationStatusId == 2 ? 1 : 0),
        Rejected = g.Sum(a => a.ApplicationStatusId == 3 ? 1 : 0),
        Total = g.Count()
    };


Related

許可下: CC-BY-SA with attribution
不隸屬於 Stack Overflow
這個KB合法嗎? 是的,了解原因
許可下: CC-BY-SA with attribution
不隸屬於 Stack Overflow
這個KB合法嗎? 是的,了解原因