You are here: Home / Blog / posts / Programming Magic / How to Use an nHibernate Composite Database Key

How to Use an nHibernate Composite Database Key

by Ken Taylor published Oct 28, 2013 10:40 AM, last modified Jan 28, 2014 11:39 AM
This article describes the extra steps required to setup a c# data access class and a nHibernate data map to use a composite database key. These additional steps allow LINQ to nHibernate to function properly for CRUD operations.

Using a Composite Database Key with Fluent nHibernate and LINQ to nHibernate:

Setting up the data access class is as straight forward as setting up a data access class with a non-composite database key; however, I would receive an unimplemented method exception when it attempted to filter by ID.

Therefore, I was able to devise a work-around by adding an additional method that returned the same ID key that nHibernate was already returning, but as a string.  I also changed the DomainObjectWithTypeId<ReimbursableBenefitsAccountId> back to DomainObjectWithTypeId<string>.  Lastly, for that sake of convenience, I added a new constructor to the composite ID class that accepted the ID string, and in turn, set the composite ID.  After making these changes I could now filter, insert and update the database records as desired.

Data Access Class:

Given the following class:
Note the highlighted portions are the additions I needed to get fluent LINQ to nHibernate working for ID filtering.  I also overrode the Equals and GetHashCode methods to defer to the ID class' Equals and GetHashCode methods; although, I'm not sure this is necessary or makes any difference.

public class ReimbursableBenefitsAccount : DomainObjectWithTypedId<string>
    {
        // Not a database field. private string _idString; public virtual string IdString { get { return _id.ToString(); } set { _idString = value; } }

        private ReimbursableBenefitsAccountId _id = new ReimbursableBenefitsAccountId();
        public new virtual ReimbursableBenefitsAccountId Id 
        {
            get { return _id; }
            set { _id = value; }
        }

        private string _orgId;
        [Display(Name = "Org ID")]
        [Required]
        public virtual string OrgId {
            get
            {
                return _orgId;
            }
            set
            {
                _orgId = value;
                _id.OrgId = _orgId; 
            }
        }

        private string _chart;
        [Display(Name = "Chart")]
        [Required]
        public virtual string Chart
        {
            get { return _chart; }
            set
            {
                _chart = value;
                _id.Chart = _chart;
            }
        }

        private string _account;
        [Display(Name = "Account")]
        [Required(AllowEmptyStrings = false, ErrorMessage = "You must enter an Account Number.")]
        [StringLength(7, ErrorMessage = "{0} must be seven (7) characters long.", MinimumLength = 7)]
        public virtual string Account
        {
            get
            {
                return _account;
            }
            set 
            { 
                _account = value;
                _id.Account = _account;
            }
        }

        [Display(Name = "Is Active")]
        [Required]
        public virtual bool IsActive { get; set; }

        public override bool Equals(Object obj)
        {
            return this.Id.Equals(obj);
        }

        public override int GetHashCode()
        {
            return this.Id.GetHashCode();
        }
    }

Composite ID Class:

For composite keys you also need to define an ID that overrides the Equal and GetHashCode methods:

public class ReimbursableBenefitsAccountId
    {
        public virtual string OrgId { get; set; }
        public virtual string Chart { get; set; }
        public virtual string Account { get; set; }

        public ReimbursableBenefitsAccountId() {} public ReimbursableBenefitsAccountId(string id) { if (!String.IsNullOrEmpty(id)) { try { var pieces = id.Split('|'); OrgId = pieces[0]; Chart = pieces[1]; Account = pieces[2]; } catch (Exception ex) { throw; } } }

        public override bool Equals(Object obj)
        {
            if (obj == null)
                return false;
            var t = obj as ReimbursableBenefitsAccountId;
            if (t == null)
                return false;
            if (OrgId == t.OrgId && Chart == t.Chart && Account == t.Account)
                return true;
            return false;
        }
        public override int GetHashCode()
        {
            return (OrgId + "|" + Chart + "|" + Account).GetHashCode();
        }

        public override string ToString()
        {
            return (OrgId + "|" + Chart + "|" + Account);
        }
    }

nHibernate Fluent mapping class:

The mapping class would be similar to the following:

Note that the fields comprising the composite key are also mapped, but with the NOT.UPDATE().NOT.INSERT() attributes, in addition to them being mapped as part of the composite key.

public class ReimbursableBenefitsAccountMap : ClassMap<ReimbursableBenefitsAccount>
    {
        public ReimbursableBenefitsAccountMap()
        {
            Table("ReimbursableBenefitsAccounts");
            CompositeId(x => x.Id)
                .KeyProperty(x => x.OrgId, "OrgID")
                .KeyProperty(x => x.Chart, "Chart")
                .KeyProperty(x => x.Account, "Account");

            Map(x => x.IdString).Formula("(Select OrgId + '|' + Chart + '|' + Account)").Not.Update().Not.Insert();
   
            Map(x => x.OrgId)
                .Not.Update()
                .Not.Insert();
            Map(x => x.Chart)
                .Not.Update()
                .Not.Insert();
            Map(x => x.Account)
                .Not.Update()
                .Not.Insert();
            Map(x => x.IsActive);
        }
    }

If all you require is a simple select with no filtering, SELECT * FROM MyTable then no additional modifications are necessary.

However, an issue arises if you attempt to use LINQ to nHibernate to filter out the results by Id. Apparently the overridden Equals and GetHashCode methods are not utilized, and an method not implemented exception is thrown.  Needless to say the query fails.

If you're dealing with a composite key that is derived from scalars, one workaround is to add an additional non-database field to both the data access class and the mapping class. This field basically duplicates the concatenated key already returned by nHibernate, and allows LINQ to nHibernate work as desired.  See the highlighted sections in the above code for an example.

When:

Where:

Contact