Convert a column data type on the fly with LINQ To SQL

I got an email yesterday from a reader with this LINQ To SQL problem. 

He was working with an existing database and some of the columns in his database weren’t the same type as what he wanted to use in his C# classes.  The database column is a string but in the LINQ To SQL entity, they wanted the C# property type to be a long.  When they tried to do it, they got all kinds of errors saying that the type can’t be converted on the fly. 

The solution is to map the column to a private member variable that is the same type as in the database and then provide a public property that wraps that member variable and exposes it as the proper, required type.  Essentially, you’re solving the problem by not solving the problem.

Here’s what their database table looks like.  Notice that Longitude and Latitude is stored as an NVARCHAR(4000).

Here’s the class that they wanted to use with LINQ to SQL.  Notice that the Latitude and Longitude properties are of type long.

Here’s the solution.  Add a wrapper property with a private member variable. 

[Column(Name = “Latitude”, Storage = “m_latitudeAsString”)]
private string m_latitudeAsString;

public long Latitude
{
    get
    {
        return Int64.Parse(m_latitudeAsString);
    }
    set
    {
        m_latitudeAsString = value.ToString();
    }
}

The key piece here is the [Column] attribute
[Column(Name = “Longitude”, Storage = “m_longitudeAsString”)]
and the column’s Storage value.  The Column’s Storage value allows you to tell LINQ to SQL to store/retrieve the mapped value from a variable rather than the public property.  So, what we’re doing here is using our public Latitude property to handle the data-type conversion logic and wrap access to m_latitudeAsString.  The public Latitude property actually isn’t mapped to the database. 

Here’s a link to download the sample code.  In order to make the unit test run, you’ll need to edit the connection string in the app.config in the unit test project.  You don’t have to create the database schema yourself, you just have to make sure that the database referenced in the connection string exists in your SQL Server. 

-Ben