Distributed SQL Server Cache - Create SQL table programmatically

[glacial lake]

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:

(
    [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?

[thin sundial]

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

But - I think that The dist cache uses the ID as the key so you can add crazy good stuff instead of sequential, have you tried also removing the primary key attribute?

[glacial lake]

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

[thin sundial]

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; }

[vagrant garden]

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