ASP.NET Core 6.0 - Data Filters

KenHaggerty.Com has been logging requests for over 3 years. The UI started with just a chronological list of PageHits. As the number of PageHits grew, I implemented related entities and query filters. The HAP V2 implements all these features which were developed with real world data.

Pagination has the biggest impact on responsiveness when query results are large. See ASP.NET Core 2.2 - Bootstrap 4 Pagination. Pagination returns a maximum number (page size) of records from the query. The first PageHits filter set the query history length by days. Next, I implemented filters for user authenticated, non-essential cookie consent, and exclulde current IP address. The Days filter was implemented with a radio button group while the rest were checkboxes. Filtering a boolean property with a checkbox facilitates 2 states: include and only. I implemented radio buttons for boolean properties to allow 3 states: include, exclude, and only. After I added the UserAgent entity with a preferred boolean property, I implemented a preferred UA filter.

I added select filters for the method, path, queryString, referer, sessionId, userAgentId, screenSize, anonymizedIp, minutesOffset, and appUserId. The select options are populated with the distinct values from the query results before pagination. To better fit dropdown lists, the option's text for common UserAgent strings are condensed and long path, queryString, referer, and condensed UserAgent strings are truncated.

I often used backups from KenHaggerty.Com to develop new features. The AnalyticsSettings. BackupDateTimeOffset provided a date and time when the production data ended. The HAP V2 was developed and tested with a sample database derived from a KenHaggerty. Com backup. I developed a date and time picker based on input type="datetime-local" which allows the user to select a viewDateTime. I developed purge methods and settings which require Hangfire NuGet packages for "fire and forget" long running tasks.

