Mapping Vertical Legacy Table with Fluent NHibernate

I am trying to map the following table setup, of which I have no control over (sorry for the text tables, I can't post images yet):

**METADATA**
METADATA_ID NUMBER(10,0)
METADATA_DICT_ID    NUMBER(10,0)
SAMPLE_ID   NUMBER(10,0)
CREATED_BY  NUMBER(10,0)
CREATE_DATE DATE
REQUIRED    VARCHAR2(9 BYTE)
METADATA_VALUE  VARCHAR2(300 BYTE)
METADATA_LARGE_VALUE    CLOB

**METADATA_DICT**
METADATA_DICT_ID    NUMBER(10,0)
METADATA_DICT_TYPE_ID   NUMBER(10,0)
METADATA_DICT_NM    VARCHAR2(60 BYTE)
DESCRIPTION VARCHAR2(512 BYTE)
CREATE_DATE DATE
CREATED_BY  VARCHAR2(20 BYTE)


**SAMPLES**
METADATA_DICT_ID    NUMBER(10,0)
METADATA_DICT_TYPE_ID   NUMBER(10,0)
METADATA_DICT_NM    VARCHAR2(60 BYTE)
DESCRIPTION VARCHAR2(512 BYTE)
CREATE_DATE DATE
CREATED_BY  VARCHAR2(20 BYTE)

Basically, a sample has a collection of metadata attributes, each of which is a key / value pair (with a little gotcha, in that the value can be in either a CLOB column, or a varchar2 column, depending on its size). Each key / value pair's key corresponds to a type of information being collected (METADATA_DICT_ID), while the key corresponds to a value the user has suppl开发者_如何学Goied (METADATA_VALUE / METADATA_LARGE_VALUE). A user might be prompted for 'Plasmid Type', and they would answer 'Source Clone', for instance. The reason for doing this was so a user can dynamically add new types of information (questions) to collect about samples by defining new metadata types. This actually works pretty well for their purposes, but I want to create a domain model now for use in an ASP.NET MVC app, and I want to populate that domain model with NHibernate.

It seems like there are a couple of ways to approach this. Ultimately, I want the data in the following structure:

public class ProteinSample : PersistentObject
    {
        public virtual SampleType SampleType { get; set; }
        public virtual Project Project { get; set; }
        public virtual long Variant { get; set; }
        public virtual DateTime CreationDate { get; set; }
        public virtual User User { get; set; }

        //Metadata table values:
        public virtual string PrepNumber { get; set; }
        public virtual string Host { get; set; }
        public virtual string NTermTag { get; set; }
        public virtual string CTermTag { get; set; }
        public virtual string Buffer { get; set; }
        public virtual double ConcentrationMgPerMl { get; set; }
        public virtual double StorageTemperatureCelsius { get; set; }
        public virtual string PurificationProtocol { get; set; }
        public virtual string Comments { get; set; }
        public virtual string ProteinSequence { get; set; }
    }

It would be ideal if I could get NHibernate to put data from the key-value pair Metadata table into each of above properties (PrepNumber, Host, NTermTag, etc...) directly. Currently what I have is the following:

 public class ProteinSample : PersistentObject
        {

            public virtual SampleType SampleType { get; set; }
            public virtual Project Project { get; set; }
            public virtual long Variant { get; set; }
            public virtual DateTime CreationDate { get; set; }
            public virtual User User { get; set; }

            public virtual IList<SampleMetadata> Metadata { get; set; }
        }  

public class SampleMetadata : PersistentObject
    {
        public virtual ProteinSample ProteinSample { get; set; }
        public virtual MetadataType MetadataType { get; set; }
        public virtual string Value { get; set; }
        public virtual string LargeValue { get; set; }
    }

public class MetadataType : PersistentObject
    {
        public virtual string MetadataDictionaryName { get; set; }
    }

which is working, but I want that List flattened out into the specific I properties in the first class above. I can supply my Fluent NHibernate mappings if folks want to see them.

Is this perhaps a job for AutoMapper after I pull the data out?

What do people think?

The mutually exclusive METADATA_LARGE_VALUE vs. METADATA_VALUE issue is pretty tricky. Any suggestions there?

Thanks for any input.

---

The key thing I see here is Separation of Concerns:

Your domain object also contains knowledge of how it's persisted (because it inherits from PersistableObject), and that's too much responsibility for one object IMO. Persistence and business logic are separate issues.

Instead of trying to map to ProteinSample directly using nHibernate, I'd recommend you create (autogenerate is best) classes that mirror the schema tables (aka DTOs) and then manually map between the DTOs (e.g. MetadataDto, MetadataDictDto, SamplesDto) and your domain entity (ProteinSample) in your ProteinSampleRepository.

I think that'll make the problem easier to manage.