Problem mapping composite key with NHibernate (column already been added)

2

new to NHibernate (and Hibernate for that matter), and I'm struggling with a composite key problem. Here's a simplified version of part of the database design.

  table_a
 +---------------------+
 | * a_id varcha (10)  |                   table_z
 |   label varchar(50) |                 +----------------------+
 |                     +<----------------+ * a_id varchar(10)   |
 +---------------------+      +----------| * b_id varchar(10)   |
                              |   +------+ * c_id varchar(10)   |
  table_b                     |   |      |   name varchar(100)  |
 +---------------------+      |   |      |                      |
 | * b_id varcha (10)  |      |   |      +----------------------+
 |   label varchar(50) <------+   |
 |                     |          |
 +---------------------+          |
                                  |
  table_c                         |
 +---------------------+          |
 | * c_id varcha (10)  <----------+
 |   label varchar(50) |
 |                     |
 +---------------------+

The key element here is table_z primary key is a composite of the 3 primary keys of table a,b,c (so, it controls unique combinations of a,b and c). They are also individually FK to table_a, table_b and table_c.

Now, beyond database design considerations, is there a way to map this into NHibernate. My attemps result in a stack trace complaining that "ArgumentException: The column 'a_id' has already been added in this SQL builder". Googling around told me that the issue is that I use the same field name on both end of the join. I'm surprised it's even an issue - or I totally misunderstand the problem..

Here's the DDL (Postgresql)

CREATE TABLE test.table_a(
a_id varchar(10) primary key,
label varchar(50)
);

CREATE TABLE test.table_b(
b_id varchar(10) primary key,
label varchar(50)
);


CREATE TABLE test.table_c(
c_id varchar(10) primary key,
label varchar(50)
);

CREATE TABLE test.table_z(
a_id varchar(10),
b_id varchar(10),
c_id varchar(10),
name varchar(100)
);

-- add combined primary key on table_z
ALTER TABLE test.table_z ADD CONSTRAINT pk_z_combined
    PRIMARY KEY (a_id,b_id,c_id)
;

-- FK

ALTER TABLE test.table_z ADD CONSTRAINT FK_to_a
    FOREIGN KEY (a_id) REFERENCES test.table_a (a_id) ON DELETE No Action ON UPDATE No Action;

ALTER TABLE test.table_z ADD CONSTRAINT FK_to_b
    FOREIGN KEY (b_id) REFERENCES test.table_b (b_id) ON DELETE No Action ON UPDATE No Action;

    ALTER TABLE test.table_z ADD CONSTRAINT FK_to_c
    FOREIGN KEY (c_id) REFERENCES test.table_c (c_id) ON DELETE No Action ON UPDATE No Action;

and here's the Fluent Hibernate C# code


using FluentNHibernate.Mapping;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace pm
{

    class TestAMapping : ClassMap<TestA>
    {
        public TestAMapping()
        {
            Table("test.table_a");
            Id(x => x.Id, "a_id");
            Map(x => x.Label, "label");
        }
    }

    class TestBMapping : ClassMap<TestB>
    {
        public TestBMapping()
        {
            Table("test.table_b");
            Id(x => x.Id, "b_id");
            Map(x => x.Label, "label");
        }
    }

    class TestCMapping : ClassMap<TestC>
    {
        public TestCMapping()
        {
            Table("test.table_c");
            Id(x => x.Id, "c_id");
            Map(x => x.Label, "label");
        }
    }

    class TestZMapping : ClassMap<TestZ>
    {
        public TestZMapping()
        {
            Table("test.table_z");
            CompositeId()
                .KeyProperty(x => x.Aid, "a_id")
                .KeyProperty(x => x.Bid, "b_id")
                .KeyProperty(x => x.Cid, "c_id");
            Map(x => x.Name, "name");
            References(x => x.TestAObj).Column("a_id");
            References(x => x.TestBObj).Column("b_id");
            References(x => x.TestCObj).Column("c_id");

        }
    }


    class TestA
    {
        public virtual string Id { get; set; }
        public virtual string Label { get; set; }

    }
    class TestB
    {
        public virtual string Id { get; set; }
        public virtual string Label { get; set; }

    }
    class TestC
    {
        public virtual string Id { get; set; }
        public virtual string Label { get; set; }

    }

    class TestZ
    {
        public virtual string Aid { get; set; }
        public virtual string Bid { get; set; }
        public virtual string Cid { get; set; }
        public virtual string Name { get; set; }
        public virtual TestA TestAObj { get; set; }
        public virtual TestB TestBObj { get; set; }
        public virtual TestC TestCObj { get; set; }

        // https://stackoverflow.com/a/7919012/8691687
        public override bool Equals(object obj)
        {
            var other = obj as TestZ;

            if (ReferenceEquals(null, other)) return false;
            if (ReferenceEquals(this, other)) return true;

            return this.Aid == other.Aid &&
                this.Bid == other.Bid && this.Cid == other.Cid;
        }

        public override int GetHashCode()
        {
            unchecked
            {
                int hash = GetType().GetHashCode();
                hash = (hash * 31) ^ Aid.GetHashCode();
                hash = (hash * 31) ^ Bid.GetHashCode();
                hash = (hash * 31) ^ Cid.GetHashCode();

                return hash;
            }
        }
    }

}

