ASP.NET Core 5.0 - Analytics Schema and Integration

This article describes the ASP.NET Core 5.0 - Homegrown Analytics Project integration with existing projects and the SQL scripts for schema, tables, and default data. I will assume you have downloaded the ASP.NET Core 5.0 - Homegrown Analytics Project.

Homegrown Analytics Project and Article Series

The project is feature complete and will increment the version with updates. The details page includes the version change log. This project is designed and organized to allow easy integration with existing projects. The KenHaggerty. Com. SingleUser NuGet package segregates the user logic. SQL scripts to create the schema, tables, and default data are included in the package. Details, screenshots, and related articles can be found at ASP.NET Core 5.0 - Homegrown Analytics Project.

Namespaces and Folder Layout

The project is designed and organized to allow easy integration with existing projects. I have been tweaking the project as I integrate it with my live sites and real-world data. The namespaces match the folder layout.

Solution Explorer Top.
Solution Explorer Bottom.
Data SQL Scripts

The Data folder includes an AnalyticsSchema migration and 3 SQL scripts. You can apply the migration by executing the update-database command in the Package Manager Console. Use the CreateAnalyticsSchema script with an existing database. To create a new database, use the CreateDbAndAnalyticsSchema script, replace NewDatabaseName to set the database name. Use the RemoveAnalyticsSchema script to remove the analytics tables, Hangfire tables, and their schemas. The scripts can be applied with Visual Studio 2019. Open the script window and use Connect and Execute command buttons.

Connect SQL.

The Server Explorer view of analytics tables.

Server Explorer Tables.
NuGet Packages

SQL Server with EntityFramework Core requires Microsoft. EntityFrameworkCore. SqlServer. Microsoft. EntityFrameworkCore. Tools is required for migrations.

