Nhibernate query entity with enum property

Question

I need to query the entity Person with enum property TargetBookingSystemType

public class Person : EntityWithTypedId<PersonCompositeId>
{
    public virtual string Key { get; set; }

    public virtual TargetBookingSystemType TargetBookingSystemType { get; set; }
}


 public class PersonMap : ClassMap<Person>
 {
     public PersonMap()
     {
        this.CompositeId(x => x.Id).KeyProperty(y => y.AccountName, "[AccountName]").KeyProperty(y => y.Domain, "[Domain]");
        this.Table("Person");
        this.Map(x => x.Key).Column("[Key]");
        this.Map(x => x.TargetBookingSystemType).Column("[TargetBookingSystemType]");//.CustomType<TargetBookingSystemType>();
     }
 }

public enum TargetBookingSystemType
    {
        GoogleCalendarAPIv3 = 1,
        MSExchange2007 = 2,
        MSExchange2010 = 3,
        MSExchange2013 = 4,
        MSOnline = 5
    }

CREATE TABLE [dbo].[Person](
    [Domain] [varchar](3) NOT NULL,
    [AccountName] [varchar](255) NOT NULL,
    [Key] [varchar](255) NOT NULL,
    [TargetBookingSystemType] [nvarchar](20) NULL
 )

I know that a possible solution is to change the type of the property to string but how can I achieve that using NHibernate I can have the property as an enum and still get a successful query?

Tried CustomType() but with no luck getting an error Input string was not in a correct format.

please note that the query is working when I comment out the line with mapping of TargetBookingSystemType

EDIT:

I would like to have one call to DB and after that filtering the result based on the enum. This is the method doing the query using NHibernate:

public IList<Domain.DomainObjects.Entities.Person> GetAllPersons()
        {
            IList<Domain.DomainObjects.Entities.Person> list = new List<Domain.DomainObjects.Entities.Person>();
            string queryString = "select MR from Person MR";
            return this.Session.CreateQuery(queryString).SetCacheable(true).SetCacheRegion("LongTerm").List<Domain.DomainObjects.Entities.Person>();
        }

How can I get the enum property in the result aswell?

You should use a smallint as type for your column, cause enums are stored by their underlying type (int). Also if you choose to allow NULL as value you should declare the property TargetBookingSystemType as nullable. — Jehof, Jun 07 '17 at 07:51
Possible duplicate of [How do you map an enum as string in fluent nhibernate?](https://stackoverflow.com/questions/503310/how-do-you-map-an-enum-as-string-in-fluent-nhibernate) — Frédéric, Jun 07 '17 at 16:09

score 2 · Answer 1 · answered Jun 07 '17 at 09:56

2

I didn't think that NH required anything special to handle an enum type?()?

Once the column's underlying data type is OK (based on @Jehof's comment), I would expect this query to be adequate:

var results = session.Query<Person>().Where(p => p.TargetBookingSystemType == TargetBookingSystemType.MSExchange2010).ToList();

answered Jun 07 '17 at 09:56

David Osborne

6,436
1
21
35

Hi, please check the updated answer. Your answer is possible solution aswell but I would like to avoid changing the type in DB since more applications are in production atm and I cannot ensure that I wont break something else by modifying the DB. – pandemic Jun 07 '17 at 14:06
@pandemic, please edit your question to reflect this. [Avoid using **Edit** sections](https://meta.stackoverflow.com/q/350184/1178314) for this, just adjust the question for giving all your requirements fluently, without your reader having to scavenge them from several parts. – Frédéric Jun 07 '17 at 16:02

score 0 · Accepted Answer · answered Jun 08 '17 at 11:01

There is not a solution when you need to query over entity with enum property and nvarchar column in DB.

The possible workarounds

Change the datatype of the property from enum to string and keep the nvarchar in DB.

public class Person : EntityWithTypedId<PersonCompositeId>
{
    public virtual string Key { get; set; }

    public virtual string TargetBookingSystemType { get; set; }
}

OR

Keep the enum property in enum but change the column type from nvarchar to int as suggested. You also need to specify the CustomProperty<enumType>() in mapping class or xml.

CREATE TABLE [dbo].[Person](
    [Domain] [varchar](3) NOT NULL,
    [AccountName] [varchar](255) NOT NULL,
    [Key] [varchar](255) NOT NULL,
    [TargetBookingSystemType] [int] NULL )

Nhibernate query entity with enum property

2 Answers2