How do I map custom types in Linq to Sql?

Question

I have a Customer class that contains a property, MyProperty, which is of a custom type MyCustomType. I want to persist the property value in the database as text. In the designer I've set the Type to 'MyType' and the Server Data Type to 'varchar(10)'. When I build the project I get the following error:

DBML1005: Mapping between DbType 'varchar(10)' and Type 'MyType' in 
Column 'MyProperty' of Type 'Customer' is not supported.

Now that make sense, as Linq to Sql has no way of knowing how to convert my custom type. So I'm assuming I have to implement some kind of Parse(string) and ToString() methods on MyCustomType however I can't find any documentation on this.

So, how do I map custom types in Linq to Sql?

Answer 1

Correct approach (from MSDN) is:

If a class implements Parse() and ToString(), you can map the object to any SQL text type (CHAR, NCHAR, VARCHAR, NVARCHAR, TEXT, NTEXT, XML). The object is stored in the database by sending the value returned by ToString() to the mapped database column. The object is reconstructed by invoking Parse() on the string returned by the database.

Answer 2

Expanding on the private/public data access pattern above I did the following.

1) Select the DB data type to which the custom type will be mapped. In my case the custom object implemented the type-safe enumeration pattern so there is a natural mapping from the custom type (an enumeration of constants) and the DB types integral type (tinyint in my case). I can see the underlying DB type as string and then using JavaScriptSerializer Class for step 2.

2) Implement in your class the ability to convert between the custom type and the DB data type. The conversion have to be bi-directional: CustomObject --> DB type and DBType --> CustomObject For me it was implementing:

2a) The ordinal value of the enumerated type as a member of my type-safe enumeration class. This handles the casting from my custom type to int.

2b) A static member, findByOrdinal(), which handles the cast from int to my custom type.

3) Use Linq To SQL to create the link between the data column, this custom data type, and a public name. This is done by pasting in the name of the custom object into the Type combo box of the data member property.

4) Clean, and rebuild the project to insure the compile is clean. If the build process complains that the custom type does not exist within the context of <ProjectName>.designer.cs file found beneath the <ProjectName>.dbml file. Th regeneration issues are discussed below. If you execute the code in the DB context you will get the casting error above.

Linq to SQL has created a number of partial class files when it created the .designer.cs file. We will need to add to this partial class code which will not be destroyed when Linq to SQL regenerates the code in <ProjectName>.designer.cs.

5) Create a new class file which contains source code to the partial classes created by Linq to SQL. To do this, right click on <ProjectName>.dbml in Solution Explore and select the View Code from the context menu. This will create a new file, <ProjectName>.cs, within the project which continues the definition of the partial classes found in <ProjectName>.designer.cs

6) Cut selected code from <ProjectName>.designer.cs and paste the code to <ProjectName>.cs

Link to SQL has created within <ProjectName>.designer.cs a partial class that maps a table to a class.

We need to remove the data access code found <ProjectName>.designer.cs and create our own version of this data access within <ProjectName>.cs.

I did it by doing a straight cut and paste of the Linq-generated code and mutating the code to my puposes once the code was in <ProjectName>.cs. The cut, paste, mutate operations were:

6a) Change the data storage type from MyCustomType to something that maps easily to the DB data type for that column in the database. In my case I selected int as the internal data store for tinyint coming in from the the DB column.

6b) Alter the getter and setter methods of the publicly named data member created by Linq to SQL.

See code below for details on the original Linq code and the final, mutated version.

7) Remove from <ProjectName>.designer.cs the using statment added in step 4 in order to get the compiler to temporarily accept the presence of the custom type within the Ling-Generated code.

8) Clean, and rebuild the project to insure the compile is clean. Nothing in .designer.cs should depend on a reference to the assembly which defines your custom class. this way Linq to SQL is free to generate <ProjectName>.designer.cs at will and this will not affect the code found in .cs.

Note: The C# class which maps to the DB table is normally under the complete control of Linq to SQL code generator. By spliting the definition of the this class between the two source files, and , the source code in the two files must now be manually coordinated. This means the technical debt of long term maintenance between these source files has been accepted.

