功能NHibernate加入不使用主键 [英] Fluent NHibernate join not using primary key

问题描述

我想从那里非PK在我的主表被连接到外部表的PK连接表得到的一个属性。下面是一个过于简单的什么，我试图完成的例子（我不想引用外国实体）：

表：

  CREATE TABLE状态
（
 ID INT ，
正文文本，
的CategoryId INT 
）
 
 CREATE TABLE类别
（
 ID INT，
 Name文本
）
  

SQL生成：

  SELECT标识，车身，类别ID，Category.Name AS类别名称
从状态
 LEFT JOIN类别在Category.Id = Status.CategoryId 
  

我试图映射在StatusMap联接喜欢这一点，但它似乎是在两个连接主键（其中Status.Id = Category.Id）：

 加入（类别M => 
 {
 m.Optional（）; 
 m.KeyColumn（类别ID）; 
 m.Map（X => x.CategoryName，姓名）; 
} ）; 
  

解决方案

据我所知道的唯一解决这个使用方法流利是映射到一个视图，你现在在做什么。 加入（）将始终映射到父表的主键。该KeyColumn方法指定只子表，而你的情况是分类表中的键列。

要达到理想的SQL使用简化版你上面会可能要使用参考来定义状态和类别之间的多到一的关系。

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");
});

解决方案

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.

这篇关于功能NHibernate加入不使用主键的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！