Hi all, We have set up Distributed SQL Server Cache by following this article https://learn.microsoft.com/en-us/aspnet/core/performance/caching/distributed?view=aspnetcore-7.0. Currently we are running the following create table script manually on the database:

CREATE TABLE [dbo].DistCache
(
    [Id] NVARCHAR(900) NOT NULL PRIMARY KEY, 
    [Value] VARBINARY(MAX) NOT NULL, 
    [ExpiresAtTime] DATETIMEOFFSET NOT NULL, 
    [SlidingExpirationInSeconds] BIGINT NULL, 
    [AbsoluteExpiration] DATETIMEOFFSET NULL
)

I wanted to do this programmatically on application start. I tried to follow this article here https://docs.umbraco.com/umbraco-cms/v/10.latest-lts/extending/database. Here is my Disc Cache Schema

[TableName("dbo.DistCache")]
        [PrimaryKey("Id")]
        [ExplicitColumns]
        public class DistCacheSchema
        {
            [PrimaryKeyColumn()]
            [Column("Id")]
            public String Id { get; set; }

            [Column("Value")]
            public Byte[] Value { get; set; }

            [Column("ExpiresAtTime")]
            public DateTimeOffset ExpiresAtTime { get; set; }

            [Column("SlidingExpirationInSeconds")]
            public Nullable<Int64> SlidingExpirationInSeconds { get; set; }

            [Column("AbsoluteExpiration")]
            public Nullable<DateTimeOffset> AbsoluteExpiration { get; set; }
        }

This fails with the following error message

Microsoft.Data.SqlClient.SqlException: 'Identity column 'Id' must be of data type int, bigint, smallint, tinyint, or decimal or numeric with a scale of 0, unencrypted, and constrained to be nonnullable.'

Any ideas how to get this work?

The

NVARCHAR

is not a valid type for the identity column. You can change it to int?

ALTER TABLE [dbo].DistCache
ALTER COLUMN [Id] INT IDENTITY(1,1);

The sql script works fine. The issue is with the code that im using to create the table programmatically.

Yep - it can't be that type. If you want to create it programatically, you could probably try and set the PrimaryKey to be a pseudo and leave Id as-is but no the priumary. Something like:

[PrimaryKeyColumn(AutoIncrement = true, IdentitySeed = 1)]
[Column("PrimaryKey")]
public int PrimaryKey { get; set; }

Npoco doesnt support that type. Since you actually wont be using it with NPoco, Id suggest switching to just running a "script" in a migration