NHibernate Join Table with Non Primary Key

I am trying to do a one to one join with 2 tables using non-primary fields. I have 2 tables in the DB.

CREATE TABLE [dbo].[Branch](
[BranchID] [int] IDENTITY(1,1) NOT NULL,
[Branch_Name] [nvarchar](100) NULL)

CREATE TABLE [dbo].[Salesman](
[SalesmanID] [int] IDENTITY(1,1) NOT NULL,
[BranchID] [int] NOT NULL,
[First_Name] [nvarchar](30) NULL,
[Last_Name] [nvarchar](30) NULL)

I basically need the Branch Name whenever I retrieve a row from the salesman table. I thought I could add a join in the Salesman.hbm.xml file.

<join table="dbo.Branch">
  <key column="BranchID" />
  <property lazy="true"   update="false" insert="false"     not-null="false"  type="String" name="Branch_Name" />
</join>

This did not work because nHibernate always created a join with the primary key. I read some other posts and they suggested using a开发者_StackOverflow社区 view for situations like this. So I created a view like so:

create view dbo.VIEW_Salesman As
SELECT a.[SalesmanID], a.[BranchID], a.[First_Name],a.[Last_Name],
(select [Branch_Name] FROM [dbo].[Branch] WHERE BranchID= a.[BranchID]) As Branch_Name
FROM [dbo].[Salesman] as a

The above view actually works but is there a better solution when you want to join 2 tables using non-primary fields?

Thanks in advance for any suggestions and advice, Have a great day!

You could either use Dependancy Injection which actually has nothing to do with NHibernate, and would definitely force a change in your mapping file, or perhaps use a named query in the NHibernate configuration file.

Besides, I just thought that you perhaps could use composite mapping while using Dependancy Injection.

Salesman s = new Salesman(branchInstance)

So you should have a Branch property within your Salesman class that could allow you to know the branch name to which this salesman belongs. Otherwise, simply have a BranchName property which would actually return the branchInstance.Name property value.

See the following for Component Mapping using NHibernate:

1. NHibernate Mapping - ;
2. NHibernate - Chapter 7 - Component Mapping;
3. NHibernate Reference Documentation.

Or if you prefer to make it as a view using NHibernate, perhaps a named query should do it with less of a change:

1. 16.2. Named SQL queries.

Hope this helps! Do not hesitate to ask further details, I'll be pleased to assist you furhter if I can! =)