Nhibernate ComposedId on intermediate table

Question

I want to have a intermediate table with only two foreign keys (as a ComposedId). But NHibernate is automatically creating a "id" property.

I have the following classes

public class Lace
{
    public virtual int Id { get; set; }
    public virtual string Hostname { get; set; }

    public virtual IList<LaceHasCard> LaceHasCards { get; set; }
}

public class Card
{
    public virtual int Id { get; set; }
    public virtual string Name { get; set; }

    public virtual IList<LaceHasCard> LaceHasCards { get; set; }
}

and this manually created intermediate table

public class LaceHasCard
{
    public virtual Card Card { get; set; }
    public virtual Lace Lace { get; set; }
}

Mappings

public LaceMapping()
{
    Id(x => x.Id, map => map.Generator(Generators.Native));
    Property(x => x.Hostname);

    Bag(x => x.LaceHasCards, col =>
    {
        col.Key(k => k.Column("LaceId"));
        col.Inverse(true);
    }, r => r.OneToMany());
}

public CardMapping()
{
    Id(x => x.Id, map => map.Generator(Generators.Native));
    Property(x => x.Name);

    Bag(x => x.LaceHasCards, col =>
    {
        col.Key(k => k.Column("CardId"));
        col.Inverse(true);
    }, r => r.OneToMany());
}

intermediate table mapping

public LaceHasCardMapping()
{
    //ComposedId(map =>
    //{
    //    map.Property(x => x.Card.Id, a =>
    //    {
    //        a.Column("CardId");
    //    });
    //    map.Property(x => x.Lace.Id, a =>
    //    {
    //        a.Column("LaceId");
    //    });
    //});

    ManyToOne(x => x.Card, map =>
    {
        map.Column("CardId");
    });

    ManyToOne(x => x.Lace, map =>
    {
        map.Column("LaceId");
    });
}

If I create the schema with the ComposedId commented out, NHibernate will create a "id" property in the table.

CREATE TABLE [dbo].[LaceHasCard] (
    [id]     INT NOT NULL,
    [CardId] INT NULL,
    [LaceId] INT NULL,
    PRIMARY KEY CLUSTERED ([id] ASC),
    CONSTRAINT [FKDC6D54711CD160AE] FOREIGN KEY ([CardId]) REFERENCES [dbo].[Card] ([Id]),
    CONSTRAINT [FKDC6D547151F8AF85] FOREIGN KEY ([LaceId]) REFERENCES [dbo].[Lace] ([Id])
);

If I try to create the schema with the ComposedId, I get the following error message:

Unable to instantiate mapping class (see InnerException): EmpLaceMgmt.Models.Mappings.LaceHasCardMapping

What would be the right way to tell NHibernate to create a composed Id?

*Despite of your question, my own suggestion would be - use other way to solve it. Do not use composite keys. Provide pairing object with its own ID. Make it first level citizen. Later you will be awarded once you will start to querying... One column, with auto generated, surrogate key - should not damage any performance. BUT NHiberante will offer easier handling... just my private suggestion.* — Radim Köhler, Feb 04 '15 at 10:41
so you mean I should add a Id property to my intermediate table, with auto increment, like all the other tables have? — samvdst, Feb 04 '15 at 10:44
I do use this approach 100%. (no many-to-many - each object, including the pairing is first level === has ID, has surrogated ID column...) That will bring just benefits... But this is my way. Maybe others would suggest different stuff. In extreme (which happens with users often) one can provide view driven by "pairing object"... then it is handy, that it does contain its one ID, because NHibernate can provide simplified handling... — Radim Köhler, Feb 04 '15 at 10:45
What do you mean with surrogated ID column? Is this the same as a normal primary key? — samvdst, Feb 04 '15 at 10:49
Yes, surrogate key means "It has NO BUSINESS meaning" http://en.wikipedia.org/wiki/Surrogate_key, I.e. it is just DB related stuff (and NHibernate ;) — Radim Köhler, Feb 04 '15 at 10:49

score 1 · Accepted Answer · answered Feb 04 '15 at 11:03

Let me give you suggestion, just my point of view - do not use composite id. Use standard primary key in DB and its C# / entity representation as Id { get; set; }

Chapter 24. Best Practices

...

Declare identifier properties on persistent classes.

NHibernate makes identifier properties optional. There are all sorts of reasons why you should use them. We recommend that identifiers be 'synthetic' (generated, with no business meaning) and of a non-primitive type. For maximum flexibility, use Int64 or String.

See also more about synthetic, surrogate keys at wiki.

From my experience, we should not be worry about having pairing object like this:

public class LaceHasCard
{
    public virtual int Id { get; set; } // the key
    public virtual Card Card { get; set; }
    public virtual Lace Lace { get; set; }
}

Because later it would become so easy to access it:

session.Get<LaceHasCard>(id)

And also to use it in Subqueries (for filtering Card with Laces and vice versa)

One column in DB, autogenerated, should not have any extra bad impact. But handling such table is a bit (a lot) easier...

So, summary, my suggestion would be, make all entities first level citizens, with full rights (including synthetic/surrogate key)

and how would I have to add data to the intermediate table in your suggestion? See: http://stackoverflow.com/questions/28324078/asp-net-mvc-nhibernate-bind-multi-select-list-to-ilistt — samvdst, Feb 05 '15 at 13:03

Nhibernate ComposedId on intermediate table

1 Answers1

Chapter 24. Best Practices

Declare identifier properties on persistent classes.

Linked