public async Task <IActionResult> HotestComments() { // 子查询可以有,但是只能查询一个scalar。outer join应当用正规的outer join var query = from p in Context.Comments select new { comment = p, agreeCount = Context.Attitudes.Where(o => o.Agree == true && o.CommentId == p.Id).Count(), } into k join u in Context.Users on k.comment.SenderId equals u.Id join w in Context.Works on k.comment.WorkId equals w.Id orderby k.comment.AgreeCount descending select new { comment = k.comment, agreeCount = k.agreeCount, work = w, user = u }; var comments = await query.Take(20).ToListAsync(); var qcomments = comments.Select( p => QComment.NormalView(p.comment, QUser.NormalView(p.user), null, QWork.NormalView(p.work))) .ToList(); return(Ok(qcomments)); }
public async Task <IActionResult> GetWorkById(Guid id) { var work = await Context.Works.AsNoTracking().Where(p => p.Id == id).FirstOrDefaultAsync(); var qwork = QWork.NormalView(work); return(Ok(qwork)); }
public async Task <IActionResult> HotestWorks(WorkType type) { var query = from p in Context.Works.Where(p => p.Type == type) select new { work = p, commentCount = Context.Comments.Where(o => o.WorkId == p.Id).Count() } into k orderby k.commentCount descending select k; var works = await query.Take(20).ToListAsync(); var qworks = works.Select(p => QWork.NormalView(p.work, p.commentCount)); return(Ok(qworks)); }
public async Task <IActionResult> GetWorkByKeyword(WorkType?type, string keyword, int page) { page = Math.Max(0, page); const int pageSize = 20; if (keyword == null) { return(new ApiError(MyErrorCode.ModelInvalid, "Keyword is null").Wrap()); } if (keyword.Length >= 20) { keyword = keyword.Substring(0, 20); } var filteredTypeQuery = Context.Works.AsNoTracking().AsQueryable(); if (type != null) { filteredTypeQuery = filteredTypeQuery.Where(p => p.Type == type); } var query = from p in filteredTypeQuery select new { work = p, qr = EF.Functions.WebSearchToTsQuery("testzhcfg", keyword) } into p select new { p.work, p.qr, rank = p.work.Tsv.RankCoverDensity(p.qr, NpgsqlTsRankingNormalization.DivideBy1PlusLogLength) } into p where p.work.Tsv.Matches(p.qr) orderby p.rank descending select p.work; var works = await query.Skip(page *pageSize).Take(pageSize).ToListAsync(); var qworks = works.Select(p => QWork.NormalView(p)).ToList(); return(Ok(qworks)); }