12

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?

Interminable
  • 1,338
  • 3
  • 21
  • 52
  • You don't need to use any conversions. Just map property of type `object` in your model to column of type `sql_variant` in database, that's all. Then if you read column and it's sql datetime - your `object` model property will be of type `System.DateTime`. If it was decimal on sql server side - it will be `System.Decimal` in .NET, and so on. – Evk Apr 09 '18 at 07:00
  • That does sound like the ideal scenario (and how I thought it would be to begin with), however when generating the Model, the `SQL_VARIANT` column was not detected, so I had to add it manually to the model (with a type of `object`), and also to `OnModelCreating()`, using `entity.Property(e => e.Value);`. Unfortunately, this does not work, and throws an `InvalidOperationException` with the message `'No mapping to a relational type can be found for the CLR type 'object'.` – Interminable Apr 09 '18 at 08:03
  • I tested it and it's working for me (with last EF 2.1 preview). I did it like this: `.Property(e => e.Value).HasColumnType("sql_variant");`. Maybe you didn't add that `HasColumnType`? – Evk Apr 09 '18 at 08:08
  • @Evk I think you should compose an answer from your comments. Probably at some later point the provider will be updated to setup the column type automatically, but for now this should be the way to go. – Ivan Stoev Apr 09 '18 at 09:14
  • @Evk Wow, that did it! Could you put that as your answer and explain why this is so? And where I can perhaps read up more on this? I confess that I am rather new to Entity Framework. EF Core is my first foray into all this. I didn't realise I had to manually tell it what the column type was as I thought it was detected automatically. – Interminable Apr 09 '18 at 09:16
  • Although according to this [commit](https://github.com/aspnet/EntityFrameworkCore/commit/641b571868614821eda8740887044164e0d22822) it should already be there. Probably not included in the preview, or `HasConversion` is breaking it up, who knows - beta software risks :) – Ivan Stoev Apr 09 '18 at 09:21
  • @IvanStoev but doesn't that commit explictly states that for that to work "The store type name is specified as sql_variant" condition should be satisfied? – Evk Apr 09 '18 at 09:24
  • @Evk May be. Although to me property type `object` should be enough :) And if the database provider does not support it, throw `NotSupportedException` or something. With the lack of any documentation, all we can do is to guess. – Ivan Stoev Apr 09 '18 at 09:30

1 Answers1

11

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.

Evk
  • 98,527
  • 8
  • 141
  • 191
  • Do you know if there's any good documentation available for Entity Framework Core on where `HasColumnType()` is and is not needed? I have noticed that for the `DbContext` file it generated, some fields (such as `DateTime` columns) have automatically been created with `HasColumnType()` calls, but other fields (such as `string` ones) have not. – Interminable Apr 09 '18 at 09:43
  • @Interminable all I can find is this link: https://learn.microsoft.com/en-us/ef/core/modeling/relational/data-types. It basically says that "database provider selects a data type based on the CLR type of the property", so it's up to provider to decide how to map given property. So when generating dbcontext from database - I guess provider uses explicit `HasColumnType` where real column type does not match what it would otherwise use by default (say by default for DateTime it would use datetime2(7), but your column in database is say smalldatetime), and otherwise would omit that call. – Evk Apr 09 '18 at 09:49