Custom Entity Id Data Type Fails on Inserts

141 Views Asked by At

I'm trying to use sequential INT ids in my data layer and use the Hashids.NET library to give me YouTube-like 'hashed' ids in my server code and UI layers. I got it to work by creating the custom struct shown below which resides in memory as an Int32 and prints to string as a hashed id exactly as I want. I then automatically create a ValueConverter in OnModelCreating any time any data column property uses this data type.

This ends up working flawlessly with querying objects, includes, foreign keys, etc. However, only upon trying to insert new objects, I get the error also shown below. My goal is to get Entity Framework to ignore the HashIdentifier value on inserts if its value is default. The same way it would ignore an int if the value is 0. I want the database to be generating the new keys with the IDENTITY I set there, and then Entity Framework to use the ValueConverter I have set up to use the new values in the form of my HashIdentifier, which already works in every other case other than inserts.

public partial struct HashIdentifier : IEquatable<HashIdentifier>, IEquatable<int>, IComparable<HashIdentifier>, IComparable<int>
{
    private static Hashids _hashids = new("seed", 11);

    private int _value;

    public int Value => _value;

    public HashIdentifier(in int value)
    {
        _value = value;
    }

    public override string ToString()
    {
        return _hashids.Encode(Value);
    }

    public static HashIdentifier Parse(in string value)
    {
        if (TryParse(value, out HashIdentifier _result))
            return _result;
        throw new InvalidIdentifierException(value);
    }

    public static bool TryParse(in string value, out HashIdentifier result)
    {
        int[] _decode = _hashids.Decode(value);

        if (_decode.Length == 0)
        {
            result = default;
            return false;
        }
        result = new(_decode[0]);
        return true;
    }

    public int CompareTo(HashIdentifier other)
    {
        return CompareTo(other.Value);
    }

    public int CompareTo(int other)
    {
        return Value.CompareTo(other);
    }

    public override bool Equals(object obj)
    {
        switch (obj)
        {
            case int _a: return Equals(_a);
            case HashIdentifier _a: return Equals(_a);
        }
        return false;
    }

    public bool Equals(int other)
    {
        return other == Value;
    }

    public bool Equals(HashIdentifier other)
    {
        return other.Value == Value;
    }

    public static bool operator ==(int left, HashIdentifier right)
    {
        return right.Equals(left);
    }

    public static bool operator !=(int left, HashIdentifier right)
    {
        return !(left == right);
    }

    public static explicit operator int(HashIdentifier value)
    {
        return value.Value;
    }

    public static explicit operator HashIdentifier(int value)
    {
        return new(value);
    }
}

The error I get on inserts:

Cannot insert explicit value for identity column in table 'TestTable' when IDENTITY_INSERT is set to OFF

The value of the HashIdentifier at the time of the insert was effectively 0. And it does call into the ValueConverter right before hitting the exception. So why does it think I am setting an explicit value?

This is not a duplicate

I've already searched the internet and StackOverflow for solutions and I haven't found one. I am not looking for a way to use IDENTITY_INSERT. Please understand my issue carefully. I want Entity Framework to work as it did before I switched my keys to use custom structs.

My table schema is as follows:

CREATE TABLE [dbo].[TestTable]
(
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Sequence] [int] NULL,
    [CreatedDate] [datetime] NOT NULL

    CONSTRAINT [PK_TestTable] 
        PRIMARY KEY CLUSTERED ([Id] ASC)
                WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, 
                      IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, 
                      ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

Update

This appears to be a known issue according to the Microsoft Documentation

Currently key properties with conversions cannot use generated key values. Vote for GitHub issue #11597 to have this limitation removed.

0

There are 0 best solutions below