Fluent NHibernate join not using primary key

Question

I am trying to get a single property from a joined table where a non-PK in my main table is joined to the PK of the foreign table. Below is an oversimplified example of what I am trying to accomplish (I do not want to reference the foreign entity):

Tables:

CREATE TABLE Status
(
  Id int,
  Body text,
  CategoryId int
)

CREATE TABLE Category
(
  Id int,
  Name text
)

SQL to generate:

SELECT Id, Body, CategoryId, Category.Name AS CategoryName
FROM Status
LEFT JOIN Category ON Category.Id = Status.CategoryId

I am trying to map the join like this in the StatusMap but it seems to be joining on the two primary keys (where Status.Id = Category.Id):

Join("Category" m =>
{
  m.Optional();
  m.KeyColumn("CategoryId");
  m.Map(x => x.CategoryName, "Name");
});

Answer 1

As far as I know the only way around this using Fluent is to map to a view as you currently are doing. Join() will always map to the primary key of the parent table. The KeyColumn method specifies the key column for the child table only, which in your case is the Category table.

To achieve the desired SQL using your simplified version above you'd probably want to use References to define a many-to-one relationship between status and category.