Helping linqtosql datacontext use implicit conversion between varchar column in the database and table and a custom data type in my application

Question

I am creating an mssql database table, "Orders", that will contain a varchar(50) field, "Value" containing a string that represents a slightly complex data type, "OrderValue".

I am using a linqtosql datacontext class, which automatically types the "Value" column as a string.

I gave the "OrderValue" class implicit conversion operators to and from a string, so I can easily use implicit conversion with the linqtosql classes like this:

// get an order from the orders table
MyDataContext db = new MyDataContext();
Order order = db.Orders(o => o.id == 1);

// use implicit converstion to turn the string representation of the order
// value into the complex data type.
OrderValue value = order.Value;

// adjust one of the fields in the complex data type
value.Shipping += 10;

// use implicit conversion to store the string representation of the complex
// data type back in the linqtosql order object
order.Value = value;

// save changes
db.SubmitChanges();

However, I would really like to be able to tell the linqtosql class to type this field as "OrderValue" rather than as "string". Then I would be able to avoid complex code and re-write the above as:

// get an order from the orders table
MyDataContext db = new MyDataContext();
Order order = db.Orders(o => o.id == 1);

// The Value field is already typed as the "OrderValue" type rather than as string.
// When a string value was read from the database table, it was implicity converted
// to "OrderValue" type.
order.Value.Shipping += 10;

// save changes
db.SubmitChanges();

In order to achieve this desired goal, I looked at the datacontext designer and selected the "Value" field of the "Order" table.

Then, in properties, I changed "Type" to "global::MyApplication.OrderValue".

The "Server Data Type" property was left as "VarChar(50) NOT NULL"

The project built without errors.

However, when reading from the database table, I was presented with the following error message:

Could not convert from type 'System.String' to type 'MyApplication.OrderValue'.
    at System.Data.Linq.DBConvert.ChangeType(Object value, Type type)
    at Read_Order(ObjectMaterializer`1 )
    at System.Data.Linq.SqlClient.ObjectReaderCompiler.ObjectReader`2.MoveNext()
    at System.Linq.Buffer`1..ctor(IEnumerable`1 source)
    at System.Linq.Enumerable.ToArray[TSource](IEnumerable`1 source)
    at Example.OrdersProvider.GetOrders() 
    at ... etc

From the stack trace, I believe this error is happening while reading the data from the table. When presented with converting a string to my custom data type, even though the implicit conversion operators are present, the DBConvert class gets confused and throws an error.

Is there anything I can do to help it not get confused and do the implicit conversion?

Answer 1

Interesting problem... I managed to do it by extending the generated data context partial class. In there I created a separate "typed" object which is set on load of the original data and replaces the original data on save...

namespace LinqSQLTest
{
    partial class DataClasses1DataContext
    {

    }

    partial class Order
    {
        public OrderValue TypedValue { get; set; }

        partial void OnLoaded()
        {
            TypedValue = this.Value;
        }

        partial void OnValidate(System.Data.Linq.ChangeAction action)
        {
            if (action == System.Data.Linq.ChangeAction.Insert ||
                action == System.Data.Linq.ChangeAction.Update)
            {
                if (TypedValue != null) Value = TypedValue;
            }
        }        
    }    
}

To use it, you'd access your "Value" through the "TypedValue" property instead - not ideal, but it's the only way I can see of doing it.

private void button1_Click(object sender, RoutedEventArgs e)
{
    DataClasses1DataContext dc = new DataClasses1DataContext();
    Order order = new Order();
    OrderValue value = new OrderValue();
    value.OrderID=22;
    value.OtherStuff="stuff";
    order.Value= value;
    dc.Orders.InsertOnSubmit(order);
    dc.SubmitChanges();

    var incomingOrder = dc.Orders.Where(x => x.ID == 32).FirstOrDefault();
    Console.WriteLine("ID: {0} \r\n Stuff: {1}", incomingOrder.TypedValue.OrderID, incomingOrder.TypedValue.OtherStuff);

    incomingOrder.TypedValue.OrderID += 10;
    dc.SubmitChanges();

    var incomingOrder2 = dc.Orders.Where(x => x.ID == 32).FirstOrDefault();
    Console.WriteLine("ID: {0} \r\n Stuff: {1}", incomingOrder2.TypedValue.OrderID, incomingOrder2.TypedValue.OtherStuff);

}