Click here to Skip to main content
13,409,720 members (35,708 online)
Click here to Skip to main content
Add your own
alternative version


9 bookmarked
Posted 18 Dec 2010

Reading sql_variant in Entity Framework

, 8 Jan 2011
Rate this:
Please Sign up or sign in to vote.
Working around an Entity Framework exclusion of sql_variant


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.


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.

  1. 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.
  2. 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.
  3. 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] and
    sql_variant_property([mytable].[mycolumn], 'BaseType') as [BaseTypeMyColumn].
  4. Replace Schema="dbo" attribute of the "mytable" EntitySet with store:Name="mytable"
  5. 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.
  6. Switch back to EDM editor, and map SqlVariant properties as follows: BaseType maps to BaseTypeMyColumn; Representation maps to RepMyColumn.
  7. 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.


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.

public object Converted {
    get {
        if (Representation == null || BaseType == null) {
            return null;
        switch (BaseType) {
            case "uniqueidentifier":
                return new Guid(Representation);
            case "char":
            case "varchar":
                return GetString(Representation);
            case "nvarchar":
            case "nchar": 
                return GetNlString(Representation);
            // More cases covering other base types
        throw new InvalidOperationException("Unsupported SQL type: '" + BaseType + "'");

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:

private static string GetNlString(byte[] src) {
    if (src.Length % 2 != 0) {
        throw new InvalidOperationException("NLS format is invalid.");
    var buf = new char[src.Length / 2];
    for (var pos = 0 ; pos != src.Length ; pos += 2) {
        buf[pos/2] = BitConverter.ToChar(src, pos);
    return new string(buf);

Using the Code

You can now read the data from your SqlVariant columns by accessing the Converted property of the complex properties you added in step 2:

foreach (var x in myEdm.myTableWithSqlVariantDatas) {
    var obj = x.MySqlVariantColumn.Converted;


  • 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.


This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


About the Author

United States United States
No Biography provided

You may also be interested in...


Comments and Discussions

GeneralWriteable property Pin
Member 8401846-Feb-11 23:49
memberMember 8401846-Feb-11 23:49 
GeneralRe: Writeable property Pin
dasblinkenlight7-Feb-11 4:33
memberdasblinkenlight7-Feb-11 4:33 
Unfortunately, I was unable to figure out a trick to make sql_variant writable, or for that matter - even to make it server-side queryable. In a few instances when we needed to filter on the value of sql_variant, we used ToList to force intermediate results into memory. But regardless of all these shortcomings, the solution proved to be a big win for the team: it bought us time till the EF team comes through with native support of sql_variant.
GeneralRe: Writeable property -> USE LINQ-TO-SQL Classes Pin
Member 865203114-Mar-13 3:47
memberMember 865203114-Mar-13 3:47 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Permalink | Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.180221.1 | Last Updated 8 Jan 2011
Article Copyright 2010 by dasblinkenlight
Everything else Copyright © CodeProject, 1999-2018
Layout: fixed | fluid