Using sql_variant with Entity Framework 4

Question

My use case is pretty simple. I have a small table with a column like this:

dtgTestValue sql_variant not null

I created an Entity Framework model (.edmx file), and it skipped over that column saying:

The data type 'sql_variant' is not supported; the column 'dtgTestValue' in table 'databaseFoo.dbo.Foobar' was excluded.

I need to be able to read and write values into this particular column. Any suggestions on how to approach this fairly simple use case?

Here's an [article](http://www.codeproject.com/Articles/137509/Reading-sql_variant-in-Entity-Framework) that might help. — The Jonas Persson, Jun 26 '12 at 02:54
@TheJonasPersson: Found that article during my search, but it seems it's `read-only`. I need to be able to read and write data to that column. — Only Bolivian Here, Jun 26 '12 at 02:57

score 4 · Answer 1 · answered Jun 26 '12 at 02:56

EF 4 does not have built-in support for sql_variant type. This article explains how to do reading by mapping entities to custom queries, capturing the type and value separately, and then decoding the value manually in your code.

Unfortunately, this solution cannot be adapted for writing the data back. You can try mapping inserts/updates/deletes to stored procedures, but I cannot tell you for sure that it is going to work, because I never tried it.

score 2 · Answer 2 · edited Sep 18 '13 at 14:33

A bit late to the party, but still there is another way of doing this, it's possible to create a separate LINQ table class per variant "type" that you're storing in the database table. This allows you to perform your insert operations. for example;

[Table(Name = "tVariant")]
public class tVariantDouble
{
    [Column(IsPrimaryKey = true, IsDbGenerated = true)]
    public int variantID { get; set; }

    [Column(CanBeNull = true)]
    public double? myVariant { get; set; }

    [Column(CanBeNull = true)]
    public string name { get; set; }

    [Column(CanBeNull = false)]
    public int typeID { get; set; }

    public tVariantDouble() { typeID = 1; }
};

[Table(Name = "tVariant")]
public class tVariantInteger
{
    [Column(IsPrimaryKey = true, IsDbGenerated = true)]
    public int variantID { get; set; }

    [Column(CanBeNull = true)]
    public int? myVariant { get; set; }

    [Column(CanBeNull = true)]
    public string name { get; set; }

    [Column(CanBeNull = false)]
    public int typeID { get; set; }

    public tVariantInteger() { typeID = 2; }
};

These classes then allow you to insert in the traditional LINQ-to-SQL method. I also set the typeID during the insert to an unique value, which is useful for selecting the data of the appropriate type again, with a LINQ where statement.

I would suggest creating a base template class for the same table, however LINQ is clunky at best with inheritance and it simply won't work.

This method does cause a little bit of duplication, but still quite a neat method of accomplishing this, with less drawbacks than the other suggested methods.

If you wanted to select all the data from the table, regardless of variant type, I would suggest using the union operator with a separate intermediate class to join all the results up.

I don't understand a single word. Any example perhaps? – ViRuSTriNiTy Feb 09 '17 at 17:47 — ViRuSTriNiTy, Feb 09 '17 at 17:47

Using sql_variant with Entity Framework 4

2 Answers2