Search code examples
nhibernatefluent-nhibernatenhibernate-mappingmany-to-manycomposite-key

Fluent NHibernate: How to Map M:N many-to-many with composite keys on both sides

OK, so here is the problem. Its not even as crazy as the guy who wants to map m:n with different column counts in his PKs.

No matter what I do or where I look there seems to be no method chain that will result in a successful mapping of this.

I have tried doubling up on the Parent and Child columns, eg ParentColumn("").ParentColumn("").ChildColumn("").ChildColumn("") - didnt' think it would work and I was right.

Tried just using ForeignKeyConstraintNames no luck. Still FNH is mapping one side to a single key.

problem domain 

        public partial class M2M2ParentAMap : ClassMap<M2M2ParentA>
        {
            public M2M2ParentAMap()
            {
                Table("`M2M2ParentA`");
                Schema("`dbo`");
                CompositeId().KeyProperty( x => x.M2M2ParentAId1, "`M2M2ParentAId1`" ).KeyProperty( x => x.M2M2ParentAId2, "`M2M2ParentAId2`" );
                HasManyToMany(x => x.M2M2ParentB).Schema("`dbo`")
                    .ForeignKeyConstraintNames("FK_M2M2Link_M2M2ParentA", "FK_M2M2Link_M2M2ParentB");
            }
        }



        public partial class M2M2ParentBMap : ClassMap<M2M2ParentB>
        {
            public M2M2ParentBMap()
            {
                Table("`M2M2ParentB`");
                Schema("`dbo`");
                CompositeId().KeyProperty( x => x.M2M2ParentBId1, "`M2M2ParentBId1`" ).KeyProperty( x => x.M2M2ParentBId2, "`M2M2ParentBId2`" );
                HasManyToMany(x => x.M2M2ParentA)
                    .Schema("`dbo`").ForeignKeyConstraintNames("FK_M2M2Link_M2M2ParentB", "FK_M2M2Link_M2M2ParentA");
            }
        }



        public partial class M2M2LinkMap : ClassMap<M2M2Link>
        {
            public M2M2LinkMap()
            {
                Table("`M2M2Link`");
                Schema("`dbo`");
                CompositeId()
                    .KeyProperty( x => x.M2M2ParentA_Id1, "`M2M2ParentA_Id1`" )
                    .KeyProperty( x => x.M2M2ParentA_Id2, "`M2M2ParentA_Id2`" )
                    .KeyProperty( x => x.M2M2ParentB_Id1, "`M2M2ParentB_Id1`" )
                    .KeyProperty( x => x.M2M2ParentB_Id2, "`M2M2ParentB_Id2`" );

                References(x => x.M2M2ParentA).Columns("`M2M2ParentA_Id1`","`M2M2ParentA_Id2`").Cascade.All();
                References(x => x.M2M2ParentB).Columns("`M2M2ParentB_Id1`","`M2M2ParentB_Id2`").Cascade.All();
            }
        }


ERROR:
Foreign key (FK_M2M2Link_M2M2ParentB:M2M2ParentAToM2M2ParentB [M2M2ParentB_id])) must have same number of columns as the referenced primary key (M2M2ParentB [M2M2ParentBId1, M2M2ParentBId2])

AND

        public partial class M2M2ParentAMap : ClassMap<M2M2ParentA>
        {
            public M2M2ParentAMap()
            {
                Table("`M2M2ParentA`");
                Schema("`dbo`");
                CompositeId()
                    .KeyProperty( x => x.M2M2ParentAId1, "`M2M2ParentAId1`" )
                        .KeyProperty( x => x.M2M2ParentAId2, "`M2M2ParentAId2`" );

         HasManyToMany(x => x.M2M2ParentB)
            .Schema("`dbo`")
            .Table("`M2M2Link`")
            .ParentKeyColumn("`M2M2ParentA_Id1`")
            .ParentKeyColumn("`M2M2ParentA_Id2`")
            .ChildKeyColumn("`M2M2ParentB_Id1`")
            .ChildKeyColumn("`M2M2ParentB_Id2`");
            }
        }


        public partial class M2M2ParentBMap : ClassMap<M2M2ParentB>
        {
            public M2M2ParentBMap()
            {
                Table("`M2M2ParentB`");
                Schema("`dbo`");
                CompositeId()
                    .KeyProperty( x => x.M2M2ParentBId1, "`M2M2ParentBId1`" )
                    .KeyProperty( x => x.M2M2ParentBId2, "`M2M2ParentBId2`" );

        HasManyToMany(x => x.M2M2ParentA)
            .Schema("`dbo`")
            .Table("`M2M2Link`")
            .ParentKeyColumn("`M2M2ParentB_Id1`")
            .ParentKeyColumn("`M2M2ParentB_Id2`")
            .ChildKeyColumn("`M2M2ParentA_Id1`")
            .ChildKeyColumn("`M2M2ParentA_Id2`");
            }
        }



        public partial class M2M2LinkMap : ClassMap<M2M2Link>
        {
            public M2M2LinkMap()
            {
                Table("`M2M2Link`");
                Schema("`dbo`");
                CompositeId()
                    .KeyProperty( x => x.M2M2ParentA_Id1, "`M2M2ParentA_Id1`" )
                    .KeyProperty( x => x.M2M2ParentA_Id2, "`M2M2ParentA_Id2`" )
                    .KeyProperty( x => x.M2M2ParentB_Id1, "`M2M2ParentB_Id1`" )
                    .KeyProperty( x => x.M2M2ParentB_Id2, "`M2M2ParentB_Id2`" );

                References(x => x.M2M2ParentA)
                    .Columns("`M2M2ParentA_Id1`","`M2M2ParentA_Id2`").Cascade.All();

                References(x => x.M2M2ParentB)
                    .Columns("`M2M2ParentB_Id1`","`M2M2ParentB_Id2`").Cascade.All();
            }
        }