public async Task OnGetAsync(
    DateTime viewDateTime,
    bool usePicker, 
    int p = 1,
    int daysFilter = PageDefaults.DaysFilter,
    int preferredFilter = PageDefaults.PreferredFilter,
    int authenticatedFilter = PageDefaults.AuthenticatedFilter,
    int consentedFilter = PageDefaults.ConsentedFilter,
    int apiFilter = PageDefaults.ApiFilter,
    int notFoundFilter = PageDefaults.NotFoundFilter,
    int maliciousIpsFilter = PageDefaults.MaliciousIpsFilter,
    int currentIpFilter = PageDefaults.CurrentIpFilter,
    int homeIpFilter = PageDefaults.HomeIpFilter,
    string method = "",
    string path = "",
    string queryString = "",
    string referer = "",
    int sessionId = 0,
    int userAgentId = 0,
    string screenSize = "",
    string anonymizedIp = "",
    int minutesOffset = 0,
    int appUserId = 0,
    bool purging = false)
{
    if (AnalyticsSettings.EnablePurge && !purging)
    {
        PurgeCount = await _analyticsService.GetPageHitPurgeCountAsync().ConfigureAwait(false);
        if (AnalyticsSettings.EnableHangfire) CanPurge = PurgeCount > AnalyticsSettings.PurgeThresholdLarge;
    }

    UsePicker = usePicker;
    CurrentPage = p == 0 ? 1 : p;
    DaysFilter = daysFilter;
    PreferredFilter = preferredFilter;
    AuthenticatedFilter = authenticatedFilter;
    ConsentedFilter = consentedFilter;
    ApiFilter = apiFilter;
    NotFoundFilter = notFoundFilter;
    MaliciousIpsFilter = maliciousIpsFilter;
    CurrentIp = HttpContext.Connection.RemoteIpAddress!.AnonymizeIP();
    CurrentIpFilter = currentIpFilter;
    HomeIp = AnalyticsSettings.HomeIp;
    HomeIpFilter = homeIpFilter;

    DateTimeFormat = AnalyticsSettings.UtcShortDateTimeFormat;
    var clientUtcOffsetMinutes = AnalyticsUtilities.GetClientUtcOffsetMinutes(HttpContext);
    if (clientUtcOffsetMinutes != null)
    {
        DateTimeFormat = AnalyticsSettings.ClientShortDateTimeFormat;
        clientUtcOffsetMinutes = clientUtcOffsetMinutes.GetValueOrDefault();
    }

    OldestDateTime = DateTimeOffset.UtcNow;
    var oldestDateTime = await _analyticsService.GetOldestPageHitDateAsync();
    if (oldestDateTime != null) OldestDateTime = (DateTimeOffset)oldestDateTime;

    ViewDateTime = DateTimeOffset.UtcNow;
    if (viewDateTime > DateTime.MinValue)
    {
        var utcDateTime = viewDateTime.AddMinutes(-clientUtcOffsetMinutes.GetValueOrDefault());
        ViewDateTime = new DateTimeOffset(utcDateTime.Year, utcDateTime.Month,
            utcDateTime.Day, utcDateTime.Hour, utcDateTime.Minute, utcDateTime.Second, TimeSpan.Zero);
    }

    BackupDateTime = AnalyticsSettings.BackupDateTimeOffset;
    if (BackupDateTime != null && !UsePicker) ViewDateTime = BackupDateTime!.Value;

    var viewDateTimeUtc = ViewDateTime;
    if (clientUtcOffsetMinutes != 0)
    {
        ViewDateTime = ViewDateTime.AddMinutes(clientUtcOffsetMinutes.GetValueOrDefault());
        if (oldestDateTime != null)
            OldestDateTime = OldestDateTime.AddMinutes(clientUtcOffsetMinutes.GetValueOrDefault());
        if (BackupDateTime != null)
            BackupDateTime = BackupDateTime!.Value.AddMinutes(clientUtcOffsetMinutes.GetValueOrDefault());
    }

    var fromDateTimeOffset = viewDateTimeUtc.AddDays(-DaysFilter);
    var pageHitsQuery = _analyticsService.GetPageHits(fromDateTimeOffset, viewDateTimeUtc);

    var preferredUserAgentIdList = await _analyticsService.GetPreferredUserAgentIdListAsync().ConfigureAwait(false);
    PreferredCount = preferredUserAgentIdList.Count;

    if (PreferredFilter > 0 && PreferredCount > 0)
    {
        if (PreferredFilter == 1)
        {
            pageHitsQuery = pageHitsQuery.Where(ph => !preferredUserAgentIdList.Contains(ph.UserAgentId)).AsQueryable();
            if (userAgentId > 0 && preferredUserAgentIdList.Contains(userAgentId)) userAgentId = 0;
        }
        if (PreferredFilter == 2)
        {
            pageHitsQuery = pageHitsQuery.Where(ph => preferredUserAgentIdList.Contains(ph.UserAgentId)).AsQueryable();
            if (userAgentId > 0 && !preferredUserAgentIdList.Contains(userAgentId)) userAgentId = 0;
        }
    }

    if (AuthenticatedFilter == 1) pageHitsQuery = pageHitsQuery.Where(ph => ph.AppUserId == null).AsQueryable();
    if (AuthenticatedFilter == 2) pageHitsQuery = pageHitsQuery.Where(ph => ph.AppUserId != null).AsQueryable();

    if (ConsentedFilter == 1) pageHitsQuery = pageHitsQuery.Where(ph => !ph.CookieConsent).AsQueryable();
    if (ConsentedFilter == 2) pageHitsQuery = pageHitsQuery.Where(ph => ph.CookieConsent).AsQueryable();

    if (ApiFilter == 1) pageHitsQuery = pageHitsQuery.Where(ph => !ph.Path.StartsWith("/api/")).AsQueryable();
    if (ApiFilter == 2) pageHitsQuery = pageHitsQuery.Where(ph => ph.Path.StartsWith("/api/")).AsQueryable();

    if (NotFoundFilter == 1) pageHitsQuery = pageHitsQuery.Where(ph => !ph.NotFound).AsQueryable();
    if (NotFoundFilter == 2) pageHitsQuery = pageHitsQuery.Where(ph => ph.NotFound).AsQueryable();

    var maliciousIpList = await _analyticsService.GetMaliciousIpListAsync().ConfigureAwait(false);
    MaliciousIpCount = maliciousIpList.Count;
    if (MaliciousIpsFilter > 0 && maliciousIpList.Count > 0)
    {
        if (MaliciousIpsFilter == 1)
            pageHitsQuery = pageHitsQuery.Where(ph => !maliciousIpList.Contains(ph.AnonymizedIp)).AsQueryable();
        if (MaliciousIpsFilter == 2)
            pageHitsQuery = pageHitsQuery.Where(ph => maliciousIpList.Contains(ph.AnonymizedIp)).AsQueryable();
    }

    if (!HomeIp.Equals(CurrentIp) && HomeIpFilter > 0)
    {
        if (HomeIpFilter == 1) pageHitsQuery = pageHitsQuery.Where(ph => ph.AnonymizedIp != HomeIp).AsQueryable();
        if (HomeIpFilter == 2) pageHitsQuery = pageHitsQuery.Where(ph => ph.AnonymizedIp == HomeIp).AsQueryable();
    }

    if (CurrentIpFilter == 1) pageHitsQuery = pageHitsQuery.Where(ph => ph.AnonymizedIp != CurrentIp).AsQueryable();
    if (CurrentIpFilter == 2) pageHitsQuery = pageHitsQuery.Where(ph => ph.AnonymizedIp == CurrentIp).AsQueryable();

    if (appUserId > 0)
        pageHitsQuery = pageHitsQuery.Where(ph => ph.AppUserId == appUserId).AsQueryable();

    if (!string.IsNullOrEmpty(method))
        pageHitsQuery = pageHitsQuery.Where(ph => ph.Method == method.ToUpper()).AsQueryable();

    if (!string.IsNullOrEmpty(path))
        pageHitsQuery = pageHitsQuery.Where(ph => ph.Path.Equals(path)).AsQueryable();

    if (!string.IsNullOrEmpty(queryString))
        pageHitsQuery = pageHitsQuery.Where(ph => ph.QueryString.Equals(queryString)).AsQueryable();

    if (!string.IsNullOrEmpty(referer))
        pageHitsQuery = pageHitsQuery.Where(ph => ph.Referer.StartsWith(referer)).AsQueryable();

    if (!string.IsNullOrEmpty(screenSize))
        pageHitsQuery = pageHitsQuery.Where(ph => ph.ScreenSize == screenSize).AsQueryable();

    if (!string.IsNullOrEmpty(anonymizedIp))
        pageHitsQuery = pageHitsQuery.Where(ph => ph.AnonymizedIp == anonymizedIp).AsQueryable();

    if (minutesOffset != 0)
        pageHitsQuery = pageHitsQuery.Where(ph => ph.MinutesOffset == minutesOffset).AsQueryable();

    if (userAgentId > 0)
        pageHitsQuery = pageHitsQuery.Where(ph => ph.UserAgentId == userAgentId).AsQueryable();

    if (sessionId > 0)
        pageHitsQuery = pageHitsQuery.Where(ph => ph.SessionId == sessionId).AsQueryable();

    Count = await pageHitsQuery.CountAsync().ConfigureAwait(false);
    if (Count > AnalyticsSettings.MaxFilteredRecords)
    {
        pageHitsQuery = pageHitsQuery.Take(AnalyticsSettings.MaxFilteredRecords);
        Count = AnalyticsSettings.MaxFilteredRecords;
    }

    MethodSelectList = await pageHitsQuery
        .Select(ph => new SelectListItem { Text = ph.Method, Value = ph.Method.ToLower() })
        .Distinct()
        .OrderBy(s => s.Text)
        .ToListAsync()
        .ConfigureAwait(false);

    if (MethodSelectList.Any(x => x.Value == method))
        Method = method;
    else
        Method = string.Empty;

    AppUserIdSelectList = await pageHitsQuery
        .Where(ph => ph.AppUserId != null)
        .Select(ph => new SelectListItem
        {
            // Implement with AppUser entity.
            //Text = ph.AppUser == null ? "Not Found" : ph.AppUser.LoginName,
            Text = ph.AppUserId == 1 ? "Single User" : "Not Found",
            Value = ph.AppUserId.ToString()
        })
        .Distinct()
        .OrderBy(s => s.Text)
        .ToListAsync()
        .ConfigureAwait(false);

    if (AppUserIdSelectList.Any(x => x.Value == appUserId.ToString())) AppUserId = appUserId;
    else AppUserId = 0;

    SessionSelectList = await pageHitsQuery
        .Select(ph => new SelectListItem
        {
            Text = ph.Session == null ? "Not Found" : 
            ph.Session.CreateDate.ToString(AnalyticsSettings.DateToStringFormat),
            Value = ph.SessionId.ToString()
        })
        .OrderBy(li => li.Value)
        .Distinct()
        .ToListAsync()
        .ConfigureAwait(false);

    if (SessionSelectList.Any(x => x.Value == sessionId.ToString())) SessionId = sessionId;
    else SessionId = 0;

    PathSelectList = await pageHitsQuery
        .Select(ph => new SelectListItem { Text = ph.Path, Value = ph.Path })
        .Distinct()
        .OrderBy(s => s.Text)
        .ToListAsync()
        .ConfigureAwait(false);

    foreach (var item in PathSelectList)
        item.Text = AnalyticsUtilities.CondenseString(item.Text, AnalyticsSettings.MaxSelectTextLength);

    if (PathSelectList.Any(x => x.Value == path))
        Path = path;
    else
        Path = string.Empty;

    QueryStringSelectList = await pageHitsQuery
        .Select(ph => new SelectListItem { Text = ph.QueryString, Value = ph.QueryString })
        .Distinct()
        .OrderBy(s => s.Text)
        .ToListAsync()
        .ConfigureAwait(false);

    foreach (var item in QueryStringSelectList)
        item.Text = AnalyticsUtilities.CondenseString(item.Text, AnalyticsSettings.MaxSelectTextLength);

    if (QueryStringSelectList.Any(x => x.Value == queryString))
        QueryString = queryString;
    else
        QueryString = string.Empty;

    RefererSelectList = await pageHitsQuery
        .Select(ph => new SelectListItem { Text = ph.Referer, Value = ph.Referer })
        .Distinct()
        .OrderBy(s => s.Text)
        .ToListAsync()
        .ConfigureAwait(false);

    foreach (var item in RefererSelectList)
        item.Text = AnalyticsUtilities.CondenseString(item.Text, AnalyticsSettings.MaxSelectTextLength);

    if (RefererSelectList.Any(x => x.Value == referer))
        Referer = referer;
    else
        Referer = string.Empty;

    UserAgentSelectList = await pageHitsQuery
        .Select(ph => new SelectListItem
        {
            Text = ph.UserAgent == null ? "Not Found" : ph.UserAgent.UAString,
            Value = ph.UserAgentId.ToString()
        })
        .Distinct()
        .ToListAsync()
        .ConfigureAwait(false);

    foreach (var item in UserAgentSelectList)
        item.Text = AnalyticsUtilities.CondenseUAString(item.Text, AnalyticsSettings.MaxSelectTextLength);

    UserAgentSelectList = UserAgentSelectList.OrderBy(li => li.Text).ToList();

    if (UserAgentSelectList.Any(x => x.Value == userAgentId.ToString()))
        UserAgentId = userAgentId;
    else
        UserAgentId = 0;

    ScreenSizeSelectList = await pageHitsQuery
        .Select(ph => new SelectListItem { Text = ph.ScreenSize })
        .Distinct()
        .OrderBy(s => s.Text)
        .ToListAsync()
        .ConfigureAwait(false);

    if (ScreenSizeSelectList.Any(x => x.Text == screenSize))
        ScreenSize = screenSize;
    else
        ScreenSize = string.Empty;

    AnonymizedIpSelectList = await pageHitsQuery
        .Select(ph => new SelectListItem { Text = ph.AnonymizedIp })
        .Distinct()
        .OrderBy(s => s.Text)
        .ToListAsync()
        .ConfigureAwait(false);

    if (AnonymizedIpSelectList.Any(x => x.Text == anonymizedIp))
        AnonymizedIp = anonymizedIp;
    else
        AnonymizedIp = string.Empty;

    MinutesOffsetSelectList = await pageHitsQuery
        .Where(ph => ph.MinutesOffset != 0)
        .Select(ph => new SelectListItem { Text = ph.MinutesOffset.ToString(), Value = ph.MinutesOffset.ToString() })
        .Distinct()
        .ToListAsync()
        .ConfigureAwait(false);

    if (MinutesOffsetSelectList.Any(x => x.Value == minutesOffset.ToString()))
        MinutesOffset = minutesOffset;
    else
        MinutesOffset = 0;

    if (CurrentPage > TotalPages) CurrentPage = TotalPages;
    if (Count > 0)
    {
        PageHits = await pageHitsQuery
            .AsNoTracking()
            .Skip((CurrentPage - 1) * PageSize)
            .Take(PageSize)
            .ToListAsync()
            .ConfigureAwait(false);

        if (clientUtcOffsetMinutes != null && clientUtcOffsetMinutes != 0)
            foreach (var ph in PageHits)
                ph.CreateDate = ph.CreateDate.AddMinutes(clientUtcOffsetMinutes.GetValueOrDefault());
    }
}

The HAP V2 implements an _AnalyticsLayout. cshtml and css classes to display data up to 1900 px wide on listing pages.