private async Task UpdateCache()
{
Stopwatch sw = new Stopwatch();
Console.WriteLine("Updating Sankaku Cache");
_postedImages.Clear();
_cachedImages.Clear();
var context = new DiscordContext();
sw.Start();
var minTime = DateTime.Now - TimeSpan.FromDays(90);
Thread.CurrentThread.Name = "SankakuBot";
foreach (var setting in context.SabrinaSettings.Where(ss => ss.FeetChannel != null))
{
var channel = setting.FeetChannel.Value;
var whiteListedTags = context.SankakuTagWhiteList.Where(whiteTag => whiteTag.ChannelId == channel);
var blacklistedTags = context.SankakuTagBlacklist.Where(blackTag => blackTag.ChannelId == channel);
// Note: Part is commented out because of performance problems in LINQ to SQL. Normal SQL Query behaves much better
//var filteredImages = from image in context.SankakuImage
// where
// (from si in context.SankakuImageTag
// join wt in context.SankakuTagWhiteList
// on si.TagId equals wt.TagId
// where si.ImageId == image.Id
// && wt.ChannelId == channel
// select wt).Sum(cwT => cwT.Weight) > 29
// &&
// (from si in context.SankakuImageTag
// join wt in context.SankakuTagBlacklist
// on si.TagId equals wt.TagId
// where si.ImageId == image.Id
// && wt.ChannelId == channel
// select wt).Count() == 0
// &&
// (
// (from postedImg in context.SankakuPost
// where postedImg.ImageId == image.Id
// select postedImg).Count() == 0
// ||
// (from postedImg in context.SankakuPost
// where postedImg.ImageId == image.Id
// orderby postedImg.Date descending
// select postedImg.Date).First() < minTime
// )
// && image.Score > minScore
// select image;
var filteredImages = context.SankakuImage.FromSql(@"
SELECT TOP(100) *
FROM [Sankaku.Image] mainImg
WHERE
(SELECT SUM(Weight) FROM [Sankaku.ImageTag]
JOIN [Sankaku.TagWhiteList] on [Sankaku.TagWhiteList].TagID = [Sankaku.ImageTag].TagID
WHERE [Sankaku.ImageTag].ImageID = mainImg.ID AND [Sankaku.TagWhiteList].ChannelID = 448417831067975680) > 29
AND
(SELECT Count(*) FROM [Sankaku.ImageTag]
JOIN [Sankaku.TagBlacklist] on [Sankaku.TagBlacklist].TagID = [Sankaku.ImageTag].TagID
WHERE [Sankaku.ImageTag].ImageID = mainImg.ID AND [Sankaku.TagBlacklist].ChannelID = 448417831067975680) = 0
AND
((SELECT COUNT(1) Date FROM [Sankaku.Post]
WHERE [Sankaku.Post].ImageID = mainImg.ID) = 0
OR
(SELECT TOP(1) Date FROM [Sankaku.Post]
WHERE [Sankaku.Post].ImageID = mainImg.ID
ORDER BY Date DESC) < '2018-01-01')
AND
mainImg.Score > 60");
var cachedImages = await filteredImages.ToListAsync();
_cachedImages.Add(channel, cachedImages.Select(img => img.Id).ToList());
}
context.Dispose();
Console.WriteLine($"Finished updating Sankaku Cache after {sw.Elapsed}");
}