ASP.Net Core App Only Works for Few Minutes After Restarting Server

Question

I have an ASP.Net Core application that runs so slow all of a sudden that to load some pages I must manually reboot my server. Even then the site only loads right for a few minutes until it starts timing out again. At least I think it is timing out because all I get in my browser is "err connection closed" or a message to enable developer mode if I want to see the error with a recommendation not to use developer mode on my remote server. I have several other apps running on this sever, but they all use WebForms and don't have any problems, so this is obviously a problem with .Net Core.

This problem seems exclusive to task chains that begin with my GetPosts[] task which gets all posts and related data. Then the other tasks in the chain get paginated posts by taking from GetPosts[] 12 items at a time.

GetPosts[] looks like this:

 public async Task<Posts[]> GetPosts()
 {
 return await (from post in _context.Posts
   where post.Active == (bool?)true && post.Adminban == (bool?)false && post.Site == (int?)GlobalStatic.SITENUMBER()
   select post into pts
   select new Posts
   {
   Postid = pts.Postid,
   Title = pts.Title,
   Description = pts.Description,
   Dateposted = pts.Dateposted,
   Datemodified = pts.Datemodified,
   Video = pts.Video,
   Videostream = pts.Videostream,
   Location = pts.Location,
   Tags = pts.Tags,
   Cap = pts.Cap,
   Titletag = pts.Titletag,
   Metatag = pts.Metatag,
   Link = pts.Link,
   Linkurl = pts.Linkurl,
   Category = pts.Category,
   Subcategory = pts.Subcategory,
   Subcategory2 = pts.Subcategory2,
   Worldarea = pts.Worldarea,
   Region = pts.Region,
   City = pts.City,
   Sendemail = pts.Sendemail,
   Userid = pts.Userid,
   Active = pts.Active,
   Adminban = pts.Adminban,
   Posturl = linkgenerator.postlink($"{pts.Postid}", $"{pts.Title}"),
   Comments = (from cm in pts.Comments
   where cm.Active == (bool?)true && cm.Adminblock == (bool?)false
   select cm into cmts
   select new Comments
   {
   Commentid = cmts.Commentid,
   Comment = cmts.Comment,
   Date = cmts.Date,
   Active = cmts.Active,
   Adminblock = cmts.Adminblock,
   Userid = cmts.Userid,
   Post = cmts.Post,
   Ratings = cmts.Ratings.Select((Ratings rts) => new Ratings
   {
   Commentnumber = rts.Commentnumber,
   Rating = rts.Rating
   }).ToArray(),
   Images = cmts.Images.Select((Images imgs) => new Images
   {
   Image = imgs.Image,
   Imagename = imgs.Imagename
   }).ToArray(),
   Likes = cmts.Likes.Select((Likes lks) => new Likes
   {
   Likeid = lks.Likeid,
   Commentid = lks.Commentid
   }).ToArray()
   }).ToArray(),
   Commentcount = pts.Comments.Where((Comments cm) => cm.Active == (bool?)true && cm.Adminblock == (bool?)false).Count(),
   Ratings = (from r in pts.Ratings
  where r.CommentnumberNavigation.Active == (bool?)true && r.CommentnumberNavigation.Adminblock == (bool?)false
  select r into rtgs
  select new Ratings
  {
  Ratingid = rtgs.Ratingid,
  Rating = rtgs.Rating,
  Daterated = rtgs.Daterated,
  CommentnumberNavigation = new Comments
  {
  Commentid = rtgs.CommentnumberNavigation.Commentid
  }
  }).ToArray(),
   Ratingcount = pts.Ratings.Where((Ratings rc) => rc.CommentnumberNavigation.Active == (bool?)true && rc.CommentnumberNavigation.Adminblock == (bool?)false).Count(),
   Ratingavg = ratingavg((from rac in pts.Ratings
   where rac.CommentnumberNavigation.Active == (bool?)true && rac.CommentnumberNavigation.Adminblock == (bool?)false
   select rac into ra
   select ra.Rating).Average()),
   Images = pts.Images.Select((Images imgs) => new Images
   {
   Imageid = imgs.Imageid,
   Imagename = imgs.Imagename,
   Image = imgs.Image,
   Imagetype = imgs.Imagetype,
   Postid = imgs.Postid,
   Comment = imgs.Comment,
   Userid = imgs.Userid
   }).ToArray(),
   Primaryimage = computations.primaryimage((from pic in pts.Images
 where pic.Imagetype == (int?)1
 select pic into pi
 select new Images
 {
 Imageid = pi.Imageid,
 Image = pi.Image,
 Imagename = pi.Imagename
 }).DefaultIfEmpty().First()),
   Likes = pts.Likes.Select((Likes lks) => new Likes
   {
   Likeid = lks.Likeid,
   Post = lks.Post,
   Commentid = lks.Commentid
   }).ToArray(),
   Likecount = pts.Likes.Where((Likes lc) => lc.Commentid == null).Count(),
   WorldareaNavigation = new Worldarea
   {
   Worldarea1 = pts.WorldareaNavigation.Worldarea1,
   Worldareaacronym = pts.WorldareaNavigation.Worldareaacronym,
   Wurl = linkgenerator.worldarealink($"{pts.WorldareaNavigation.Worldareaacronym}")
   },
   RegionNavigation = new Regions
   {
   Regionname = pts.RegionNavigation.Regionname,
   Regionacronym = pts.RegionNavigation.Regionacronym,
   Rurl = linkgenerator.regionlink($"{pts.WorldareaNavigation.Worldareaacronym}", $"{pts.RegionNavigation.Regionacronym}")
   },
   CityNavigation = new Cities
   {
   City = pts.CityNavigation.City,
   Cityacronym = pts.CityNavigation.Cityacronym,
   Cityurl = linkgenerator.citylink($"{pts.WorldareaNavigation.Worldareaacronym}", $"{pts.RegionNavigation.Regionacronym}", $"{pts.CityNavigation.Cityacronym}")
   },
   CategoryNavigation = new Categories
   {
   Categoryname = pts.CategoryNavigation.Categoryname,
   Categorylongname = pts.CategoryNavigation.Categorylongname,
   Categorytitle = pts.CategoryNavigation.Categorytitle,
   Categorydescription = pts.CategoryNavigation.Categorydescription,
   Categorykeywords = pts.CategoryNavigation.Categorykeywords,
   Categorymeta = pts.CategoryNavigation.Categorymeta,
   Categoryurl = pts.CategoryNavigation.Categoryurl,
   Curl = linkgenerator.categorylink($"{pts.CategoryNavigation.Categoryurl}")
   },
   SubcategoryNavigation = new Subcategories
   {
   Subcategoryname = pts.SubcategoryNavigation.Subcategoryname,
   Subcategorylongname = pts.SubcategoryNavigation.Subcategorylongname,
   Subcategorytitle = pts.SubcategoryNavigation.Subcategorytitle,
   Subcategorydescription = pts.SubcategoryNavigation.Subcategorydescription,
   Subcategorymeta = pts.SubcategoryNavigation.Subcategorymeta,
   Subcategorykeywords = pts.SubcategoryNavigation.Subcategorykeywords,
   Subcategoryurl = pts.SubcategoryNavigation.Subcategoryurl,
   Scurl = linkgenerator.subcategorylink($"{pts.CategoryNavigation.Categoryurl}", $"{pts.SubcategoryNavigation.Subcategoryurl}")
   },
   Subcategory2Navigation = new Subcategory2
   {
   Subcategory2name = pts.Subcategory2Navigation.Subcategory2name,
   Subcategory2longname = pts.Subcategory2Navigation.Subcategory2longname,
   Subcategory2title = pts.Subcategory2Navigation.Subcategory2title,
   Subcategory2description = pts.Subcategory2Navigation.Subcategory2description,
   Subcategory2meta = pts.Subcategory2Navigation.Subcategory2meta,
   Subcategory2keywords = pts.Subcategory2Navigation.Subcategory2keywords,
   Subcategory2url = pts.Subcategory2Navigation.Subcategory2url,
   Sc2url = linkgenerator.subcategory2link($"{pts.CategoryNavigation.Categoryurl}", $"{pts.SubcategoryNavigation.Subcategoryurl}", $"{pts.Subcategory2Navigation.Subcategory2url}")
   }
   }).ToArrayAsync();
 }

