ASP.NET Core 5.0 - Analytics Schema and Integration

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.

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.

The Server Explorer view of analytics tables.

NuGet Packages

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

Connection String

appsettings.json:

"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));

AnalyticsDbContext

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)
{
    base.OnModelCreating(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);
    builder.Entity<Session>()
        .HasMany(s => s.PageHits)
        .WithOne(ph => ph.Session)
        .OnDelete(DeleteBehavior.NoAction);

    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

wwwroot Folder

Entities Folder

Pages Folder

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:

app.UseCookiePolicy();
app.UseAnalytics();

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

Startup > Configure:

app.UseStatusCodePagesWithReExecute("/Error/{0}");

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

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.