How to map an enum as a foreign key in (Fluent) NHibernate?

Question

How do I map an enum property as an integer foreign key in (Fluent) NHibernate?

The enum property serves as a type field which is an integer in the database, but it also needs to be a foreign key to a table which contains all the allowable values of that enum (reporting purposes).

Specifically, I need to have a Communication entity with a Type field of type enum CommunicationType mapped as an integer foreign key to the CommunicationType table. Code below.

NOTE: Answers for NHibernate (without Fluent) are accepted also.


public enum CommunicationType {
    General = 1,
    ServicesContactEnquiry = 2,
    ChangePassword = 3,
    OrderDetails = 4,  
}

// This is basically the Enum represented in the database for reporting purposes
public class CommunicationTypeRecord {
    public virtual int ID { get; set; }
    public virtual string Name { get; set; }
    public virtual string Description { get; set; }
}

public class Communication {
    public virtual CommunicationType Type { get; set; }
    // rest of fields ommitted
}

public class CommunicationMap : ClassMap<Communication> {
    public CommunicationMap() {
        Map(x => x.Type).CustomType<CommunicationType>.Not.Nullable();  // Needs to be FK -> [CommunicationType].[ID]
        // rest of fields ommitted
    }
}

public class CommunicationTypeRecordMap : ClassMap<CommunicationTypeRecord> {
    public CommunicationTypeRecordMap () {
        Table("CommunicationType");
        Id(x => x.ID).Column("ID").GeneratedBy.Assigned();
        Map(x => x.Name).Column("Name").Not.Nullable().CustomType("AnsiString");
        Map(x => x.Description).Column("Description").Nullable();
    }
}

Attempt 1

I tried the following which works for generating the DB correctly, but it doesn't work with hydrating/dehydrating data.

References<CommunicationTypeRecord>(x => x.CommunicationType).ForeignKey().Not.Nullable();

Exception:

[ArgumentException: There is a problem with your mappings.  You are probably trying to map a System.ValueType to a <class> which NHibernate does not allow or you are incorrectly using the IDictionary that is mapped to a <set>.  

A ValueType (CommunicationType) can not be used with IdentityKey.  The thread at google has a good description about what happens with boxing and unboxing ValueTypes and why they can not be used as an IdentityKey: http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&threadm=bds2rm%24ruc%241%40charly.heeg.de&rnum=1&prev=/groups%3Fhl%3Den%26lr%3D%26ie%3DUTF-8%26oe%3DUTF-8%26q%3DSystem.Runtime.CompilerServices.RuntimeHelpers.GetHashCode%26sa%3DN%26tab%3Dwg (Parameter 'key')]
Data:
StackTrace:
   at NHibernate.Util.IdentityMap.VerifyValidKey(Object obj)
   at NHibernate.Util.IdentityMap.set_Item(Object key, Object value)
   at NHibernate.Engine.StatefulPersistenceContext.AddChildParent(Object child, Object parent)
   at NHibernate.Engine.Cascade.CascadeToOne(Object parent, Object child, IType type, CascadeStyle style, Object anything, Boolean isCascadeDeleteEnabled)
   at NHibernate.Engine.Cascade.CascadeAssociation(Object parent, Object child, IType type, CascadeStyle style, Object anything, Boolean isCascadeDeleteEnabled)
   at NHibernate.Engine.Cascade.CascadeProperty(Object parent, Object child, IType type, CascadeStyle style, String propertyName, Object anything, Boolean isCascadeDeleteEnabled)
   at NHibernate.Engine.Cascade.CascadeOn(IEntityPersister persister, Object parent, Object anything)
   at NHibernate.Event.Default.AbstractSaveEventListener.CascadeBeforeSave(IEventSource source, IEntityPersister persister, Object entity, Object anything)
   at NHibernate.Event.Default.AbstractSaveEventListener.PerformSaveOrReplicate(Object entity, EntityKey key, IEntityPersister persister, Boolean useIdentityColumn, Object anything, IEventSource source, Boolean requiresImmediateIdAccess)
   at NHibernate.Event.Default.AbstractSaveEventListener.PerformSave(Object entity, Object id, IEntityPersister persister, Boolean useIdentityColumn, Object anything, IEventSource source, Boolean requiresImmediateIdAccess)
   at NHibernate.Event.Default.AbstractSaveEventListener.SaveWithGeneratedId(Object entity, String entityName, Object anything, IEventSource source, Boolean requiresImmediateIdAccess)
   at NHibernate.Event.Default.DefaultSaveOrUpdateEventListener.SaveWithGeneratedOrRequestedId(SaveOrUpdateEvent event)
   at NHibernate.Event.Default.DefaultSaveOrUpdateEventListener.EntityIsTransient(SaveOrUpdateEvent event)
   at NHibernate.Event.Default.DefaultSaveOrUpdateEventListener.PerformSaveOrUpdate(SaveOrUpdateEvent event)
   at NHibernate.Event.Default.DefaultSaveOrUpdateEventListener.OnSaveOrUpdate(SaveOrUpdateEvent event)
   at NHibernate.Impl.SessionImpl.FireSaveOrUpdate(SaveOrUpdateEvent event)
   at NHibernate.Impl.SessionImpl.SaveOrUpdate(Object obj)

Firo · Answer 1 · 2023-01-02T09:07:29.453

this should work. can't test it right now.

public class CommunicationTypeRecord {
    public virtual CommunicationType ID { get; set; }
    public virtual string Name { get; set; }
    public virtual string Description { get; set; }
}

