EF Core 6为何会对Count(*)执行空值校验？

我写了一段简单的EF Core查询，用来筛选出恰好有一条有效成绩的学生：

var students = from s in dbContext.Students
               select new 
                      {
                          Name = s.Name,
                          HasGrades = s.Grades.Where(x => x.IsActive).Count() == 1
                      };

其中HasGrades为布尔类型，仅当学生存在恰好一条有效成绩时为true。

但EF Core生成的SQL存在冗余：它生成了两个完全重复的子查询，还额外对COUNT(*)的结果做了空值检查——而PostgreSQL的COUNT(*)永远不会返回null：

SELECT 
    s."NAME" AS "Name", 
    ((SELECT COUNT(*)::INT
      FROM "GRADES" AS p0
      WHERE (s."NAME" = p0."NAME") AND p0."IS_ACTIVE") = 1) 
        AND (((SELECT COUNT(*)::INT
               FROM "GRADES" AS p0
               WHERE (s."NAME" = p0."NAME") AND p0."IS_ACTIVE") IS NOT NULL)) AS "HasGrades"
FROM 
    "STUDENTS" AS s

优化方案 #

将HasGrades的赋值语句改为三元表达式：

HasGrades = s.Grades.Where(x => x.IsActive).Count() == 1 ? true : false

此时EF Core生成的SQL就变得高效合理，只用了一个子查询，也没有多余的空值检查：

CASE
    WHEN (
        SELECT COUNT(*)::INT
        FROM "GRADES" AS p1
        WHERE (p."NAME" = p1."NAME") AND p1."IS_ACTIVE") = 1 THEN TRUE
    ELSE FALSE
END

内容的提问来源于stack exchange，提问作者user3529134