Select count with join and group by

Question

I have linq query in my code which does not give the right output. I have two Mysql tables "classes" and "eleves" which have a 1-n relationship. This is the query:

from p in db.Classes
from e in db.Eleves.LeftJoin(el => el.Classe == p.IdC)
group e by p.IdC into _
select new Classe{IdC=_.Key, Num=_.Count()}

It generates this SQL statement:

SELECT
`t2`.`id_c`,
Count(*) as `c1`
FROM
`classe` `t2`
    LEFT JOIN `eleve` `t1` ON `t1`.`classe` = `t2`.`id_c`
GROUP BY
`t2`.`id_c`

which outputs this result:

At first hand, i thought it was good. But i noticed that "classes" with no "eleves", also output "1" in the "cl" column. When i edit the SQL statement like this:

SELECT
`t2`.`id_c`,
Count(matricule) as `c1`
FROM
`classe` `t2`
    LEFT JOIN `eleve` `t1` ON `t1`.`classe` = `t2`.`id_c`
GROUP BY
`t2`.`id_c`

the result is :

_{(source: mmbh.info)}

which is the expected output. After googling, i can't figure out how to format the query to get the correct output. I tried many suggestions like this

from p in db.Classes
from e in db.Eleves.LeftJoin(el => el.Classe == p.IdC)
group e by p.IdC into _
select new Classe { IdC = _.Key, elv = _.Count(u=>u.Matricule!=null) }

or

from p in db.Classes
from e in db.Eleves.LeftJoin(el => el.Classe == p.IdC)
group e by p.IdC into _
select new Classe { IdC = _.Key, elv = _.Select(x => x.Matricule).Distinct().Count() }

But i get the error "LinqToDB.LinqToDBException : ''u.Matricule' cannot be converted to SQL.' "

I eventually found a query which gives the right output:

from p in db.Classes
join e in db.Eleves on p.IdC equals e.Classe into k
select new Classe { IdC = p.IdC, elv = k.Count()}

which generate this SQL statement:

SELECT
`p`.`id_c`,
(
    SELECT
        Count(*)
    FROM
        `eleve` `t1`
    WHERE
        `p`.`id_c` = `t1`.`classe`
) as `c1`
FROM
`classe` `p`

But i don't know if this is an optimized way to handle this. Can you please give me advice ?

Answer 1

Use the following LINQ query:

from p in db.Classes
from e in db.Eleves.LeftJoin(el => el.Classe == p.IdC)
group e by p.IdC into _
select new Classe 
{ 
    IdC = _.Key, 
    elv = _.CountExt(u => u.Matricule, Sql.AggregateModifier.Distinct) 
}