将SQL Server关联表计数查询转换为C# Linq语句

原SQL需求是查询finca表的所有字段，同时统计关联表ps中对应finca_id的记录数，原SQL如下：

select 
    (select count(*)
        from ps p
        where p.finca_id = f.finca_id
       ) as ps_count, f.*
from finca f

写法一：Linq查询表达式 #

var result = from f in dbContext.Finca
             select new 
             {
                 PsCount = (from p in dbContext.Ps
                            where p.FincaId == f.FincaId
                            select p).Count(),
                 Finca = f
             };

写法二：Linq方法链语法 #

var result = dbContext.Finca.Select(f => new 
{
    PsCount = dbContext.Ps.Count(p => p.FincaId == f.FincaId),
    Finca = f
});

如果需要把finca的字段直接展开到匿名类中（而非嵌套Finca对象），可以改写为：

var result = from f in dbContext.Finca
             select new 
             {
                 PsCount = dbContext.Ps.Count(p => p.FincaId == f.FincaId),
                 f.FincaId,
                 f.OtherField1, // 替换为finca表的实际字段
                 f.OtherField2
                 // 依次列出finca的所有字段
             };

说明：以上两种写法都会被EF Core（或其他ORM）转换为与原SQL逻辑一致的查询，执行时会在数据库端完成统计，不会将全表数据加载到内存后再计算，保证性能。

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