EF Core NuGet Packages.
Connection String
"ConnectionStrings": {
    "AnalyticsConnection": "Server=(localdb)\\mssqllocaldb;Database=HomegrownAnalytics;Trusted_Connection=True;MultipleActiveResultSets=true;"
Startup > ConfigureServices:
var analyticsConnection = Configuration.GetConnectionString("AnalyticsConnection");
services.AddDbContext<AnalyticsDbContext>(options => options.UseSqlServer(analyticsConnection));
Data > AnalyticsDbContext.cs:
public class AnalyticsDbContext : DbContext
public AnalyticsDbContext(DbContextOptions<AnalyticsDbContext> options)
    : base(options)

public DbSet<AnalyticsSchema> AnalyticsSchemas { get; set; }
public DbSet<PageHit> PageHits { get; set; }
public DbSet<UserAgent> UserAgents { get; set; }
public DbSet<Session> Sessions { get; set; }
public DbSet<NotFoundError> NotFoundErrors { get; set; }
public DbSet<CaptchaChallenge> CaptchaChallenges { get; set; }

protected override void OnModelCreating(ModelBuilder builder)

    builder.Entity<AnalyticsSchema>().ToTable("AnalyticsSchemas", "analytics");

    builder.Entity<UserAgent>().ToTable("UserAgents", "analytics");
    builder.Entity<UserAgent>().Property(prop => prop.Id).UseIdentityColumn(1000, 1);
    builder.Entity<UserAgent>().HasIndex(ua => ua.UAString);

    builder.Entity<NotFoundError>().ToTable("NotFoundErrors", "analytics");
    builder.Entity<NotFoundError>().Property(prop => prop.Id).UseIdentityColumn(1000, 1);
    builder.Entity<NotFoundError>().HasIndex(nfe => nfe.CreateDate);

    builder.Entity<Session>().ToTable("Sessions", "analytics");
    builder.Entity<Session>().Property(prop => prop.Id).UseIdentityColumn(1000, 1);
    builder.Entity<Session>().HasIndex(s => s.CreateDate);
        .HasMany(s => s.PageHits)
        .WithOne(ph => ph.Session)

    builder.Entity<PageHit>().ToTable("PageHits", "analytics");
    builder.Entity<PageHit>().Property(prop => prop.Id).UseIdentityColumn(1000, 1);
    builder.Entity<PageHit>().HasIndex(ph => ph.CreateDate);

    builder.Entity<CaptchaChallenge>().ToTable("CaptchaChallenges", "analytics");
Optional Features

The Startup class has static properties for options. I address integrating the options in their related articles.

// Enables the SessionId cookie without non-essential cookie consent.
public static bool SessionIdCookieIsEssential { get; } = true;
// Enables SignalR for online user count.
public static bool UseSignalR { get; } = false;
// Enables the Google Recaptcha demo.
// Requires a registered key and secret set in appsettings.json for the
// GoogleRecaptchaKey and GoogleRecaptchaSecret properties.
public static bool UseGoogleRecaptcha { get; } = false;
// Enables the Hangfire database and Dashboard.
// Search project for UseHangfire to locate commented declarations and functions
// The database must exist for Hangfire migration
public static bool UseHangfire { get; } = false;
// Used to test Error page and NotFoundErrors.
private readonly bool debugExceptionHandling = false;
Data Folder

AnalyticsDbContext needs to be implemented or merged with an existing DbContext. The project includes a migration based on the project's Namespace. The migration and MigrationCommands.txt includes code for the default data.

Solution Explorer Data Folder.
wwwroot Folder

The ajax-modal.js and message-modal.js are required. The site.css and site.js need to be included or merged. The onlinecount.js and the lib/signalr are required for SignalR online user count only. I address integrating Hubs and SignalR in the ASP.NET Core 5.0 - Online User Count with SignalR article.

 Solution Explorer wwwroot Folder.
Entities Folder

All entities which map to the database are in the Entities folder. Properties are decorated with System. ComponentModel. DataAnnotations which define SQL column properties. The CaptchaChallenge entity is used with UseGoogleRecaptcha only and is not required.

Solution Explorer Entities Folder.
Pages Folder

Analytics pages are in the Admin > Analytics folder. _Layout.cshtml and _CookieConsentPartial.cshtml in the Shared folder are configured to manage client side-cookies and consent. I address cookie consent in the ASP.NET Core 5.0 - Cookie Consent and GDPR article. I address the Error page which traps and stores the NotFoundErrors in the ASP.NET Core 5.0 - Not Found Errors article. The CaptchaChallenges and CaptchaResult pages are used with UseGoogleRecaptcha only and are not required. I address implementing Google Recaptcha in the ASP.NET Core 5.0 - Is Human with Google reCAPTCHA article.

 Solution Explorer Pages Folder.
Services Folder

The AnalyticsMiddleware uses the request and cookies to capture and store PageHits. The middleware is fault tolerant for not found foriegn keys. The AnonymizeIpAddressExtention makes IP addresses anonymous by blanking the Host ID (the last segment) with zero. The AnalyticsService and DataAcessResult are used for database operations. The HangfireService is used with Hangfire only and is not required. I address implementing Hangfire in the ASP.NET Core 5.0 - Log Maintenance with Hangfire article.

Solution Explorer Services Folder.

Implement CheckConsentNeeded from Microsoft. AspNetCore. CookiePolicy to request user consent for non-essential cookies.

Startup > ConfigureServices:
services.Configure<CookiePolicyOptions>(options =>
    // Sets the display of the Cookie Consent banner (/Pages/Shared/_CookieConsentPartial.cshtml).
    // This lambda determines whether user consent for non-essential cookies is needed for a given request.
    options.CheckConsentNeeded = context => true;
    options.MinimumSameSitePolicy = SameSiteMode.Strict;

Add the AnalyticsService to the services collection.

Startup > ConfigureServices:
services.AddTransient<IAnalyticsService, AnalyticsService>();

Add the UseCookiePolicy extension to apply the CheckConsentNeeded option to the pipeline. Add the UseAnalytics extension after UseAuthentication to apply the AnalyticsMiddleware to the pipeline. The AnalyticsMiddleware records a PageHit for the current request.

Startup > Configure:

The Error page captures NotFoundErrors with UseStatusCodePagesWithReExecute extension and the 404 status code.

Startup > Configure:

The AnalyticsMiddleware captures ClaimsPrincple. Identity. IsAuthenticated but does not depend on user logic. Users should at least be authenticated to access the Admin folder.

Startup > ConfigureServices:
services.AddRazorPages(options => { options.Conventions.AuthorizeFolder("/Admin"); });

The project implements simple user logic with the KenHaggerty. Com. SingleUser NuGet package. See ASP.NET Core 5.0 - SingleUser NuGet Package

SingleUser NuGet Package.

Create the database schema, tables, and default data, set the connection string, install NuGet packages, copy or merge the required files, implement services and middleware, update namespaces and paths, build, run, and test.

Ken Haggerty
Created 12/31/20
Updated 01/06/21 05:57 GMT

Log In or Reset Quota to read more.

Article Tags:

Analytics EF Core Model SQL
Successfully completed. Thank you for contributing.
Contribute to enjoy content without advertisments.
Something went wrong. Please try again.



Not accepting new comments.

Submit your comment. Comments are moderated.

User Image.
DisplayedName - Member Since ?