protected override async Task ExecuteAsync(CancellationToken stoppingToken) { while (!stoppingToken.IsCancellationRequested) { // Get all product order ids from the last month List <int> productOrderIds = await context.ProductOrders .AsNoTracking() .Where(x => x.Date >= DateTime.Now.Date.AddMonths(-1)) .Select(x => x.ProductId).ToListAsync(); // This will get all keywords info var keywords = await context.ProductKeywords .AsNoTracking() .Select(x => new { x.Keyword.Name, SearchVolume = x.Keyword.KeywordSearchVolumes.Where(z => z.Date >= DateTime.Now.Date.AddMonths(-1) && z.KeywordId == x.KeywordId).Count(), ProductIds = context.ProductKeywords .Where(z => z.KeywordId == x.KeywordId && z.Product.Niche.CategoryId == x.Product.Niche.Category.Id) .Select(x => new { productId = x.ProductId, categoryId = x.Product.Niche.Category.UrlId, x.Product.Rating, mediaCount = x.Product.ProductMedia.Count() }) .ToList(), category = new { x.Product.Niche.Category.UrlId, x.Product.Niche.Category.Name, x.Product.Niche.Category.UrlName, } }) .ToListAsync(); // Project the keywords into search words var searchWords = keywords.GroupBy(x => x.Name, (key, x) => new { name = key, searchVolume = x.Select(z => z.SearchVolume).FirstOrDefault(), categories = x.Select(z => new { z.category.Name, z.category.UrlId, z.category.UrlName, productIds = z.ProductIds.Where(w => w.categoryId == z.category.UrlId) .Select(a => new { a.productId, a.Rating, a.mediaCount, salesCount = productOrderIds.Count(c => c == a.productId) }) .ToList() }) .GroupBy(x => x.UrlId, (key, a) => new { UrlId = key, Name = a.Select(w => w.Name).FirstOrDefault(), UrlName = a.Select(w => w.UrlName).FirstOrDefault(), productIds = a.Select(w => w.productIds).FirstOrDefault() }) .Select(z => new { z.UrlId, z.Name, z.UrlName, salesCount = z.productIds.Select(w => w.salesCount).Sum(), mediaCount = z.productIds.Select(w => w.mediaCount).Sum(), rating = z.productIds.Select(w => w.Rating).Sum() / z.productIds.Count() }) .Select(z => new { UrlId = z.UrlId, Name = z.Name, UrlName = z.UrlName, Weight = (z.rating * 0.8) + (z.salesCount * 0.15) + (z.mediaCount * .05) }).ToList() }) .Select(x => new SearchWord { Name = x.name, SearchVolume = x.searchVolume, Categories = x.categories .OrderByDescending(z => z.Weight) .Select(z => new SuggestionCategory { UrlId = z.UrlId, Name = z.Name, UrlName = z.UrlName }) .ToList() }) .ToList(); Node rootNode = new Node(); List <string> categoryIds = await context.Categories.Select(x => x.UrlId).ToListAsync(); // Insert the search words foreach (SearchWord searchWord in searchWords) { searchSuggestionsService.Insert(searchWord, ref rootNode, searchWords, categoryIds); } searchSuggestionsService.root = rootNode; searchSuggestionsService.searchWords = searchWords; // Delete keyword search volumes List <KeywordSearchVolume> keywordSearchVolumes = await context.KeywordSearchVolumes.Where(x => x.Date < DateTime.Now.Date.AddMonths(-1)).ToListAsync(); if (keywordSearchVolumes.Count > 0) { context.RemoveRange(keywordSearchVolumes); await context.SaveChangesAsync(); } // 1 hour await Task.Delay(1000 * 60 * 60); } }
public async Task <int> Save() { return(await context.SaveChangesAsync()); }