public IEnumerable <SchoolClassification> Get(Guid userId, Guid groupId, IEnumerable <Guid> ltSchoolSuperior) { var groupRep = new GroupRepository(); var groupUser = groupRep.GetById(groupId); using (var context = new SqlConnection(stringConnection)) { SchoolRepository school = new SchoolRepository(); IEnumerable <Guid> ltAUPermission = null; if (groupUser.VisionId > 1) { ltAUPermission = school.GetAUByPermission(userId, groupId, ltSchoolSuperior); } StringBuilder sb = new StringBuilder(); sb.Append(@"SELECT DISTINCT tce.tce_id 'Id', tce.tce_nome 'Name' FROM ESC_Escola esc WITH(NOLOCK) INNER JOIN ESC_EscolaClassificacao ecl WITH(NOLOCK) ON ecl.esc_id = esc.esc_id INNER JOIN ESC_TipoClassificacaoEscola tce WITH(NOLOCK) ON tce.tce_id = ecl.tce_id AND tce.tce_situacao <> 3 INNER JOIN Synonym_SYS_UnidadeAdministrativa uad WITH(NOLOCK) ON uad.ent_id = esc.ent_id AND uad.uad_id = esc.uad_id AND uad.uad_situacao <> 3 INNER JOIN Synonym_SYS_UnidadeAdministrativa uadSuperior WITH(NOLOCK) ON uadSuperior.ent_id = uad.ent_id AND uadSuperior.uad_id = ISNULL(esc.uad_idSuperiorGestao, uad.uad_idSuperior) AND uadSuperior.uad_situacao <> 3 WHERE esc.esc_situacao <> 3" ); if (groupUser.VisionId > 1) { sb.Append(" AND uad.uad_id IN @idsUADPermissao "); } //(SELECT uad_id FROM Synonym_FN_Select_UAs_By_PermissaoUsuario(@usu_idLogado, @gru_idLogado))"); if (ltSchoolSuperior != null && ltSchoolSuperior.Any()) { sb.Append(" AND uadSuperior.uad_id in @idsDRE"); } sb.Append(" ORDER BY tce.tce_nome"); var query = context.Query <SchoolClassification>( sb.ToString(), new { usu_idLogado = userId , gru_idLogado = groupId , idsUADPermissao = ltAUPermission , idsDRE = ltSchoolSuperior }); return(query); } }