public async Task <IEnumerable <StudentGroup> > Get(StudentGroupGetOptions options) { try { StringBuilder sql = new StringBuilder(); _logger.LogInformation("Try to create get student groups sql query"); sql.AppendLine(@" select distinct sg.Id, sg.StudyDirectionId, sg.Name, sg.StudentsCount, sg.StartYear, sg.CurrentCourse from StudentGroup sg "); if (options.DepartmentLoadsIds != null && options.DepartmentLoadsIds.Count > 0) { sql.AppendLine($"join GroupDisciplineLoad gdl on gdl.StudentGroupId = sg.Id and gdl.DepartmentLoadId in @DepartmentLoadsIds"); } if (options.DepartmentId.HasValue) { sql.AppendLine("join [StudyDirection] sd on sd.Id = sg.StudyDirectionId and sd.DepartmentId = @DepartmentId"); } if (options.DepartmentIds != null && options.DepartmentIds.Count > 0) { sql.AppendLine($"join [StudyDirection] sd on sd.Id = sg.StudyDirectionId and sd.DepartmentId in @DepartmentIds"); } int conditionIndex = 0; if (options.Id.HasValue) { sql.AppendLine($"{(conditionIndex++ == 0 ? "where" : "and")} sg.Id = @id"); } if (options.Ids != null && options.Ids.Count > 0) { sql.AppendLine($"{(conditionIndex++ == 0 ? "where" : "and")} sg.Id in @Ids"); } if (options.Names != null && options.Names.Count > 0) { sql.AppendLine($@"{(conditionIndex++ == 0 ? "where" : "and")} sg.Name in @Names"); } if (!string.IsNullOrEmpty(options.Search)) { sql.AppendLine($@"{(conditionIndex++ == 0 ? "where" : "and")} lower(sg.Name) like '%lower(@search)%'"); } _logger.LogInformation($"Sql query successfully created:\n{sql.ToString()}"); _logger.LogInformation("Try to execute sql get student groups query"); var result = await QueryAsync <StudentGroup>(sql.ToString(), options); _logger.LogInformation("Sql get student groups query successfully executed"); return(result); } catch (Exception exception) { _logger.LogError(exception.Message); throw exception; } }
public async Task <IEnumerable <StudentGroup> > Get(StudentGroupGetOptions options) { return(await _dao.Get(options)); }