I am making a query that has some joins and a count, however I want to do it with Linq
.
This is my Query.
sbSQL.Append("SELECT grproj.cdgruproj as Id, grproj.dsgruproj as DescricaoGrupoProjeto, ");
sbSQL.Append("(SELECT COUNT(*) ");
sbSQL.Append(" FROM usufrm ");
sbSQL.Append(" WHERE usufrm.cdfrm = form.cdfrm ");
sbSQL.Append(" AND usufrm.sgproj = proj.sgproj ");
sbSQL.Append(" AND usufrm.tpproj = tproj.tpproj ");
sbSQL.Append(" AND usufrm.dtcan IS NULL) as QuantidadeDeUsuarios ");
sbSQL.Append("FROM grproj ");
sbSQL.Append("INNER JOIN proj ON grproj.cdgruproj = proj.cdgruproj ");
sbSQL.Append("INNER JOIN tproj ON proj.tpproj = tproj.tpproj ");
sbSQL.Append("INNER JOIN projfr ON proj.sgproj = projfr.sgproj AND proj.tpproj = projfr.tpproj ");
sbSQL.Append("INNER JOIN form ON form.cdfrm = projfr.cdfrm ");
sbSQL.Append("WHERE grproj.dtcan IS NULL AND proj.dtcan IS NULL AND form.dtcan IS NULL ");
This is the example of my query with Linq, the quantity field should be a count:
IList<GrupoProjetoEntity> grupos = (from grupoProjeto in session.Query<GrupoProjetoEntity>()
join projeto in session.Query<ProjetoEntity>() on grupoProjeto.Id equals projeto.cdgruproj.Id
join tipoProjeto in session.Query<TipoProjetoEntity>() on projeto.tpproj.TipoProjeto equals tipoProjeto.TipoProjeto
join projetoFormulario in session.Query<ProjetoFormularioEntity>() on
new { SiglaProjeto = projeto.sgproj, tpproj = projeto.tpproj.TipoProjeto }
equals new { SiglaProjeto = projetoFormulario.sgproj, tpproj = projetoFormulario.tpproj.TipoProjeto }
join formulario in session.Query<FormularioEntity>() on projetoFormulario.cdfrm.Id equals formulario.Id
select new GrupoProjetoEntity()
{
Id = grupoProjeto.Id,
DescricaoGrupoProjeto = grupoProjeto.DescricaoGrupoProjeto,
QuantidadeDeUsuarios = 0
}).ToList();