Fluent NHibernate many-to-many using non-PK column

Question

I need to relate some tables using non-PK columns in two of them. The tables and FKs are set up as follows:

enter image description here

So a Message can be assigned to many Users and Groups via the MessageUserGroups table. Additionally, a User can be assigned to many Groups via the UserGroups table.

The AdGuid field (a uniqueidentifier) in Users and Groups is not the primary key for legacy reasons. However, I would like to use the UserId and GroupId fields in MessageUserGroups to relate Messages to Users and Groups through their AdGuid fields.

Additionally, I would like to have a PostedMessages property on User which relates Message.AuthorId to User.AdGuid, and have Messages properties on User and Group which relates to Messages through MessageUserGroups.

How do I explain this to Fluent NHibernate?

Currently the mappings look like:

public MessageMap()
{
    Id(m => m.Id);
    References(m => m.Author).Nullable().ForeignKey("FK_Messages_Author").Column("AuthorId").Fetch.Join().PropertyRef(u => u.AdGuid);

    HasManyToMany<users.user>(m => m.Users)
        .Cascade.All()
        .ParentKeyColumn("MessageId")
        .ChildKeyColumn("UserId")
        .Table("MessageUserGroups")
        .FetchType.Join();

    HasManyToMany<users.group>(m => m.Groups)
        .Cascade.All()
        .ParentKeyColumn("MessageId")
        .ChildKeyColumn("GroupId")
        .Table("MessageUserGroups")
        .FetchType.Join();
}
public UserMap()
{
    Id(u => u.Id);
    Map(u => u.AdGuid);

    HasManyToMany<staff.Message>(u => u.Messages)
        .Cascade.All()
        .ParentKeyColumn("AdGuid")
        .ChildKeyColumn("UserId")
        .Inverse()
        .LazyLoad()
        .Table("MessageUserGroup")

    HasMany<staff.Message>(u => u.PostedMessages)
        .Cascade.All()
        .KeyColumn("AuthorId")
        .Inverse()
        .LazyLoad()
        .Table("Messages")

    HasManyToMany<Group>(u => u.Groups)
        .Cascade.All()
        .Not.LazyLoad()
        .Cascade.All()
        .ParentKeyColumn("UserID")
        .ChildKeyColumn("GroupID")
        .Table("UserGroups")
}
public GroupMap()
{
    Id(g => g.Id);
    Map(g => g.AdGuid);

    HasManyToMany<staff.Message>(g => g.Messages)
        .Cascade.All()
        .ParentKeyColumn("AdGuid")
        .ChildKeyColumn("GroupId")
        .Inverse()
        .LazyLoad()
        .Table("MessageUserGroup")

    HasManyToMany<User>(g => g.Users)
        .Inverse()
        .Cascade.All()
        .ParentKeyColumn("GroupId")
        .ChildKeyColumn("UserId")
        .Table("UserGroups")
}

A little lengthy, I know. I can successfully query for Messages using NH. However, when lazy-loading the Messages properties of User or Group, NH generates this SQL (extra properties removed for brevity):

SELECT users0_.MessageId as MessageId1_, users0_.UserId as UserId1_, user1_.Id
as id38_0_, user1_.AdGuid as guid38_0_ FROM MessageUserGroups users0_
LEFT OUTER JOIN Users user1_ on users0_.UserId=user1_.Id WHERE users0_.MessageId=@p0

That join is invalid because it's trying to compare the uniqueidentifer MessageUserGroups.UserId against the bigint User.Id.

Likewise, when lazy-loading User.PostedMessages, this SQL is generated (again, abbreviated):

SELECT postedmess0_.AuthorId as AuthorId1_, postedmess0_.Id as Id1_, postedmess0_.Id as Id43_0_, postedmess0_.AuthorId as AuthorId43_0_
FROM Staff.Messages postedmess0_
WHERE postedmess0_.AuthorId=@p0

And @p0 is set to 89, which is the Id of the user I am testing with, but AuthorId needs to be a uniqueidentifier.

After both of those I get an SqlException: Operand type clash: uniqueidentifier is incompatible with bigint which is to be expected.

It looks like (Fluent) NHibernate always wants to join on the PKs, even though the docs suggest that specifying the ParentKeyColumn and ChildKeyColumn should allow me to specify any columns I like. Is that really possible? Am I misreading?

Firo Firo · Accepted Answer · 2012-08-19T20:25:39

ParentKeyColumn and ChildKeyColumn specify the column names in the link table between and not the columns of the entity-tables joined. What you need is PropertyRef and ChildPropertyRef on the hasmanytomany to specify the properties to join to

Fluent NHibernate many-to-many using non-PK column

1 Answers