How to map SQL_VARIANT in Entity Framework 6?

Question

I'm using Entity framework 6 database first. There was already designed database and I need to use it to create an API. In severl tables [sql_variant] was used as a column type. When I added entity framework, all [sql_variant] columns were ignored with warning informing me that there is no mapping for this type!

I tried to manually add the [sql_variant] column with object type and map it. But I'm getting its value always null!

//The name of my [sql_variant] column is Value.
[Column("Value", TypeName = "sql_variant")]
[Required]
public object Value { get; set; }


//In the context class I tried to add also the following.
protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
 modelBuilder.Entity<MeasurementData>();
 modelBuilder.Properties<MeasurementData>();
}

I'm still getting the following exception:

System.NotSupportedException
  HResult=0x80131515
  Message=The specified type member 'Value' is not supported in LINQ to Entities. Only initializers, entity members, and entity navigation properties are supported.
  Source=<Cannot evaluate the exception source>
  StackTrace:
<Cannot evaluate the exception stack trace>

Answer 1

I just had the same requirement. The table that I have has a single sql_variant field. What I did was I mapped the sql_variant column to an int C# field using the knowledge that the criteria I use to query some specific data in this table returns int values in the sql_variant column.

Let's say the table is called EntityPropertyValue with a sql_variant column called PropertyValue.

Then you can have several EF C# classes, EntityIntPropertyValue, EntityStringPropertyValue etc. where PropertyValue in EntityIntPropertyValue is declared:

[Column("PropertyValue")]
public int PropertyValue {get; set;}

In EntityStringPropertyValue, you have:

[Column("PropertyValue")]
public String PropertyValue {get; set;}

The where clauses I use in the code do not reference this field. I expect that if you do reference the field in your code, the sql statement generated by EF might fail during conversions due to invalid conversions especially varchar -> int. I haven't tried it.

I know, it is a bit of more work but it works in my context. Inserting rows and populating the column with int values works.