Automatic serialization with Linq to Sql

Question

Is there any way to store data in the table kind of this:

Inside SettingsModel column, which is defined in Linq-to-Sql like this:

And also with DataContext option turned to the:

With the class SettingsModel defined like this:

namespace ElQueue.DataClasses
{
    [DataContract]
    public sealed class SettingsModel
    {
        [DataMember(IsRequired = true)]
        public int[] VideoMediaData { get; set; }
    }
}

This way? ...

using (SomeDataContext dataContext = new SomeDataContext())
{
    SettingsModel m = new SettingsModel();
    m.VideoMediaData = new int[] { 1, 2, 3 };
    dataContext.MainTableSettings.InsertOnSubmit(new MainTableSetting() { SettingsModel = m });
    dataContext.SubmitChanges();
}

using (SomeDataContext dataContext = new SomeDataContext())
{
    var r = dataContext.MainTableSettings.Single();
}

You see, the code above doesnt work correctly, it is throwing exception which says that it cannot convert string to MainTableSetting which means that either it can not save all the serialized data or that plus cannot deserialize it back.

I need some advices how to point this Linq-to-Sql to actually do serialization (and vice versa) when I access the database.

Answer 1

Although you can map XElement and XDocument to SQL Server as shown in the Type Mapping Run Time Behavior Matrix, the System.Data.Linq.DataContext.CreateDatabase method has no default SQL Server type mapping for these types.

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.

http://msdn.microsoft.com/en-us/library/bb386947.aspx#TextMapping

public sealed class SettingsModel
{
    // ...

    public static SettingsModel Parse(string source)
    {
        SettingsModel res;

        // Deserialise the object

        return res;
    }
}