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);
}
}
}
}