ASP.NET Core session with MySQL

Question

I want to customize the session provider in asp.net core. i.e. the custom session provider will use the MYSQL database instead of SQL Server I am seeing that asp.net core gives us the built-in method AddDistributedSqlServerCache. but it seems like this method is only for SQL server.

Can anyone help me regarding this?

Answer 1

I have below working implementation in .NET 5 or .NET 6 (also stands good for .NET Core)

Add nuget package Pomelo.Extensions.Caching.MySql

At the top of ConfigureServices in Startup.cs

    services.AddDistributedMySqlCache(options =>
    {
        options.ConnectionString = <MySQL CnStr with: Allow User Variables=true; >;
        options.TableName = "WebUserSessions";
        options.SchemaName = <MySQL Database Name>;
    });
    services.AddSession(options =>
    {
        options.IdleTimeout = TimeSpan.FromMinutes(180);
        options.Cookie.IsEssential = true;
        options.Cookie.HttpOnly = true;
        options.Cookie.SecurePolicy = CookieSecurePolicy.SameAsRequest;
        options.Cookie.SameSite = SameSiteMode.Strict;
    });

In Configure method app.UseSession();

My SQL Table creation script:

CREATE TABLE `webusersessions` (
  `Id` varchar(449) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
  `AbsoluteExpiration` datetime(6) DEFAULT NULL,
  `ExpiresAtTime` datetime(6) NOT NULL,
  `SlidingExpirationInSeconds` bigint DEFAULT NULL,
  `Value` longblob NOT NULL,
  PRIMARY KEY (`Id`),
  KEY `Index_ExpiresAtTime` (`ExpiresAtTime`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

Now your session values are stored in MySQL Table 'WebUserSessions', thus you can serve your client from multiple servers using single mysql database for storing the sessions. In case you have only one server, your user's session would be preserved on IIS Application pool restart or Linux service restart.

To update .NET core or .NET 6 application without any down time, please refer to my answer: https://stackoverflow.com/a/66580629/9659885

Answer 2

Try this NuGet package instead....

Pomelo.Extensions.Caching.MySql

Or this one for Postgresql....

Community.Microsoft.Extensions.Caching.PostgreSQL