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://docs.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
For simplicity I've only specified
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 (
DateTime) I'd want to map to at runtime? Is there a way to do this?
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.