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