[7146ebb] | 1 | using FinkiChattery.Persistence.Context;
|
---|
| 2 | using FinkiChattery.Persistence.Models;
|
---|
[b25b9ea] | 3 | using FinkiChattery.Persistence.Repositories.Contracts;
|
---|
[a3b5f34] | 4 | using Microsoft.Data.SqlClient;
|
---|
[b25b9ea] | 5 | using Microsoft.EntityFrameworkCore;
|
---|
| 6 | using System;
|
---|
[70e04f1] | 7 | using System.Collections.Generic;
|
---|
[b25b9ea] | 8 | using System.Linq;
|
---|
[70e04f1] | 9 | using System.Linq.Expressions;
|
---|
[a3b5f34] | 10 | using System.Text.RegularExpressions;
|
---|
[b25b9ea] | 11 | using System.Threading.Tasks;
|
---|
[7146ebb] | 12 |
|
---|
| 13 | namespace FinkiChattery.Persistence.Repositories
|
---|
| 14 | {
|
---|
| 15 | public class QuestionRepo : Repository<Question>, IQuestionRepo
|
---|
| 16 | {
|
---|
| 17 | public QuestionRepo(ApplicationDbContext dbContext) : base(dbContext)
|
---|
| 18 | {
|
---|
| 19 | }
|
---|
[b25b9ea] | 20 |
|
---|
[70e04f1] | 21 | public async Task<List<QuestionPreviewDto>> GetPreviewQuestionsLatest()
|
---|
| 22 | {
|
---|
| 23 | Expression<Func<Question, DateTime>> orderBy = x => x.CreatedOn;
|
---|
| 24 | return await GetPreviewQuestions(orderBy);
|
---|
| 25 | }
|
---|
| 26 |
|
---|
| 27 | public async Task<List<QuestionPreviewDto>> GetPreviewQuestionsPopular()
|
---|
| 28 | {
|
---|
| 29 | Expression<Func<Question, long>> orderBy = x => x.Views;
|
---|
| 30 | return await GetPreviewQuestions(orderBy);
|
---|
| 31 | }
|
---|
| 32 |
|
---|
| 33 | private async Task<List<QuestionPreviewDto>> GetPreviewQuestions<T>(Expression<Func<Question, T>> orderBy)
|
---|
| 34 | {
|
---|
| 35 | return await DbSet
|
---|
| 36 | .AsNoTracking()
|
---|
| 37 | .Include(x => x.QuestionCategories).ThenInclude(x => x.Category)
|
---|
| 38 | .OrderByDescending(orderBy)
|
---|
| 39 | .Select(x => new QuestionPreviewDto(x.Id,
|
---|
| 40 | x.Uid,
|
---|
| 41 | x.Title,
|
---|
| 42 | x.Views,
|
---|
| 43 | x.AnswersCount,
|
---|
| 44 | x.CreatedOn,
|
---|
| 45 | x.QuestionCategories.Select(y => new QuestionPreviewCategoryDto(y.Id, y.Uid, y.Category.Name))))
|
---|
| 46 | .Skip(0).Take(30)
|
---|
| 47 | .ToListAsync();
|
---|
| 48 | }
|
---|
| 49 |
|
---|
[b25b9ea] | 50 | public async Task<QuestionStateDto> GetQuestionState(Guid questionUid)
|
---|
| 51 | {
|
---|
| 52 | // TODO: MAYBE WRITE THIS QUERY AS SP ??
|
---|
| 53 | var questionDto = await DbSet
|
---|
| 54 | .AsNoTracking()
|
---|
| 55 | .Include(x => x.Student)
|
---|
| 56 | .Include(x => x.Team)
|
---|
| 57 | .Include(x => x.Answers).ThenInclude(y => y.Student)
|
---|
| 58 | .Include(x => x.Answers).ThenInclude(y => y.AnswerResponses).ThenInclude(y => y.Student)
|
---|
| 59 | .Include(x => x.QuestionCategories).ThenInclude(y => y.Category)
|
---|
| 60 | .Where(x => x.Uid == questionUid)
|
---|
| 61 | .Select(x => new QuestionStateDto(
|
---|
| 62 | x.Id,
|
---|
| 63 | x.Uid,
|
---|
| 64 | x.Title,
|
---|
| 65 | x.Text,
|
---|
| 66 | x.CreatedOn,
|
---|
| 67 | x.Views,
|
---|
| 68 | x.LastActiveOn,
|
---|
| 69 | new StudentQuestionStateDto(
|
---|
| 70 | x.Student.Id,
|
---|
| 71 | x.Student.Uid,
|
---|
| 72 | x.Student.IndexNumber,
|
---|
[dab7a9b] | 73 | x.Student.ImageUrl,
|
---|
| 74 | x.Student.Reputation),
|
---|
[b25b9ea] | 75 | x.Answers.Select(y =>
|
---|
| 76 | new AnswerQuestionStateDto(
|
---|
| 77 | y.Id,
|
---|
| 78 | y.Uid,
|
---|
| 79 | y.Text,
|
---|
| 80 | y.CorrectAnswer,
|
---|
| 81 | y.CreatedOn,
|
---|
[ad0fcd3] | 82 | y.VotesCount,
|
---|
[b25b9ea] | 83 | new AnswerStudentQuestionStateDto(
|
---|
| 84 | y.Student.Id,
|
---|
| 85 | y.Student.Uid,
|
---|
| 86 | y.Student.IndexNumber,
|
---|
[dab7a9b] | 87 | y.Student.ImageUrl,
|
---|
| 88 | y.Student.Reputation),
|
---|
[b25b9ea] | 89 | y.AnswerResponses.Select(z =>
|
---|
| 90 | new AnswerResponseQuestionStateDto(
|
---|
| 91 | z.Id,
|
---|
| 92 | z.Uid,
|
---|
| 93 | z.Text,
|
---|
| 94 | z.CreatedOn,
|
---|
| 95 | new AnswerResponseStudentQuestionStateDto(
|
---|
| 96 | z.Student.Id,
|
---|
| 97 | z.Student.Uid,
|
---|
| 98 | z.Student.IndexNumber,
|
---|
[dab7a9b] | 99 | z.Student.ImageUrl,
|
---|
| 100 | z.Student.Reputation))))),
|
---|
[b25b9ea] | 101 | x.QuestionCategories.Select(y =>
|
---|
| 102 | new QuestionCategoryQuestionStateDto(
|
---|
| 103 | y.Id,
|
---|
| 104 | y.Uid,
|
---|
| 105 | y.Category.Name)),
|
---|
| 106 | x.Team == null ? null : new TeamQuestionStateDto(
|
---|
| 107 | x.Team.Id,
|
---|
| 108 | x.Team.Uid,
|
---|
| 109 | x.Team.Name)))
|
---|
| 110 | .FirstOrDefaultAsync();
|
---|
| 111 |
|
---|
| 112 | return questionDto;
|
---|
| 113 | }
|
---|
[a3b5f34] | 114 |
|
---|
| 115 | public async Task<List<QuestionPreviewDto>> SearchQuestions(string searchText, IEnumerable<Guid> categories)
|
---|
| 116 | {
|
---|
| 117 | var search = Regex.Replace(searchText, "[\\\\/:*?\"<>\\]\\[|&'`~^=%,(){}_\\-]", " ")
|
---|
| 118 | .Split(" ".ToArray(), StringSplitOptions.RemoveEmptyEntries)
|
---|
| 119 | .Select(c => $"\"{c}*\"");
|
---|
| 120 |
|
---|
| 121 | var searchString = string.Join(" AND ", search);
|
---|
| 122 |
|
---|
| 123 | var rawQuery = (IQueryable<Question>)
|
---|
| 124 | DbSet.FromSqlRaw(@"
|
---|
| 125 | SELECT [q].[Id], [q].[Uid], [q].[Title], [q].[Views], [q].[AnswersCount], [q].[CreatedOn]
|
---|
| 126 | FROM [dbo].[Question] AS [q]
|
---|
| 127 | INNER JOIN CONTAINSTABLE(Question, Search, @searchString, 30) ccontains ON [q].[Id] = ccontains.[KEY]",
|
---|
| 128 | new SqlParameter("searchString", searchString))
|
---|
| 129 | .Include(x => x.QuestionCategories).ThenInclude(x => x.Category);
|
---|
| 130 |
|
---|
| 131 | if (categories.Any())
|
---|
| 132 | {
|
---|
| 133 | rawQuery = rawQuery.Where(x => x.QuestionCategories.Any(y => categories.Contains(y.Category.Uid)));
|
---|
| 134 | }
|
---|
| 135 |
|
---|
| 136 | return await rawQuery
|
---|
| 137 | .Select(x => new QuestionPreviewDto(x.Id,
|
---|
| 138 | x.Uid,
|
---|
| 139 | x.Title,
|
---|
| 140 | x.Views,
|
---|
| 141 | x.AnswersCount,
|
---|
| 142 | x.CreatedOn,
|
---|
| 143 | x.QuestionCategories.Select(y => new QuestionPreviewCategoryDto(y.Id, y.Uid, y.Category.Name))))
|
---|
| 144 | .ToListAsync();
|
---|
| 145 | }
|
---|
[7146ebb] | 146 | }
|
---|
| 147 | }
|
---|