SELECT COUNT (ColumnName) Group By [ColumnName] in Linq C#

Question

SELECT COUNT (ColumnName) Group By [ColumnName] in Linq C#

c# entity-framework-core linq

Question

I am trying to count records and group them by a specific column in Linq using C#. Here is my SQL Statement that I would like to convert to Linq. I want to put the grouped records to an IEnumerable<object> or List<object> so that I can pass the list to my chart.

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]

These are SQL results from the above SQL statement.

My Classes

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; }
    }

Linda

View more on Stack Overflow

Ivan Stoev · Accepted Answer · 6/24/2016 5:24:09 PM

The SQL aggregate function COUNT(expr) is mapped to LINQ Count() when the expr cannot be null (which is the case with your A.ApplicationStatusId column), Count(expr != null) or Sum(expr != null ? 1 : 0) otherwise (I prefer the second because it seems to be translated better by EF).

So the equivalent LINQ query could be something like this:

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

SELECT COUNT (ColumnName) Group By [ColumnName] in Linq C#

Question

Fastest Entity Framework Extensions

Accepted Answer

Performance

Expression Evaluator

Related