/// <summary> /// Get a word id list for all the words that is related to the passed words /// </summary> /// <param name="db">database</param> /// <param name="wordValues">words</param> /// <returns>a word id list for all the words that is related to the passed words</returns> /// <example>var exludeIds = WordDatabaseService.GetWordIdList(db, new List<string> { "BY", "NAVN" });</example> public static IEnumerable <int> GetWordIdList(WordHintDbContext db, IEnumerable <string> wordValues) { var words = db.Words.Where(w => wordValues.Contains(w.Value)); if (words.Any()) { var wordIds = words.Select(w => w.WordId); var relatedWords = db.WordRelations.Where(w => wordIds.Contains(w.WordFromId) || wordIds.Contains(w.WordToId)) .AsNoTracking(); if (relatedWords.Any()) { var wordList = new List <int>(); // build flattened distinct list foreach (var relation in relatedWords) { wordList.Add(relation.WordToId); wordList.Add(relation.WordFromId); } // add main keys wordList.AddRange(wordIds); // and sort wordList.Sort(); // return distinct ids return(wordList.Distinct()); } } return(new List <int>()); }
/// <summary> /// Get a word id list for all the words that is related to the passed word /// </summary> /// <param name="db">database</param> /// <param name="wordValue">word</param> /// <returns>a word id list for all the words that is related to the passed word</returns> /// <example>var exludeIds = WordDatabaseService.GetWordIdList(db, "BY");</example> public static IEnumerable <int> GetWordIdList(WordHintDbContext db, string wordValue) { var word = db.Words.SingleOrDefault(w => w.Value == wordValue); if (word != null) { var wordId = word.WordId; var relatedWords = db.WordRelations.Where(w => w.WordFromId == word.WordId || w.WordToId == word.WordId) .AsNoTracking(); if (relatedWords.Any()) { var wordList = new List <int>(); // build flattened distinct list foreach (var relation in relatedWords) { wordList.Add(relation.WordToId); wordList.Add(relation.WordFromId); } // add main key wordList.Add(wordId); // and sort wordList.Sort(); // return distinct ids return(wordList.Distinct()); } } return(new List <int>()); }
public static string GetLastWordFromSource(WordHintDbContext db, string source) { Log.Information("Looking for last word from '{0}'", source); var lastWord = db.States // .AsNoTracking() .OrderByDescending(w => w.CreatedDate) .FirstOrDefault(item => item.Source == source); if (lastWord != null) { // detach in order to clean this from the db tracked cache db.Entry(lastWord).State = EntityState.Detached; Log.Information("Using the last word '{0}'", lastWord); return(lastWord.Word.RemoveDiacriticsToNorwegian()); } return(null); }
public static string GetLastWordFromComment(WordHintDbContext db, string source, string comment) { if (comment != null) { Log.Information("Looking for last word using comment '{0}' from '{1}'", comment, source); var lastWordWithComment = db.States // .AsNoTracking() .FirstOrDefault(item => item.Source == source && item.Comment == comment); if (lastWordWithComment != null) { // detach in order to clean this from the db tracked cache db.Entry(lastWordWithComment).State = EntityState.Detached; Log.Information("Using the last word with comment '{0}', last word '{1}'", comment, lastWordWithComment); return(lastWordWithComment.Word.RemoveDiacriticsToNorwegian()); } } return(null); }
public static string GetLastWordFromLetterCount(WordHintDbContext db, string source, int letterCount) { if (letterCount > 0) { Log.Information("Looking for last word using letter count '{0}' from '{1}'", letterCount, source); var lastWordWithPatternLength = db.States // .AsNoTracking() .FirstOrDefault(item => item.Source == source && item.NumberOfLetters == letterCount); if (lastWordWithPatternLength != null) { // detach in order to clean this from the db tracked cache db.Entry(lastWordWithPatternLength).State = EntityState.Detached; Log.Information("Using the last word with letter count '{0}', last word '{1}'", letterCount, lastWordWithPatternLength); return(lastWordWithPatternLength.Word.RemoveDiacriticsToNorwegian()); } } return(null); }
public static void AddToDatabase(WordHintDbContext db, string source, User user, string wordText, IEnumerable <string> relatedValues, TextWriter writer = null, bool doStoreState = true) { // Note that tracking should be disabled to speed things up // note that this doesn't load the virtual properties, but loads the object ids after a save // db.ChangeTracker.QueryTrackingBehavior = QueryTrackingBehavior.NoTracking; // ensure uppercase wordText = wordText.ToUpper(); var word = new Word { Language = "no", Value = wordText, NumberOfLetters = ScraperUtils.CountNumberOfLetters(wordText), NumberOfWords = ScraperUtils.CountNumberOfWords(wordText), User = user, CreatedDate = DateTime.Now, Source = source }; // ensure related are all uppercase and distinct var relatedValuesUpperCase = relatedValues.Select(x => x.ToUpper()).Distinct(); // get all related words (hints) as Word objects var relatedWords = relatedValuesUpperCase.Select(hintText => new Word { Language = "no", Value = hintText, NumberOfLetters = ScraperUtils.CountNumberOfLetters(hintText), NumberOfWords = ScraperUtils.CountNumberOfWords(hintText), User = user, CreatedDate = DateTime.Now, Source = source }); AddToDatabase(db, source, word, relatedWords, writer, doStoreState); }
public static void UpdateState(WordHintDbContext db, string source, Word word, TextWriter writer = null, bool doUseCommentAsKey = false) { var wordText = word.Value; var wordComment = word.Comment; var wordLength = word.Value.Length; // use actual word length State stateEntity = null; if (doUseCommentAsKey) { wordLength = 0; // ignore using word Length - set to zero stateEntity = db.States // .AsNoTracking() .FirstOrDefault(item => item.Source == source && item.Comment == wordComment); } else { stateEntity = db.States // .AsNoTracking() .FirstOrDefault(item => item.Source == source && item.NumberOfLetters == wordLength); } // Validate entity is not null if (stateEntity != null) { stateEntity.CreatedDate = DateTime.Now; stateEntity.Word = wordText; // add state db.States.Update(stateEntity); if (writer != null) { writer.WriteLine("Updated state with '{0}' as last processed word for '{1}' letters with source '{2}'.", wordText, wordLength, source); } } else { stateEntity = new State { Word = wordText, NumberOfLetters = wordLength, CreatedDate = DateTime.Now, Source = source, Comment = wordComment }; // add new state db.States.Add(stateEntity); if (writer != null) { writer.WriteLine("Added state with '{0}' as last processed word for '{1}' letters with source '{2}'.", wordText, wordLength, source); } } // Save changes in database db.SaveChanges(); // detach in order to clean this from the db tracked cache db.Entry(stateEntity).State = EntityState.Detached; }
public static void AddToDatabase(WordHintDbContext db, string source, Word word, IEnumerable <Word> relatedWords, TextWriter writer = null, bool doStoreState = true) { // Note that tracking should be disabled to speed things up // note that this doesn't load the virtual properties, but loads the object ids after a save // db.ChangeTracker.QueryTrackingBehavior = QueryTrackingBehavior.NoTracking; if (word == null || word != null && word.Value == null) { return; } if (relatedWords == null || relatedWords != null && relatedWords.Count() == 0) { return; } // check if word already exists var existingWord = db.Words.Where(w => w.Value == word.Value).FirstOrDefault(); if (existingWord != null) { // update reference to existing word (i.e. reuse the word) word = existingWord; } else { // add new word db.Words.Add(word); db.SaveChanges(); } // update that we are processing this word if (doStoreState) { UpdateState(db, source, word, writer); } // Note! ensure related are all uppercase before this method is called var relatedValuesUpperCase = relatedWords.Select(x => x.Value); // find out which related words already exist in the database var existingRelatedWords = db.Words.Where(x => relatedValuesUpperCase.Contains(x.Value)).ToList(); // which words need to be added? var newRelatedWords = relatedWords.Where(x => !existingRelatedWords.Any(a => a.Value == x.Value)).ToList(); if (newRelatedWords.Count > 0) { db.Words.AddRange(newRelatedWords); db.SaveChanges(); if (writer != null) { writer.WriteLine("Added '{0}'", string.Join(",", newRelatedWords.Select(i => i.Value).ToArray())); } } else { if (writer != null) { writer.WriteLine("Skipped adding '{0}'", string.Join(",", existingRelatedWords.Select(i => i.Value).ToArray())); } } // what relations needs to be added? var allRelatedWords = existingRelatedWords.Concat(newRelatedWords); var allWordRelationsFrom = allRelatedWords.Select(hint => // only use the ids to speed things up new WordRelation { WordFromId = word.WordId, WordToId = hint.WordId, Source = source, CreatedDate = DateTime.Now } ); // add relation from each hint to word as well var allWordRelationsTo = allRelatedWords.Select(hint => // only use the ids to speed things up new WordRelation { WordFromId = hint.WordId, WordToId = word.WordId, Source = source, CreatedDate = DateTime.Now } ); // all relations var allWordRelations = allWordRelationsFrom.Concat(allWordRelationsTo).Distinct(); // which relations need to be added? var newWordRelations = allWordRelations.Where(x => !db.WordRelations.Any(z => z.WordFromId == x.WordFromId && z.WordToId == x.WordToId)).ToList(); // find out which relations already exist in the database var existingWordRelations = allWordRelations.Except(newWordRelations); if (newWordRelations.Count > 0) { db.WordRelations.AddRange(newWordRelations); db.SaveChanges(); if (db.ChangeTracker.QueryTrackingBehavior == QueryTrackingBehavior.NoTracking) { // without tracking we don't have the word value, so use only the wordids if (writer != null) { writer.WriteLine("Related '{0}' to '{1}'", string.Join(",", newWordRelations.Select(i => i.WordToId).ToArray().Distinct()), word.Value); } } else { // with tracking we can output the actual words if (writer != null) { writer.WriteLine("Related '{0}' to '{1}'", string.Join(",", newWordRelations.Select(i => i.WordTo.Value).ToArray().Distinct()), word.Value); } } } else { if (db.ChangeTracker.QueryTrackingBehavior == QueryTrackingBehavior.NoTracking) { // without tracking we don't have the word value, so use only the wordids if (writer != null) { writer.WriteLine("Skipped relating '{0}' to '{1}'", string.Join(",", existingWordRelations.Select(i => i.WordToId).ToArray().Distinct()), word.Value); } } else { // with tracking we can output the actual words if (writer != null) { writer.WriteLine("Skipped relating '{0}' to '{1}'", string.Join(",", existingWordRelations.Select(i => i.WordTo.Value).ToArray().Distinct()), word.Value); } } } }