Transcribe SELECT to LINQ query expression

Question

How can I reproduce the query then to LINQ expression?

SELECT nome_empresa, 
       Count(funcionarios.id_funcionario) AS qtdfuncionario, 
       Count(id_colaborador)              AS qtdcolaboradores 
FROM   empresas 
       JOIN funcionarios 
         ON funcionarios.id_empresa = empresas.id_empresa 
       LEFT JOIN colaborador 
              ON colaborador.id_funcionario = funcionarios.id_funcionario 
GROUP  BY nome_empresa

This is what I have done so far, but I have not yet achieved the result:

var resultado = from e in db.empresas
                join f in db.funcionarios on e.id_empresa equals f.id_empresa into eGroup
                from f in eGroup.DefaultIfEmpty()
                join c in db.colaborador on f.id_funcionario equals c.id_funcionario into eGroup2
                from c in eGroup.DefaultIfEmpty()
                select new 
                {
                    NomeEmpresa = e.nome_empresa,
                    CountFuncionario = e.funcionarios.Count,
                    Colaboradores = eGroup2.Count(t => t.id_colaborador != null)
                };

Answer 1

The reason I asked if you were using Entity Framework is precisely because of the approach. LINQ does not support Include, which is used to load the information in advance (Eager Load) and mount the joins in the best possible way. Facilitates readability and avoids confusion that may be arising in this LINQ expression you put in the question.

An alternative is to use expression methods (Expression Methods) instead of the LINQ itself. It would look like this:

var resultado = db.Empresas
                .Include(e => e.Funcionarios)
                .Select(e => new {
                    NomeEmpresa = e.NomeEmpresa, 
                    CountFuncionario = e.Funcionarios.Count(),
                    Colaboradores = e.Funcionarios.SelectMany(f => f.Colaborador).Count()
                }).ToList();