public class Communication {
    public virtual CommunicationTypeRecord Type { get; set; }
    // rest of fields ommitted
}

public class CommunicationMap : ClassMap<Communication> {
    public CommunicationMap() {
        CompositeId().KeyReference(x => x.Type);
        // rest of fields ommitted
    }
}

public class CommunicationTypeRecordMap : ClassMap<CommunicationTypeRecord> {
    public CommunicationTypeRecordMap () {
        Table("CommunicationType");
        Id(x => x.ID).Column("ID").CustomType<CommunicationType>().GeneratedBy.Assigned();
        Map(x => x.Name).Column("Name").Not.Nullable().CustomType("AnsiString");
        Map(x => x.Description).Column("Description").Nullable();
    }
}

What i usually do is to not have the type as a record in the database at all. We can still create the table and entries but not use them in code at all. This is more performant (no joins) easier to maintain translations (no migrations needed for changes or additions of translations) and it is trivial to fill a combobox with all communicationtypes or use CommunicationType as a strategy object or add more metadata like Icon and so on.

public class CommunicationType
{
    public static readonly CommunicationType General = new CommunicationType(1, nameof(General));
    public static readonly CommunicationType ServicesContactEnquiry = new CommunicationType(2, nameof(ServicesContactEnquiry));
    public static readonly CommunicationType ChangePassword = new CommunicationType(3, nameof(ChangePassword));
    public static readonly CommunicationType OrderDetails = new CommunicationType(4, nameof(OrderDetails));

    private static IReadOnlyList<CommunicationType> _all;
    public static IReadOnlyList<CommunicationType> All { get { return _all ?? (_all = typeof(CommunicationType).GetFields(BindingFlags.Static | BindingFlags.Public).Select(f => f.GetValue(null)).Cast<CommunicationType>().ToArray()); } }

    private CommunicationType(int iD, string name)
    {
        ID = iD;
        NameKey = "CommunicationType." + name;
        DescriptionKey = NameKey + ".Description";
        Icon = NameKey + ".Icon";
    }

    public int ID { get; }
    /// <summary>resrouceKey to translate in ui</summary>
    public string NameKey { get; }
    /// <summary>resrouceKey to translate in ui</summary>
    public string DescriptionKey { get; }
    public string Icon { get; }

    public override int GetHashCode()
    {
        return ID.GetHashCode();
    }

    public override bool Equals(object obj)
    {
        var other = obj as CommunicationType;
        return other != null && ID == other.ID;
    }

    public static bool operator ==(CommunicationType x, CommunicationType y) => Equals(x, y);
    public static bool operator !=(CommunicationType x, CommunicationType y) => !Equals(x, y);
}

public class Communication
{
    public virtual CommunicationType Type { get; set; }
    // rest of fields ommitted
}

public class CommunicationMap : ClassMap<Communication>
{
    public CommunicationMap()
    {
        Id(x => x.Type).CustomType<CommunicationType>();
        // rest of fields ommitted
    }
}

public class CommunicationTypeUserType : ImmutableUserType // see https://stackoverflow.com/a/7484139/671619 for example implementation
{
    public override Type ReturnedType => typeof(CommunicationType);

    public override SqlType[] SqlTypes => new[] { NHibernateUtil.Int32.SqlType };

    public override object NullSafeGet(DbDataReader rs, string[] names, ISessionImplementor session, object owner)
    {
        var id = (int)rs[names[0]];
        return CommunicationType.All.First(c => c.ID == id);
    }

    public override void NullSafeSet(DbCommand cmd, object value, int index, ISessionImplementor session)
    {
        cmd.Parameters[index].Value = ((CopmmunicationType)value).ID;
    }
}

class ConnectionTypeDbObject : SimpleAuxiliaryDatabaseObject
{
    public static string Create
    {
        get {
            var builder = new StringBuilder("Create Table CommunicationType(...);", 100).AppendLine();
            foreach (var insert in CommunicationType.All.Select(t => $"INSERT INTO CommunicationType (...) Values ({t.ID}, ...) "))
                builder.AppendLine(insert);
            builder.AppendLine("Create ForeignKey ...");
            return builder.ToString();
        }
    }
    public const string Drop = "Drop Table CommunicationType";

    public ConnectionTypeDbObject() : base(Create, Drop) { }
}

I see where you're going but this breaks the `Communication` entity design since the Type property is no longer an enum type but is now an entity reference. We only want the reference at the DB level as a foreign key but to remain an enum at the code mapping level. In production, the code is structured in this way but the objective is to have enum at code level and foreign key at DB level. — Herman Schoenfeld, Dec 26 '22 at 12:55
@HermanSchoenfeld i added how i would do it which is more similar to what you want. If you decide to ditch the table, just remove the AuxiliaryObject and you are done — Firo, Jan 02 '23 at 09:08
Thanks @Firo. As you suggested in your comment, what I've done now is keep the enum field but with no foreign key to the separately generated type table. This will have to do for my purposes, but the question remains open. I need to map an Enum property as an integer foreign key in NHibernate. — Herman Schoenfeld, Jan 06 '23 at 02:55
@HermanSchoenfeld you can map the enum as foreign key exactly like i mapped the advanced enum, map the property and add the foreignkey with AuxiliaryObject. It is bad design however because having all the data associated to the type in one place makes using it much easier. I don't see any advantage of the vanilla enum there. — Firo, Jan 09 '23 at 07:26

How to map an enum as a foreign key in (Fluent) NHibernate?

Attempt 1

1 Answers1