如何将给定多表关联SQL查询转换为LINQ语句，解决GROUP BY实现问题

var result = from fi in model.FormInstance                                                                      
             join ins in model.Inspections on fi.Id equals ins.FormInstanceId 
             join a in model.Assets on ins.AssetId equals a.Id
             join br in model.Branches on a.BranchId equals br.Id
             join usr in model.Users on br.Id equals usr.HomeBranchId
             // 若需要实现SQL的like模糊匹配，可替换为 where EF.Functions.Like(br.Id, "%xxx%")
             where br.Id == gBranchId 
             // 同时按两个字段分组，和SQL的GROUP BY逻辑完全对齐
             group fi by new { fi.CreatedOn, fi.Id } into grouped
             orderby grouped.Key.CreatedOn descending
             select new 
             {
                 grouped.Key.CreatedOn,
                 grouped.Key.Id
             };

关键说明 #

• 你原有写法仅按fi.Id单个字段分组，不符合SQL中同时按CreatedOn、Id两个字段分组的逻辑，使用匿名类包裹两个分组字段即可解决问题
• 如果你的业务场景只是对查询结果的CreatedOn和Id去重，也可以省略group by逻辑，直接调用Distinct()实现相同效果，写法更简洁：

var result = (from fi in model.FormInstance                                                                      
              join ins in model.Inspections on fi.Id equals ins.FormInstanceId 
              join a in model.Assets on ins.AssetId equals a.Id
              join br in model.Branches on a.BranchId equals br.Id
              join usr in model.Users on br.Id equals usr.HomeBranchId
              where br.Id == gBranchId 
              orderby fi.CreatedOn descending
              select new 
              {
                  fi.CreatedOn,
                  fi.Id
              }).Distinct();

内容的提问来源于stack exchange，提问作者Luis Antonio Jacho Zambrano