Click here to Skip to main content
15,861,168 members
Articles / Database Development / SQL Server / SQL Server 2008

Reading sql_variant in Entity Framework

Rate me:
Please Sign up or sign in to vote.
4.89/5 (10 votes)
8 Jan 2011CPOL3 min read 84.2K   819   9   22
Working around an Entity Framework exclusion of sql_variant

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.

  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.

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.

C#
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:

C#
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:

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

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.

License

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


Written By
United States United States
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

 
Questionmap SqlVariant properties Pin
Member 1392623526-Jul-18 0:13
Member 1392623526-Jul-18 0:13 
SuggestionPlease update it with examples Pin
DionnV18-Mar-15 1:03
DionnV18-Mar-15 1:03 
QuestionThanks (and a usage comment) Pin
David Killian23-Oct-14 3:31
professionalDavid Killian23-Oct-14 3:31 
QuestionNeed a bit of help Pin
EmissaryScott29-Sep-14 9:36
EmissaryScott29-Sep-14 9:36 
AnswerRe: Need a bit of help Pin
dasblinkenlight29-Sep-14 11:11
dasblinkenlight29-Sep-14 11:11 
QuestionSQL_VARCHAR Pin
Fregate26-Jul-14 19:47
Fregate26-Jul-14 19:47 
QuestionHow do I add columns to Entity Pin
Vu Jane16-Mar-13 6:53
Vu Jane16-Mar-13 6:53 
AnswerRe: How do I add columns to Entity Pin
Tod Palin18-Jun-15 2:54
Tod Palin18-Jun-15 2:54 
GeneralMy vote of 5 Pin
Michael Bakker8-Dec-11 6:46
Michael Bakker8-Dec-11 6:46 
GeneralMy vote of 5 Pin
Gonzalo Brusella19-Apr-11 7:59
Gonzalo Brusella19-Apr-11 7:59 
GeneralWriteable property Pin
Benyke6-Feb-11 22:49
Benyke6-Feb-11 22:49 
GeneralRe: Writeable property Pin
dasblinkenlight7-Feb-11 3:33
dasblinkenlight7-Feb-11 3:33 
GeneralRe: Writeable property -> USE LINQ-TO-SQL Classes Pin
Tommi V. Gustafsson14-Mar-13 2:47
Tommi V. Gustafsson14-Mar-13 2:47 
GeneralMy vote of 5 Pin
Pete O'Hanlon9-Jan-11 23:41
subeditorPete O'Hanlon9-Jan-11 23:41 
GeneralI'll throw it a 5 Pin
vbfengshui20-Dec-10 7:31
vbfengshui20-Dec-10 7:31 
GeneralA blog entry, not an article Pin
Not Active19-Dec-10 4:23
mentorNot Active19-Dec-10 4:23 
GeneralRe: A blog entry, not an article Pin
dasblinkenlight19-Dec-10 5:03
dasblinkenlight19-Dec-10 5:03 
GeneralRe: A blog entry, not an article Pin
Not Active19-Dec-10 5:15
mentorNot Active19-Dec-10 5:15 
GeneralRe: A blog entry, not an article Pin
dasblinkenlight19-Dec-10 5:29
dasblinkenlight19-Dec-10 5:29 
GeneralRe: A blog entry, not an article Pin
Not Active19-Dec-10 6:04
mentorNot Active19-Dec-10 6:04 
GeneralRe: A blog entry, not an article Pin
dasblinkenlight19-Dec-10 10:13
dasblinkenlight19-Dec-10 10:13 
GeneralRe: A blog entry, not an article Pin
JasonShort20-Apr-11 8:45
JasonShort20-Apr-11 8:45 

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.