Error when using SQL Server Memory Optimized Caching Provider Cache provider in Sql Server 2019

ServiceStack.OrmLite SQL Server
1

I get the following error when trying to use the SQL Server Memory Optimized Cache provider:
Column ‘Id’ in table ‘SqlServerMemoryOptimizedCacheEntry’ is of a type that is invalid for use as a key column in an index.
Could not create constraint or index. See previous errors.

2

Hi @bgiromini,

There isn’t much info to work with here. Are you able to provide a full stack trace and related classes so we can see info like what type the Id property is? As well as the schema of the related table?

A quick google search seems to indicate it might be an issue with the schema used is hitting a limitation with your version of SQL Server, maybe if the Id field is TEXT as the primary key. This is just a guess based on the limited info here, but might be worth looking into.

The more context you can provide the more others will be able to help with your specific issue.

Hope that helps.

3

From my understanding, the cache provider is the one dictating the scheme and type of the class, all I did was try to use the feature in my code by initializing the OrmliteCacheClient.

Here is the schema from OrmCacheClient.cs

public class SqlServerMemoryOptimizedCacheEntry : ICacheEntry
    {
        [PrimaryKey]
        [StringLength(StringLengthAttribute.MaxText)]
        [SqlServerBucketCount(1000000)]
        public string Id { get; set; }
        [StringLength(StringLengthAttribute.MaxText)]
        public string Data { get; set; }
        public DateTime CreatedDate { get; set; }
        [Index]
        public DateTime? ExpiryDate { get; set; }
        public DateTime ModifiedDate { get; set; }
    }

From my guess is sql server doesn’t support this schema anymore.

4

Seems there may be a limit to the key length, can you try using a definition without a text id column, i.e:

[SqlServerMemoryOptimized(SqlServerDurability.SchemaOnly)]
public class SqlServerMemoryOptimizedCacheEntry : ICacheEntry
{
    [PrimaryKey]
    // [StringLength(StringLengthAttribute.MaxText)]
    [SqlServerBucketCount(1000000)]
    public string Id { get; set; }
    [StringLength(StringLengthAttribute.MaxText)]
    public string Data { get; set; }
    public DateTime CreatedDate { get; set; }
    [Index]
    public DateTime? ExpiryDate { get; set; }
    public DateTime ModifiedDate { get; set; }
}