/// <summary> /// Counts the occurences of single word. /// </summary> /// <param name="shitLeopardContext">The shit leopard context.</param> /// <param name="word">The word.</param> /// <param name="seasonId">Filter by season</param> /// <param name="episodeId">Filter by episode</param> /// <returns></returns> public static async Task <long> CountOccurencesOfSingleWord(this ShitLeopardContext shitLeopardContext, string word, int?seasonId = null, int?episodeId = null) { //how many time does f**k appear in season 1 var query = from sw in shitLeopardContext.ScriptWord join sl in shitLeopardContext.ScriptLine on sw.ScriptLineId equals sl.Id join s in shitLeopardContext.Script on sl.ScriptId equals s.Id join e in shitLeopardContext.Episode on s.EpisodeId equals e.Id select new { sw.Word, e.SeasonId, EpisodeId = e.OffsetId }; query = query.Where(x => x.Word == word); if (seasonId != null) { query = query.Where(x => x.SeasonId == seasonId); } if (episodeId != null) { query = query.Where(x => x.EpisodeId == episodeId); } return(await query.LongCountAsync()); }
public static async Task <long> CountOccurencesOfPhrase(this ShitLeopardContext shitLeopardContext, string phrase, int?seasonId = null, int?episodeId = null) { try { phrase = $"FORMSOF(FREETEXT, \"{phrase}\")"; var query = shitLeopardContext.ScriptLine .FromSqlRaw(@"SELECT s.* FROM ScriptLine S INNER JOIN Script C on S.ScriptId = C.Id inner join Episode E on E.Id = C.EpisodeId WHERE CONTAINS(S.Body, @p0) AND E.OffsetId = coalesce(@p1, e.OffsetId) AND E.SeasonId = coalesce(@p2,e.SeasonId)", phrase, episodeId, seasonId) .AsNoTracking(); return(await query.CountAsync()); } catch (Exception ex) { return(0); } }