public IEnumerable <RepositoryJobPostResult> GetJobPosts(RepositoryJobPostSearchInputModel searchInputModel) { using (var jobPortalDbContext = new JobPortalDbContext()) { string selectStatement = "SELECT jp.Id AS Id, jp.IsBookmarked, jp.JobPostThumbnailUrl, jp.Views, jp.CreatedAt, et.Name AS EmploymentType, jc.Name AS JobCategory, jt.Name AS Title, l.Address AS Location"; object[] sqlParams = null; string query = GetBaseJobPostQuery(selectStatement, searchInputModel, shouldSort: true, sqlParams: out sqlParams); var result = jobPortalDbContext.Database.SqlQuery <RepositoryJobPostResult>(query, sqlParams).ToList(); return(result); } }
public int GetJobPostCount(RepositoryJobPostSearchInputModel searchInputModel) { using (var jobPortalDbContext = new JobPortalDbContext()) { string selectStatement = "SELECT COUNT(*)"; object[] sqlParams = null; string query = GetBaseJobPostQuery(selectStatement, searchInputModel, shouldSort: false, sqlParams: out sqlParams); var postCount = jobPortalDbContext.Database.SqlQuery <int>(query, sqlParams).First(); return(postCount); } }
private string GetBaseJobPostQuery(string selectStatement, RepositoryJobPostSearchInputModel searchInputModel, bool shouldSort, out object[] sqlParams) { var sqlListParams = new List <object>(); StringBuilder sbQuery = new StringBuilder(string.Format(@" {0} FROM {1} jp JOIN {2} et ON jp.EmploymentTypeId = et.Id JOIN {3} jc ON jp.CategoryId = jc.Id JOIN {4} jt ON jp.TitleId = jt.Id JOIN {5} l ON jp.LocationId = l.Id WHERE 1=1 ", selectStatement, DbHelpers.JobPostsTableName, DbHelpers.EmploymentTypeTableName, DbHelpers.JobCategoryTableName, DbHelpers.JobTitleTableName, DbHelpers.LocationTableName)); var keywordSearchType = searchInputModel.KeywordSearchType; var keyword = searchInputModel.Keyword; if ((keywordSearchType != null) && (!string.IsNullOrWhiteSpace(keyword))) { keyword = FulltextHelper.PreprocessForFulltextSearch(keyword); string tablePrefixAndColumn = null; switch (keywordSearchType) { case KeywordSearchType.JobCategory: tablePrefixAndColumn = "jc.Name"; break; case KeywordSearchType.Location: tablePrefixAndColumn = "l.Address"; break; case KeywordSearchType.Title: tablePrefixAndColumn = "jt.Name"; break; } if (!string.IsNullOrEmpty(tablePrefixAndColumn)) { sbQuery.AppendFormat(@" AND (CONTAINS({0}, @keyword)) ", tablePrefixAndColumn); sqlListParams.Add(new SqlParameter("keyword", keyword)); } } this.AppendIdListCheckIfNotEmpty(sbQuery, "jc.Id", searchInputModel.CategoryIds); this.AppendIdListCheckIfNotEmpty(sbQuery, "et.Id", searchInputModel.EmploymentTypeIds); this.AppendIdListCheckIfNotEmpty(sbQuery, "l.Id", searchInputModel.LocationIds); if (shouldSort) { var sortOrder = searchInputModel.SortOrder; if (sortOrder == null) { sortOrder = JobPortal.Common.Model.SortOrder.ByCreatedDate; } string sortColumnName = ""; if (sortOrder == JobPortal.Common.Model.SortOrder.ByViews) { sortColumnName = "jp.Views"; } else { sortColumnName = "jp.CreatedAt"; } sbQuery.AppendFormat(@" ORDER BY {0} DESC OFFSET {1} ROWS FETCH NEXT {2} ROWS ONLY ", sortColumnName, searchInputModel.Skip, searchInputModel.Take); } sqlParams = sqlListParams.ToArray(); return(sbQuery.ToString()); }