Changeset 7899209 for src/FinkiChattery/FinkiChattery.Persistence/Repositories/Implementations/QuestionRepo.cs

Timestamp:

10/27/21 19:32:04 (3 years ago)

Author:

Стојков Марко <mst@…>

Branches:

dev

Children:

6b0fbbe, 79ae621

Parents:

466d1ac (diff), a3b5f34 (diff)
Note: this is a merge changeset, the changes displayed below correspond to the merge itself.
Use the (diff) links above to see all the changes relative to each parent.

Message:

Merged feature/search-questions into dev

File:

• src/FinkiChattery/FinkiChattery.Persistence/Repositories/Implementations/QuestionRepo.cs (modified) (3 diffs)

src/FinkiChattery/FinkiChattery.Persistence/Repositories/Implementations/QuestionRepo.cs

@@ -2 +2 @@
 using FinkiChattery.Persistence.Models;
 using FinkiChattery.Persistence.Repositories.Contracts;
+using Microsoft.Data.SqlClient;
 using Microsoft.EntityFrameworkCore;
 using System;
@@ -7 +8 @@
 using System.Linq;
 using System.Linq.Expressions;
+using System.Text.RegularExpressions;
 using System.Threading.Tasks;
 
@@ -110 +112 @@
             return questionDto;
         }
+
+        public async Task<List<QuestionPreviewDto>> SearchQuestions(string searchText, IEnumerable<Guid> categories)
+        {
+            var search = Regex.Replace(searchText, "[\\\\/:*?\"<>\\]\\[|&'`~^=%,(){}_\\-]", " ")
+                                     .Split(" ".ToArray(), StringSplitOptions.RemoveEmptyEntries)
+                                     .Select(c => $"\"{c}*\"");
+
+            var searchString = string.Join(" AND ", search);
+
+            var rawQuery = (IQueryable<Question>)
+                DbSet.FromSqlRaw(@"
+                        SELECT [q].[Id], [q].[Uid], [q].[Title], [q].[Views], [q].[AnswersCount], [q].[CreatedOn] 
+                        FROM [dbo].[Question] AS [q]
+                        INNER JOIN CONTAINSTABLE(Question, Search, @searchString, 30) ccontains ON [q].[Id] = ccontains.[KEY]",
+                        new SqlParameter("searchString", searchString))
+                .Include(x => x.QuestionCategories).ThenInclude(x => x.Category);
+
+            if (categories.Any())
+            {
+                rawQuery = rawQuery.Where(x => x.QuestionCategories.Any(y => categories.Contains(y.Category.Uid)));
+            }
+
+            return await rawQuery
+                .Select(x => new QuestionPreviewDto(x.Id,
+                                    x.Uid,
+                                    x.Title,
+                                    x.Views,
+                                    x.AnswersCount,
+                                    x.CreatedOn,
+                                    x.QuestionCategories.Select(y => new QuestionPreviewCategoryDto(y.Id, y.Uid, y.Category.Name))))
+                .ToListAsync();
+        }
     }
 }