9) Test the interface. The Getter/Setter code maps my custom type to (and from) the underlying C# data store (and thence to the DB column on a DB table). Because of this the code consuming the data can rely on the strong typing from the C# class.

The steps above are more more involved and not immediately obvvious. I did the above because rule number one with visual studio wizards (al code generating wizards for that matter):

Don't Fight the Wizard.

The Linq to SQL code generator is generating a lot of code on my behalf. This approach let me claim ownership of only those small pieces within the C#/Linq/SQL/DBColum code chain which deal with the C# class that is my custom type. If the Linq code is regenerated, then the compiler will throw compilation errors, but at this point the code maintainence is to delete the Linq to SQL generated code which is now a duplicate of my code in <ProjectName>.cs.

Not ideal, but it should work over the longer term.

The resulting code was:

    namespace TestRepository
{
    using System;
    using Framework; // Contains definition of the TestPropertyDataType class

    /*
     *******************************************************************************************************
     * See: http://stackoverflow.com/questions/1097090/how-do-i-map-custom-types-in-linq-to-sql
     * 
     * Expanding on the private/public interface mentioned here and using the fact that Linq auto generates a 
     * public/private pairing with the getter/setter code, I mutated the Linq gneerated code to 
     * quietly convert from type System.Byte to the custom type, TestPropertyDataType.
     * 
     * Original code as generated by Linq to SQL which I lifted from the file: TestRepository.designer.cs
     * *****************************************************************************************************
     * [global::System.Data.Linq.Mapping.TableAttribute(Name="dbo.TEST_ACTION_PROPERTIES")]
     * public partial class TestActionPropertyMetadata : INotifyPropertyChanging, INotifyPropertyChanged
     * {
     *     // <Lots of Linq-generated code>
     *         
     *     private TestPropertyDataType _DataType;
     * 
     *     // more Linq-generated code>
     *     
     *     #region Extensibility Method Definitions
     *     partial void OnDataTypeChanging(TestPropertyDataType value);
     *     #endregion
     *     
     *     // <Lots more Linq-generated code>
     * 
     *     [global::System.Data.Linq.Mapping.ColumnAttribute(Name="DATA_TYPE", Storage="_DataType", DbType="TinyInt NOT NULL", CanBeNull=false)]
     *     public TestPropertyDataType DataType
     *     {
     *         get
     *         {
     *             return this._DataType;
     *         }
     *         set
     *         {
     *             if ((this._DataType != value))
     *             {
     *                 this.OnDataTypeChanging(value);
     *                 this.SendPropertyChanging();
     *                 this._DataType = value;
     *                 this.SendPropertyChanged("DataType");
     *                 this.OnDataTypeChanged();
     *             }
     *         }
     *     }
     * }
     * *****************************************************************************************************
     */
    public partial class TestActionPropertyMetadata
    {
        private byte _DataType;
        partial void OnDataTypeChanging(TestPropertyDataType value);

        [global::System.Data.Linq.Mapping.ColumnAttribute(Name = "DATA_TYPE", Storage = "_DataType", DbType = "TinyInt NOT NULL", CanBeNull = false)]
        public TestPropertyDataType DataType
        {
            get
            {
                return TestPropertyDataType.findByOrdinal(this._DataType);
            }
            set
            {
                if ((this.DataType != value))
                {
                    this.OnDataTypeChanging(value);
                    this.SendPropertyChanging();
                    this._DataType = (byte)value.Ordinal;
                    this.SendPropertyChanged("DataType");
                    this.OnDataTypeChanged();
                }
            }
        }
    }
}

Answer 3

A class to a varchar? I'm not aware of any functionality that supports this in LINQ-to-SQL. Your best bet may be a simple property (it can be private if you need):

[Column(Name="ColumnName", DbType="varchar(10) NULL", CanBeNull=true)]
private string MyPropertyString {
    get { /* serialize MyProperty yourself */ }
    set { /* deserialize MyProperty yourself */ }
}
public MyCustomType MyProperty {get;set;}

(i.e. read from or update your actual MyProperty property)

I also expect that you'd have to leave this off the designer, and add it yourself (and the MyProperty property) in a partial class.