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 skipped this column:

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

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

Answer 1

EF 4 does not have built-in support for sql_variant type. This article explains how to do reading by matching objects with user queries, fixing the type and value separately, and then decoding the value manually in its code.

Unfortunately, this solution cannot be adapted to write data back. You can try inserting / updating / deleting stored procedures , but I can’t tell you that it will work because I never tried it.

Answer 2

It's a bit late for the party, but there is still another way to do this, you can create a separate LINQ table class for each type option that you store in the database table. This allows you to perform insert operations. eg,

 [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; } }; 

Then these classes let you insert the traditional LINQ-to-SQL method. I also set the typeID during insertion to a unique value that is useful for re-selecting data of the appropriate type with the LINQ where clause.

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

This method does cause a bit of duplication, but still a pretty neat method to achieve this, with fewer flaws than the other methods proposed.

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