Using NHibernate to map a nChar column to an enumerated type

Question

Hello Folks,

I am trying to map a table frp, a SQL Server 2005 DB to a class which contains an enum:

public class MyClass{
private YesNoOptional addressSetting;
public YesNoOptional AddressSetting{
    {get; set;}
}

}

public enum YesNoOptional {
    Yes,
    No,
    Optional
}

This will dictate that one of three values is inserted into the corresponding column - 'Y', 'N', 'O'. This column is of type nchar(1).

My mapping file is like so (addressSetting is the property which is causing the problem):

  <class name="IncidentDefinition" table="IR_INCIDENT_DEF" lazy="false" >
    <id name="Guid" column="INT_GUID" >
      <generator class="guid"></generator>
    </id>
    <property name="Reference" column="INT_REF" ></property>
    <property name="Description" column="INT_DESCRIPTION"  ></property>

    <property name="AddressSetting" column="INT_ADDRESS_REQ" ></property>

    <property name="Active" column="INT_ACTIVE" type="YesNo"></property>
    <property name="PinDocId" column="INT_PIN_DOC_ID"></property>
  </class>  

Using the config above I get the following error: NHibernate.ADOException: could not initialize a collection: System.FormatException: Input string was not in a correct format..

If I try to map the enum using a custom type like so:

<property name="AddressSetting" column="INT_ADDRESS_REQ" type="ML.Types.YesNoOptional, ML.Types" ></property>

Error: System.FormatException: Input string was not in a correct format.

Next, if I try using a specific type like so:

 <property name="AddressSetting" column="INT_ADDRESS_REQ" type="String" ></property>

This error is generated: NHibernate.PropertyAccessException: The type System.String can not be assigned to a property of type System.ArgumentException: Object of type 'System.String' cannot be converted to type 'ML.Types.YesNoOptional'..

As a last resort I tried to specify the type as a char like so:

<property name="AddressSetting" column="INT_ADDRESS_REQ" type="Char" ></property>

This works a bit better, as it in it doesnt throw an error, however instead of returning the character from the table and mapping it to the enumerated type the ASCII value of the character is returned instead - so Y is represented by 89!

I am hoping someone can explain what I am doing wrong and\or how why this is happening please?

Thanks

Morris

Answer 1

I believe that's because your trying to map a string to an enumeration. By default the base type of an enumeration type is an integer.

You could use something like Nullable<bool> to achieve what you want, instead of an enumeration. But if you really want to use one, AND keep the backing field as a nchar(1) column, I'd make a query like:

SELECT intAddressSetting = CASE AddressSetting WHEN 'Y' THEN  1
                                               WHEN 'N' THEN  0
                                               ELSE -1 
                           END
 FROM table

And use this filed to map to your enumeration.

Change the values as needed, usually a "NO" value has a value of 0 and a "YES" is 1 but in your enumeration they are reversed and Optional has a value of 2, which I'd make a -1.

Answer 2

I think I stumbled over another way to do this which might or might not be the right way to do it.

As I explained above the ASCII code value was returned, so for Y I was getting a value of 89 I extended the enum to use these values like so:

public enum YesNoOptional { Yes = 89, No = 79, Optional = 78 }

This populates the values correctly when they are returned from the database

Answer 3

You might consider creating a custom IUserType for this. I wrote a blog post about doing something similar with DateTime, and you could copy+paste 90% of the code: http://blog.statichippo.com/archive/2010/01/07/calendar-sizes-datetime-vs-nullable-sqldatetime-in-nhibernate.aspx

Basically, the biggest changes are in NullSafeSet and NullSafeGet. In the NullSafeSet you'll want to do a switch on your enum and then set the DB value to Y/N/O accordingly. And in your NullSafeGet you'll want to do a switch on the result (probably not a bad idea to run Char.ToLower() first) and return the corresponding enum.

Simple, powerful, and it doesn't hack your model to work with your DB.

Answer 4

Implements a IUserType and then give a value to query.substitutions to query it easy

<property name="query.substitutions">yes 'Y', no 'N', opt 'O'</property>