How do you properly handle SQL_VARIANT in Entity Framework Core?

Question

It looks like support has recently been added to Entity Framework Core in .NET Core 2.1 (preview) to allow the mapping of SQL_VARIANT columns (https://github.com/aspnet/EntityFrameworkCore/issues/7043).

It looks like the way to go about doing this is using the new HasConversion() method (https://learn.microsoft.com/en-us/ef/core/modeling/value-conversions).

So, in order to map my SQL_VARIANT column and treat the underlying datatype as being a VARCHAR of any length (I only care about reading it at this point), I can do the following (where the Value property here is of type object in the model):

entity.Property(e => e.Value).HasConversion(v => v.ToString(),
                                                            v => v.ToString());

This works, if the SQL_VARIANT's underlying datatype is a VARCHAR of any length.

However, being a SQL_VARIANT, the column could contain data of other types, such as DATETIME values.

For simplicity I've only specified DateTime and string here, but in theory I'd probably want to support the datatypes necessary to map whatever could be stored in a SQL_VARIANT column, if possible.

How would I go about determining which one of those two types (string and DateTime) I'd want to map to at runtime? Is there a way to do this?

Answer 1

As I understand, the way to go at the moment is just:

// where e.Value is property of type "object"
entity.Property(e => e.Value).HasColumnType("sql_variant");

And that's all, no need for any custom converters. As pull message adding this feature states:

The type mapper will now map properties to sql_variant columns if:

• The property is type object
• The store type name is specified as sql_variant

You current code satisfies first condition (property is of type object), but does not satisfy second one. Why exactly it cannot infer store type name from property being of type object - I'm not really sure, maybe just because Entity Framework is not sql-server specific and supports many other databases, in which object property can have different semantics or not supported at all, so it requires to state your intentions explicitly.