Relevant stack trace


FluentNHibernate.Cfg.FluentConfigurationException
  HResult=0x80131500
  Message=An invalid or incomplete configuration was used while creating a SessionFactory. Check PotentialReasons collection, and InnerException for more detail.


  Source=FluentNHibernate
  StackTrace:
   at FluentNHibernate.Cfg.FluentConfiguration.BuildSessionFactory()
   at pm.dal.DAL.CreateSessionFactory(String connectionString) in C:\Users\Laptop\source\repos\pm\dal\DAL.cs:line 49
   at pm.dal.DAL..ctor(String connectionString) in C:\Users\Laptop\source\repos\pm\dal\DAL.cs:line 41
   at pm.Manager.Manager.Connect(String connectionString) in C:\Users\Laptop\source\repos\pm\Manager\Manager.cs:line 102
  (blah...)

Inner Exception 1:
MappingException: Unable to build the insert statement for class pm.TestZ: a failure occured when adding the Id of the class

Inner Exception 2:
ArgumentException: The column 'a_id' has already been added in this SQL builder
Parameter name: columnName

Can anyone tell me where I sinned..

Thanks a heap

postgresql
fluent-nhibernate
asked on Stack Overflow Aug 17, 2019 by Eric Boisvert

3 Answers

2

I just looked how I did it in my project. Unfortunately I cannot describe why this is the way to go ;-)

public TestZMapping()
{
    Table("test.table_z");
    CompositeId()
        .KeyProperty(x => x.Aid, "a_id")
        .KeyProperty(x => x.Bid, "b_id")
        .KeyProperty(x => x.Cid, "c_id");
    Map(x => x.Name, "name");
    References(x => x.TestAObj).Column("a_id").Not.Insert().Not.Update();
    References(x => x.TestBObj).Column("b_id").Not.Insert().Not.Update();
    References(x => x.TestCObj).Column("c_id").Not.Insert().Not.Update();
}
answered on Stack Overflow Aug 18, 2019 by core
0

NHibernate 5 enforces you to define a clear resposibility for updating a database field - especially if you have multiple properties accessing the same field. Only one property mapping is allowed to be writable - being owner of the "new value".

A composite key does not really define the properties for a class - it just defines a composite key. Defining a key-property does NOT allow you e.g. to sort this property in a query. So you have to repeat this property in the property list. But this repeating must be read-only to give the key-property the responsibility to update the value.

NHibernate 4 with repeated properties => allowed, but might lead to unexpected behavior

<composite-id class="MyClass, MyDll" >
    <key-property name="Key1" />
    <key-property name="Key2"/>
</composite-id>
<property name="Key1" />
<property name="Key2" />
<property name="SomeProperty" />
<property name="SomeMoreKey1" column="Key1" />

NHibernate 5

<!-- not sortable by Key1 or Key2, because property is not known -->
<composite-id class="MyClass, MyDll" >
    <key-property name="Key1" />
    <key-property name="Key2"/>
</composite-id>
<!-- no allowed to repeat the keys as property => "has already been defined"
<property name="SomeProperty" />

NHibernate 5 with properties

<!-- readonly "repeated" properties -->
<composite-id class="MyClass, MyDll" >
    <key-property name="Key1" />
    <key-property name="Key2"/>
</composite-id>
<property name="Key1" insert="false" update="false"/>
<property name="Key2" insert="false" update="false"/>
<property name="SomeProperty" />
<property name="SomeMoreKey1" column="Key1"  insert="false" update="false"/>
answered on Stack Overflow Jan 10, 2020 by Fried
0

If you need to use both ComposedId and ManyToOne for same Column, and getting errors:

Unknown column 'SomeId' in 'field list'

or

The column 'SomeId' has already been added in this SQL builder Parameter name: columnName

Then just put ManyToOne inside ComposedId mapper. Like this:

internal class PricesMapping : ClassMapping<PriceEntity>
    {
        public PricesMapping()
        {
            Table("Prices");

            ComposedId(m =>
            {
                m.ManyToOne(x => x.PriceBlock, map =>
                {
                    map.Column("PriceBlockId");
                });
                m.Property(x => x.CurrencyCode);
            });

            Property(x => x.Value, map => map.Column("Price"));
        }
    }
answered on Stack Overflow Oct 29, 2020 by Roman M

User contributions licensed under CC BY-SA 3.0