sql server - Entity Framework not calculating count() after grouping -

the code better explain i'm trying do.

var data = db.absences//.asexpandable()                 .where(a => a.employee.employeecode == "emp1" || a.employee.employeecode == "emp2" || a.employee.employeecode == "emp3" || a.employee.employeecode == "emp4" )                 .where(a=> a.employee.department.departmentcode == "hr" ||a.employee.department.departmentcode == "fin" ||a.employee.department.departmentcode == "it" )                 .where(a=> a.absenceid > 0)                 .groupby(a => new             {                 a.employee.department.departmentname,                 a.employee.department.departmentcode,                 a.employee.ispermanent             })             .select(g => new resultmodel                 {                     department = g.key.departmentname,                     permanent = g.key.ispermanent,                     totalemployeesindepartment = g.select(e => e.employeecode).distinct().count(),                     totalabsencerecordsincategory = g.count(),                     totallatecount = g.select(x=> x.absencecode == "late").count(),                     totalsickcount = g.select(x=> x.absencecode == "sick").count(),                     totalonsitecount = g.select(x => x.absencecode == "onsite").count(),                     latepercent = g.select(x => x.absencecode == "late").count() * 100.0 / g.count(),                     sickpercent = g.select(x => x.absencecode == "sick").count() * 100.0 / g.count(),                     onsitepercent = g.select(x => x.absencecode == "onsite").count() * 100.0 / g.count(),                 }); 

i want total number of absences in department, grouped permanent vs non-permanent employees.

totalabsencerecordsincategory = g.count() calculating fine in generated sql.

when i'm trying count of absences due individual reasons query not working expected. totallatecount = g.select(x=> x.absencecode == "late").count(), not working, of them evaluated

    count(1) [a1],      count(1) [a2],      count(1) [a3],      count(1) [a4],.. on  

below tables

create table [dbo].[employees] (     [departmentcode] [nvarchar](10) not null,     [employeecode] [nvarchar](10) not null,     [employeename] [nvarchar](20) not null,     [ispermanent] [bit] not null,      constraint [pk_employees]          primary key clustered ([departmentcode] asc, [employeecode] asc)                     (pad_index = off, statistics_norecompute = off,                            ignore_dup_key = off, allow_row_locks = on,                            allow_page_locks = on) on [primary] ) on [primary]  go  alter table [dbo].[employees]      add constraint [df_employees_ispermanent] default ((0)) [ispermanent] go  alter table [dbo].[employees] check      add constraint [fk_employees_departments]      foreign key([departmentcode]) references [dbo].[departments] ([departmentcode]) go  alter table [dbo].[employees] check constraint [fk_employees_departments] go  create table [dbo].[departments] (     [departmentcode] [nvarchar](10) not null,     [departmentname] [nvarchar](20) not null,      constraint [pk_departments]          primary key clustered ([departmentcode] asc)                     (pad_index = off, statistics_norecompute = off,                            ignore_dup_key = off, allow_row_locks = on,                            allow_page_locks = on) on [primary] ) on [primary]  create table [dbo].[absences] (     [departmentcode] [nvarchar](10) not null,     [employeecode] [nvarchar](10) not null,     [absenceid] [int] not null,     [absencecode] [nvarchar](10) not null,     [absencedate] [date] not null,      constraint [pk_absences_1]          primary key clustered ([departmentcode] asc, [employeecode] asc, [absenceid] asc)                     (pad_index = off, statistics_norecompute = off,                            ignore_dup_key = off, allow_row_locks = on,                            allow_page_locks = on) on [primary] ) on [primary] go  alter table [dbo].[absences]  check      add constraint [fk_absences_employees]      foreign key([departmentcode], [employeecode]) references [dbo].[employees] ([departmentcode], [employeecode]) go  alter table [dbo].[absences] check constraint [fk_absences_employees] go 

one alternative

totallatecount = g.count(x=> x.absencecode == "late") 

but creates nested sub queries i'm trying avoid.

any idea how can work? resultmodel simple class

public class resultmodel {         public string department { get; set; }         public bool permanent { get; set; }         public int totalabsencerecordsincategory { get; set; }         public int totalemployeesindepartment { get; set; }         public int totallatecount { get; set; }         public int totalsickcount { get; set; }         public int totalonsitecount { get; set; }         public double latepercent { get; set; }         public double sickpercent { get; set; }         public double onsitepercent { get; set; } }