title: Mapping Vertical Legacy Table with Fluent NHibernate
body: <p>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): </p>
  
  <pre><code>**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)
  </code></pre>
  
  <p>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 supplied (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.</p>
  
  <p>It seems like there are a couple of ways to approach this.  Ultimately, I want the data in the following structure: </p>
  
  <pre><code>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; }
      }
  </code></pre>
  
  <p>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:</p>
  
  <pre><code> 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&lt;SampleMetadata&gt; 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; }
      }
  </code></pre>
  
  <p>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.</p>
  
  <p>Is this perhaps a job for AutoMapper after I pull the data out?</p>
  
  <p>What do people think?</p>
  
  <p>The mutually exclusive METADATA_LARGE_VALUE vs. METADATA_VALUE issue is pretty tricky.  Any suggestions there?</p>
  
  <p>Thanks for any input.</p>