The next task is gets paginated posts

public async Task<List<Posts>> GetPaginatedResult(int currentPage, int pageSize)
 {
 return (await GetPosts()).OrderByDescending((Posts d) => d.Postid).Skip((currentPage - 1) * pageSize).Take(pageSize)
 .ToList();
 }

Then to get just a single post

public async Task<Posts> GetSinglePost(int? id)
 {
 return (await GetPosts()).Where((Posts p) => p.Postid == id).FirstOrDefault();
 }

I was able to mitigate this problem in the past by increasing my sqlServerOptions.CommandTimeout setting. First I increased it to 60 seconds, then 90, and then 120, but now I think I might need to increase it to something that could never be reached like 1000000000000 to have a chance.

I started building my app by copying the code used in the demo app ContosoCrafts (https://github.com/dotnet-presentations/ContosoCrafts) which does not use a database, so to make it work with a database I also looked at Contoso University (https://github.com/alimon808/contoso-university) and other examples posted by Microsoft. I always wondered why Microsoft's examples always started off by getting all data before taking paginated results from it because I always suspected that could lead to problems with large amounts of data down the road, but I also had never used EF so I hoped that Microsoft would have had the sense to build something into EF that would make it scalable.

I suspect that the site might be getting DDOS'd, but if that were the case wouldn't other apps on the same server suffer. Is there any way to see how much bandwidth is being used by the app? Is there bandwidth maximum for Windows Server in general and if so is there a way to see how much of that quote is being used at any given time?

I cannot check Windows Evert Viewer because of reasons discussed in a previous posts my operating system cannot get role and feature data. I have been looking for a solution to fix the OS which does not require me to backup certificates, websites, IIS, and databases but have not found one and because the aforementioned tasks will take a long time I have been putting it off for a couple of months now.

Finally, is there a way that I could maybe download a Windows Update on my local machine, upload it to my remote server, and run it that way to patch the OS without having to get roles and features data?

UPDATE:

I tried enabling EnableRetryOnFailure() and running the site using VisualStudio. I selected a different option from the little pop up that runs when I hit the play button, but I don't recall which. I got the following error

An unhandled exception occurred while processing the request.
Win32Exception: The wait operation timed out.
Unknown location

SqlException: Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
Microsoft.Data.SqlClient.SqlCommand+<>c.<ExecuteDbDataReaderAsync>b__169_0(Task<SqlDataReader> result)

Stack Query Cookies Headers Routing
Win32Exception: The wait operation timed out.

Show raw exception details
SqlException: Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
Microsoft.Data.SqlClient.SqlCommand+<>c.<ExecuteDbDataReaderAsync>b__169_0(Task<SqlDataReader> result)
System.Threading.Tasks.ContinuationResultTaskFromResultTask<TAntecedentResult, TResult>.InnerInvoke()
System.Threading.Tasks.Task+<>c.<.cctor>b__277_0(object obj)
System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, object state)
System.Threading.Tasks.Task.ExecuteWithThreadLocal(ref Task currentTaskSlot, Thread threadPoolThread)
Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable<T>+AsyncEnumerator.InitializeReaderAsync(DbContext _, bool result, CancellationToken cancellationToken)
Microsoft.EntityFrameworkCore.Storage.ExecutionStrategy.ExecuteImplementationAsync<TState, TResult>(Func<DbContext, TState, CancellationToken, Task<TResult>> operation, Func<DbContext, TState, CancellationToken, Task<ExecutionResult<TResult>>> verifySucceeded, TState state, CancellationToken cancellationToken)
Microsoft.EntityFrameworkCore.Storage.ExecutionStrategy.ExecuteImplementationAsync<TState, TResult>(Func<DbContext, TState, CancellationToken, Task<TResult>> operation, Func<DbContext, TState, CancellationToken, Task<ExecutionResult<TResult>>> verifySucceeded, TState state, CancellationToken cancellationToken)
Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable<T>+AsyncEnumerator.MoveNextAsync()
Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.ToListAsync<TSource>(IQueryable<TSource> source, CancellationToken cancellationToken)
Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.ToListAsync<TSource>(IQueryable<TSource> source, CancellationToken cancellationToken)
Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.ToArrayAsync<TSource>(IQueryable<TSource> source, CancellationToken cancellationToken)

