Transcribe SELECT for query expression LINQ

Question

Transcribe SELECT for query expression LINQ

Navigation

#1 by (1 votes)

4

How can I reproduce the following query for 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)
                };

c# entity-framework linq asp.net-mvc-4

asked by anonymous 29.07.2015 / 16:08

1 answer

I can not initialize Tomcat on Netbeans Structure for PHP project and use of the eval () function

score 1 · Accepted Answer

The reason I asked if you were using the Entity Framework is precisely because of the approach. LINQ does not support Include , which is used to preload the information ( Eager Load ) and mount the joins as best you can. It facilitates readability and avoids the confusion that might be arising in this LINQ expression that you put in the question.

An alternative is to use Expression Methods instead of 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();