Reading sql_variant in Entity Framework

• Download source - 1.53 KB

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.

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

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.