ERROR:
Foreign key (FKAB0E07EA57E45AB6:M2M2Link [M2M2ParentB_Id2])) must have same number of columns as the referenced primary key (M2M2ParentB [M2M2ParentBId1, M2M2ParentBId2])

DDL

CREATE TABLE [dbo].[M2M2ParentA] ( [M2M2ParentAId1] [int] NOT NULL,
                                   [M2M2ParentAId2] [int] NOT NULL,
CONSTRAINT [PK_M2M2ParentA] PRIMARY KEY CLUSTERED ( [M2M2ParentAId1] ASC, [M2M2ParentAId2] ASC ) )

CREATE TABLE [dbo].[M2M2ParentB] ( [M2M2ParentBId1] [int] NOT NULL,
                                   [M2M2ParentBId2] [int] NOT NULL,
CONSTRAINT [PK_M2M2ParentB] PRIMARY KEY CLUSTERED ( [M2M2ParentBId1] ASC, [M2M2ParentBId2] ASC ) )


CREATE TABLE [dbo].[M2M2Link] ( [M2M2ParentA_Id1] [int] NOT NULL,
                                [M2M2ParentA_Id2] [int] NOT NULL,
                                [M2M2ParentB_Id1] [int] NOT NULL,
                                [M2M2ParentB_Id2] [int] NOT NULL,
CONSTRAINT [PK_M2M2Link] PRIMARY KEY CLUSTERED ( [M2M2ParentA_Id1] ASC, [M2M2ParentA_Id2] ASC, [M2M2ParentB_Id1] ASC, [M2M2ParentB_Id2] ASC ) )



ALTER TABLE [dbo].[M2M2Link]
        WITH CHECK
ADD CONSTRAINT [FK_M2M2Link_M2M2ParentA] FOREIGN KEY ( [M2M2ParentA_Id1], [M2M2ParentA_Id2] ) REFERENCES [dbo].[M2M2ParentA] ( [M2M2ParentAId1],
                                                                                                                               [M2M2ParentAId2] )
ALTER TABLE [dbo].[M2M2Link]
        CHECK CONSTRAINT [FK_M2M2Link_M2M2ParentA]
ALTER TABLE [dbo].[M2M2Link]
        WITH CHECK
ADD CONSTRAINT [FK_M2M2Link_M2M2ParentB] FOREIGN KEY ( [M2M2ParentB_Id1], [M2M2ParentB_Id2] ) REFERENCES [dbo].[M2M2ParentB] ( [M2M2ParentBId1],
                                                                                                                               [M2M2ParentBId2] )
ALTER TABLE [dbo].[M2M2Link]
        CHECK CONSTRAINT [FK_M2M2Link_M2M2ParentB]

Update: I have tried creating a custom key type but was not successful.

Your challenge, if you choose to accept it:

Present code for the best working fluent mapping using this table structure, likely using a custom key type, and the bounty is yours.

Anyone?

Solution

  • If FluentNHibernate is currently unable to map this, then you can map it with a hbm.xml file.

    I also used a component for the composite id of both classes. This makes the identity separate from the entity, allowing session.Get<M2M2ParentA>( new M2M2Id( 1, 2 )). See this answer for a discussion of the 3 ways to represent composite-id (it is the same for NHibernate and Hibernate).

    <class name="M2M2ParentA" table="M2M2ParentA">
    <composite-id name="Id" class="M2M2Id">
        <key-property name="Id1" />
        <key-property name="Id2" />
    </composite-id>
    <bag name="BList" table="M2M2Link" lazy="false" fetch="join" >
        <key>
            <column name="M2M2ParentAId1" />
            <column name="M2M2ParentAId2" />
        </key>
        <many-to-many class="M2M2ParentB" >
            <column name="M2M2ParentBId1" />
            <column name="M2M2ParentBId2" />
        </many-to-many>
    </bag>
</class>

<class name="M2M2ParentB" table="M2M2ParentB">
    <composite-id name="Id" class="M2M2Id">
        <key-property name="Id1" />
        <key-property name="Id2" />
    </composite-id>
    <bag name="AList" table="M2M2Link" lazy="false" fetch="join" inverse="true">
        <key>
            <column name="M2M2ParentBId1" />
            <column name="M2M2ParentBId2" />
        </key>
        <many-to-many class="M2M2ParentA" >
            <column name="M2M2ParentAId1" />
            <column name="M2M2ParentAId2" />
        </many-to-many>
    </bag>
</class>

    And my version of your classes.

    public class M2M2ParentA
{
    public M2M2ParentA()
    {
        BList = new List<M2M2ParentB>();
    }
    public virtual M2M2Id Id { get; set; }
    public virtual string Name { get; set; }
    public virtual IList<M2M2ParentB> BList { get; set; }
}

public class M2M2ParentB
{
    public M2M2ParentB()
    {
        AList = new List<M2M2ParentA>();
    }
    public virtual M2M2Id Id { get; set; }
    public virtual string Name { get; set; }
    public virtual IList<M2M2ParentA> AList { get; set; }
}

public class M2M2Id
{
    public M2M2Id() {}
    public M2M2Id( int id1, int id2 )
    {
        Id1 = id1;
        Id2 = id2;
    }
    public virtual int Id1 { get; set; }
    public virtual int Id2 { get; set; }
    public override int GetHashCode()
    {
        return Id1.GetHashCode() + Id2.GetHashCode();
    }
    public override bool Equals( object obj )
    {
        M2M2Id other = obj as M2M2Id;
        return other != null && Id1 == other.Id1 && Id2 == other.Id2;
    }
}