Entity Framework sql_variant as primary key

Question

For a project I used EF Core 5.0 to scaffold an existing database.

The database has some tables with sql_variant columns as a part of the primary key.

The model created the property as an 'object'.

Base OnModelCreating of this property:

  entity.Property(e => e.OperationNo)
    .HasColumnType("sql_variant")
    .HasColumnName("Operation No_");

When I try to test this auto scaffold I get the error message:

'Property 'ProdOrderRoutingLine.OperationNo' cannot be used as a key because it has type 'object' which does not implement 'IComparable', 'IComparable' or 'IStructuralComparable'. Use 'HasConversion' in 'OnModelCreating' to wrap 'object' with a type that can be compared.'

So I have tried a lot of conversion sql_variant to string or object or ... with comparer. To make it simple I'll try to convert it to a string which has a default comparer.

 entity.Property(e => e.OperationNo)
   .HasColumnType("sql_variant")
   .HasColumnName("Operation No_")
   .HasConversion<string>(str => str.ToString(), sql => sql.ToString());

Here I changed my property to string if I leave it as an object I get the same error:

'The property 'ProdOrderRoutingLine.OperationNo' is of type 'string' which is not supported by the current database provider. Either change the property CLR type, or ignore the property using the '[NotMapped]' attribute or by using 'EntityTypeBuilder.Ignore' in 'OnModelCreating'

Any ideas how to overcome this problem and to use the existing sql_variant as a partial primary key?

OnModelCreate primary key:

entity.HasKey(e => new { e.Status, e.ProdOrderNo, e.RoutingReferenceNo, e.RoutingNo, e.OperationNo });

part of the Model:

public partial class ProdOrderRoutingLine
{
    public byte[] Timestamp { get; set; }
    public int Status { get; set; }
    public string ProdOrderNo { get; set; }
    public int RoutingReferenceNo { get; set; }
    public string RoutingNo { get; set; }
    public object OperationNo { get; set; }
    public string NextOperationNo { get; set; }
    public string PreviousOperationNo { get; set; }
    public int Type { get; set; }
    public string No { get; set; }
}

Answer 1

To make the conversions work i had to comment out:

.HasColumnType("sql_variant")