Introduction

Although Entity Framework does not have built-in support for the sql_variant data type [1][2] of SQL Server, it is possible to work around this limitation. This article describes an approach that lets you read columns of type sql_variant using an Entity Framework EDM.

Background

We needed a read-only access to a database that makes relatively heavy use of sql_variant in an application that uses Entity Framework. Unfortunately, we quickly discovered that there is no built-in support for columns of this data type: all sql_variant columns were excluded from the wizard-generated EDM with a nasty warning. Fortunately, Entity Framework supplies two features that can be combined to read sql_variant: Complex Types and DefiningQuery. The implementation converts sql_variant to varbinary, and converts binary representations to object using a long but straightforward set of rules.

Adding sql_variant to your Model

The steps below assume that you are using Visual Studio, that your database tables exist, and that you have an imported EDM file with only sql_variant columns missing. These steps also assume basic familiarity with the Entity Framework concepts and tools.

In Model Browser, add a new Complex Type called SqlVariant with two properties - a private string property called BaseType, and a private Binary property called Representation.

In EDM Editor, add a read-only Complex Property of type SqlVariant for each sql_variant field the EDM wizard skipped. Name the properties according to the naming conventions you adopted to match the names of their corresponding columns in the database.

Open your EDM file in XML editor, and add a DefiningQuery tag to each entity set on tables containing sql_variant columns. In addition to listing all "regular" columns of your table, the query needs to include a pair of expressions for each sql_variant column:cast([mytable].[mycolumn] as varbinary) as [RepMyColumn] andsql_variant_property([mytable].[mycolumn], 'BaseType') as [BaseTypeMyColumn].

Replace Schema="dbo" attribute of the "mytable" EntitySet with store:Name="mytable"

Add two columns for each sql_variant column to each entity. Call them RepMyColumn and BaseTypeMyColumn, where MyColumn is the name of your sql_variant column.

Switch back to EDM editor, and map SqlVariant properties as follows: BaseType maps to BaseTypeMyColumn; Representation maps to RepMyColumn.

Download the source file attached to this article, and change its namespace to match that of your project. This adds a property of type object called 'Converted' to the SqlVariant type. Use this column to access the value of your sql_variant.

Implementation

High-level description of the implementation fits in a single sentence: it's a switch statement on the base type of sql_variant, followed by constructing .NET objects from binary representations.

Considering that the implementation supplied with the article supports fifteen base types, it's not surprising that there's a lot of details coded into the individual converters. However, all converters follow the same basic pattern: first, they check the array of bytes to be of the expected length, then they use BitConverter to construct elements of the result, and finally construct the result. The code converting nvarchar strings provides a good example of how the individual converters work:

Limitations

The code lacks support for some SQL Server base types. However, you can add types that are of interest to you by following the general pattern of the implementation.

Since this implementation provides only a read access to sql_variant columns, you can neither write to these columns, nor run DB queries against their values.

The binary format the implementation "understands" is highly specific to SQL Server 2008, and therefore may stop working against future versions of the database. However, I hope that the Entity Framework team adds native support for sql_variant before SQL Server team releases a binary-incompatible version of their server

It goes without saying that the code supplied with this article may have bugs. It is provided as a starting point to your implementation, not as a drop-in library.

Share

About the Author

Comments and Discussions

I disagree with Mark on this. Yes, it's short, but I think there's enough in it to warrant it being an article. I'll award it a 5 just because it's the first solution to this problem that I've seen. Good job.

Thanks for the comment. It would have been a lot more valuable, though, if you could perhaps provide some specifics on what's missing to make it an article IYO. IMO, short subjects invite short explanations, and this is definitely a short subject. As far as I can see, this article is the only one out there that goes beyond the "you need another ORM framework" treatment of the subject.

It lacks the overall substance of other, more successful, articles here. There should be more in-depth discussion and more than a single incomplete code snippet. Do some research and compare this attempt to other articles here and blog entries. Again, IMO, it is a blog entry at best, not an article.

That's an excellent question. The reason anyone would care to look at the code is that it fulfills a very specific need not addressed anywhere else, at least at this moment. I was unable to find a solution to this problem even after researching the English- and Russian-speaking segments of the internet for weeks.
In spite of its seeming simplicity, my solution is not trivial to code. I decided to share it as a way of giving back to the community from which I borrowed code examples on more than one occasion.

I thought your article was more than good enough to be here. It explained the background (WHY did you do this), and then it proposed a solution that met your needs. The source code is the majority of the explanation, but I don't see anything wrong with that approach.

I think you did a fine job, keep posting. Don't let the trolls get you down.