Fluent Nhibernate foreign key mapping

Question

I have the tables:

Player
Id : int - primarykey
Name : string

BowlerType
Id : int - primarykey
Description : string

PlayerBowlerType
PlayerId : int not null foreign key referencing Player.Id
BowlerTypeId : int not null foreign key referencing BowlerType.Id

A player can confirm to many bowling types. heres some example data

Player
1 | Peter
2 | John

BowlerType
6 | Slow
7 | Fast

PlayerBowlerType
1 | 6
1 | 7
2 | 7

Answer 1

What you need here is a composite id to use with your PlayerBowlerType. This setup should work:

public class PlayerBowlerTypeId
{
    public virtual int PlayerId { get; set; }

    public virtual int BowlerTypeId { get; set; }

    public override bool Equals(object obj)
    {
        return Equals(obj as PlayerBowlerTypeId);
    }

    private bool Equals(PlayerBowlerTypeId other)
    {
        if (ReferenceEquals(other, null)) return false;
        if (ReferenceEquals(this, other)) return true;

        return PlayerId == other.PlayerId &&
            BowlerTypeId == other.BowlerTypeId;
    }

    public override int GetHashCode()
    {
        unchecked 
        {
            int hash = GetType().GetHashCode();
            hash = (hash * 31) ^ PlayerId.GetHashCode();
            hash = (hash * 31) ^ BowlerTypeId.GetHashCode();

            return hash;
        }
    }
}

public class PlayerBowlerType
{
    public PlayerBowlerType()
    {
        Id = new PlayerBowlerTypeId();
    }

    public virtual PlayerBowlerTypeId Id { get; set; }
}

public class PlayerBowlerTypeMap : ClassMap<PlayerBowlerType>
{
    public PlayerBowlerTypeMap()
    {
        Table("TABLENAME");

        CompositeId<PlayerBowlerTypeId>(x => x.Id)
            .KeyProperty(x => x.BowlerTypeId, "COLUMNNAME")
            .KeyProperty(x => x.PlayerId, "COLUMNNAME");
    }
}

You can technically do this without an identity object (the PlayerBowlerTypeId type would be removed and the code placed directly into the PlayerBowlerType and suitably adapted), but I've had a number of problems (3-4 separate bugs) caused by doing this. One of them is discussed here.

While I hate changing the domain objects to compensate for bugs in the ORM system, if you just use the PlayerBowlerTypeId type, it will save you a lot of headaches.

This should work as long as you modify the mapping to use your actual table and column names (and whatever else you need to do with the mapping for your particular setup).