CODEWITHSUNDEEP
c#sqlentity-frameworknhibernateorm
dav_i
dav_i

Reputation: 28137

Map derived properties to serialized column in database

I have the following classes:

public class Base
{
    public int Id { get; set; }
    public string Name { get; set; }
}

public class Alpha : Base
{
    public string Foo { get; set; }
    public int Bar { get; set; }
}

public class Beta : Base
{
    public string Baz { get; set; }
}

And I want to be able to store this in a SQL database like so:

Id    Name    Type    ExtraInfo
=======================================
1     Asdf    Alpha   {"Foo":"hello","Bar":7}
2     Qwer    Beta    {"Baz":"goodbye"}

And be able to retrieve it in a sensible way, e.g. (pseudo-code):

repo.Bases.First(a => a.Type == "Alpha").Magic<Alpha>().Foo; // returns hello

...where Magic is some unknown method which performs the mapping.

My prefered ORM is Entity Framework, however, I am pretty sure this is a bit beyond the scope of it.

Is this possible with any ORM (NHibernate, or even EF, etc)?

Upvotes: 5

Views: 3062

Answers (2)

Mike Koder
Mike Koder

Reputation: 1928

With small tweaks that could be achieved with NHibernate and its UserTypes

public class Base
{
    public virtual int Id { get; set; }
    public virtual string Name { get; set; }
}
public class Alpha : Base
{
    public virtual CustomData Custom { get; set; }

    public class CustomData
    {
        public string Foo { get; set; }
        public int Bar { get; set; }
    }
}
// NHibernate mapping
public class AlphaMapping : SubclassMapping<Alpha>
{
    public AlphaMapping()
    {
        Property(a => a.Custom, m => 
        {
            // tell NHibernate to use custom type
            m.Type<JsonType<Alpha.CustomData>>();
        });
    }
}
// NHibernate UserType to serialize object to json and back
public class JsonType<T> : IUserType
{
    public object NullSafeGet(System.Data.IDataReader rs, string[] names, object owner)
    {
        var json = NHibernateUtil.String.NullSafeGet(rs, names[0]) as string;
        return Newtonsoft.Json.JsonConvert.DeserializeObject<T>(json);
    }

    public void NullSafeSet(System.Data.IDbCommand cmd, object value, int index)
    {
        var json = Newtonsoft.Json.JsonConvert.SerializeObject(value);
        NHibernateUtil.String.NullSafeSet(cmd, json, index);
    }
}

Upvotes: 6

Radim K&#246;hler
Radim K&#246;hler

Reputation: 123891

The solution should be splitted into 2 parts, I would say. 1) The mapping of the serialized stuff and 2) serialization. I am using NHibernate. this scenario is in fact very similar to more common issue storing 1) the path to a file 2) finding the file on the share.

The mapping. It is trivial. Just introduce the new Property ExtraInfo, could be on the base level

public virtual string ExtranInfo { get; set; }

Regardless of the ORM tool, we will simply map this to a nvarchar column ExtraInfo.

The inheritance is also pretty simple, for NHibernate we can use 8.1.1. Table per class hierarchy (I cannot help my self, the xml mapping is more readable, but fluent would do the same)

<class name="Base" table="BaseTable">

    <!-- common id -->
    <id name="Id" generator="native" />

    <!-- the column keeping the subtype selector -->
    <discriminator column="Type" type="String"/>

    <!-- base properties
    <property name="Name" />
    <property name="ExtraInfo" /> <!-- the JSON -->

    <subclass name="Alpha" discriminator-value="Alpha" />
    <subclass name="Beta" discriminator-value="Beta" />
    <!-- ... more subclasses -->
</class>

Serialization. It depends on the architecture (and partially on ORM tool). We can use AOP (preferable solution) hooked on the Read and Write operations to create the JSON or fill the other properties. NHibernate supports 12.1. Interceptors and 12.2. Event system

The comprehensive explanation of the Intercepting in Hibernate/NHibernate world could be found here

The query how to get our Alpha:

ISession session = ... // get NHSession standard way
var result = session.Query<Base>()
            .Where(b => b is Alpha)
            .Take(1)
            .Cast<EmployeeField>()
            .ToList<EmployeeField>()

So in this case, Interceptor in the OnLoad method had access to all the loaded information and can do that job for us

public override bool OnLoad(object entity, object id, 
    object[] state, string[] propertyNames, IType[] types)

NHibernate has another nice feature: IResultTransformer. It could be injected into the query processing this way (16. QueryOver Queries):

// not linq but QueryOver
var result = session.QueryOver<Base>()
            .Where(b => b is Alpha)
            // here we go
            .TransformUsing(new MyTransformer())
            .Take(1)
            .List<Alpha>()

Example of the custom ResultTransformer implementation, the signiture of the essential method:

public object TransformTuple(object[] tuple, string[] aliases)
{

Summary. Despite of the fact, that storing properties as JSON is not out of the box supported, we can manage that with simple varchar column persistence and some AOP/Interceptors.

Maybe interesting reading how the "dynamic columns/properties" is working in NHibernate: NHibernate Dynamic Columns Number

Upvotes: 1

Related Questions