PostAlmostAnything.SiteServices.PostsService.GetPosts() in PostsService.cs

}
public async Task<Posts[]> GetPosts()
{
        IAppCache cache = new CachingService(CachingService.DefaultCacheProvider) { DefaultCachePolicy = new CacheDefaults { DefaultCacheDurationSeconds = GlobalStatic.SITEPOSTSCACHEDURATION() } };
        string cacheKey = $"GetPosts";
        var cacheobject = await (from post in _context.Posts
                           where post.Active == (bool?)true && post.Adminban == (bool?)false && post.Site == (int?)GlobalStatic.SITENUMBER()
                           select post into pts
                           select new Posts
                           {
                               Postid = pts.Postid,
                               Title = pts.Title,

PostAlmostAnything.SiteServices.PostsService.GetPaginatedResult(int currentPage, int pageSize) in PostsService.cs

                              Sc2url = linkgenerator.subcategory2link($"{pts.CategoryNavigation.Categoryurl}", $"{pts.SubcategoryNavigation.Subcategoryurl}", $"{pts.Subcategory2Navigation.Subcategory2url}")
                          }
                      }).ToArrayAsync();
    }
    public async Task<List<Posts>> GetPaginatedResult(int currentPage, int pageSize)
{
    return (await GetPosts()).OrderByDescending((Posts d) => d.Postid).Skip((currentPage - 1) * pageSize).Take(pageSize)
        .ToList();
}
public async Task<List<Posts>> GetPaginatedResultByCategoryCity(string catslug, string waslug, string regionslug, string cityslug, int currentPage, int pageSize)
{
    return (from d in await GetPosts()

PostAlmostAnything.Pages.IndexModel.OnGetAsync() in Index.cshtml.cs

    {
        PostService = postService;
    }
    public async Task<PageResult> OnGetAsync()
    {
        Posts = await PostService.GetPaginatedResult(CurrentPage, PageSize);
        return Page();
    }
}

}
Microsoft.AspNetCore.Mvc.RazorPages.Infrastructure.ExecutorFactory+GenericTaskHandlerMethod.Convert<T>(object taskAsObject)
Microsoft.AspNetCore.Mvc.RazorPages.Infrastructure.ExecutorFactory+GenericTaskHandlerMethod.Execute(object receiver, object[] arguments)
Microsoft.AspNetCore.Mvc.RazorPages.Infrastructure.PageActionInvoker.InvokeHandlerMethodAsync()
Microsoft.AspNetCore.Mvc.RazorPages.Infrastructure.PageActionInvoker.InvokeNextPageFilterAsync()
Microsoft.AspNetCore.Mvc.RazorPages.Infrastructure.PageActionInvoker.Rethrow(PageHandlerExecutedContext context)
Microsoft.AspNetCore.Mvc.RazorPages.Infrastructure.PageActionInvoker.Next(ref State next, ref Scope scope, ref object state, ref bool isCompleted)
Microsoft.AspNetCore.Mvc.RazorPages.Infrastructure.PageActionInvoker.InvokeInnerFilterAsync()
Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeNextResourceFilter>g__Awaited|24_0(ResourceInvoker invoker, Task lastTask, State next, Scope scope, object state, bool isCompleted)
Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.Rethrow(ResourceExecutedContextSealed context)
Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.Next(ref State next, ref Scope scope, ref object state, ref bool isCompleted)
Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeFilterPipelineAsync>g__Awaited|19_0(ResourceInvoker invoker, Task lastTask, State next, Scope scope, object state, bool isCompleted)
Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeAsync>g__Logged|17_1(ResourceInvoker invoker)
Microsoft.AspNetCore.Routing.EndpointMiddleware.<Invoke>g__AwaitRequestTask|6_0(Endpoint endpoint, Task requestTask, ILogger logger)
Microsoft.AspNetCore.Authorization.AuthorizationMiddleware.Invoke(HttpContext context)
Microsoft.AspNetCore.Authentication.AuthenticationMiddleware.Invoke(HttpContext context)
Microsoft.AspNetCore.Diagnostics.StatusCodePagesMiddleware.Invoke(HttpContext context)
Microsoft.AspNetCore.Diagnostics.EntityFrameworkCore.MigrationsEndPointMiddleware.Invoke(HttpContext context)
Microsoft.AspNetCore.Diagnostics.EntityFrameworkCore.DatabaseErrorPageMiddleware.Invoke(HttpContext httpContext)
Microsoft.AspNetCore.Diagnostics.EntityFrameworkCore.DatabaseErrorPageMiddleware.Invoke(HttpContext httpContext)
Microsoft.AspNetCore.Diagnostics.DeveloperExceptionPageMiddleware.Invoke(HttpContext context)

Answer 1

Maybe you should remove all of ToArray and ToArrayAsync from GetPosts and make the corresponding adjustments. (At least try removing ToArrayAsync, which probably causes a large data transfer of all of your data from SQL server to your program before applying Skip and Take. You should write a code where Skip and Take is interpreted on SQL server).

Which existing Contoso sample file does not perform the pagination efficiently in your opinion?