Fluent NHibernate join tables in mapping not using primary key

Question

I'm trying to create one entity from 2 tables that are related not by primary key

Tables:

CREATE TABLE [employees](
    [ssn] [nvarchar](9) NULL,
    [active] [bit] NULL,    
    [employee_id] [int] IDENTITY(1,1) NOT NULL 
)


CREATE TABLE [sam_employees](
    [ssn] [nvarchar](9) NULL,
    [first_name] [nvarchar](50) NULL,
    [last_name] [nvarchar](50) NULL,
    [skill] [nvarchar](50) NULL,
    ....
)

SQL to generate:

SELECT this_.employee_id  as employee1_0_0_,
       this_.ssn          as ssn0_0_,
       this_.active       as active0_0_,
       this_1_.first_name as first2_1_0_,
       this_1_.last_name  as last3_1_0_,
       this_1_.skill      as skill1_0_
FROM   employees this_
       inner join sam_employees this_1_
         on this_.ssn = this_1_.ssn
WHERE  this_.active = 1

My current mapping:

public class EmployeeMap : ClassMap<Employee>
{
    public EmployeeMap()
    {
        Table("employees");
        Id(x => x.Id, "employee_id");
        Map(x => x.SSN, "ssn");
        Map(x => x.IsActive, "active");

        Join("sam_employees", mm =>
                                  {                                          
                                      mm.KeyColumn("ssn");
                                      mm.Map(xx => xx.FirstName, "first_name");
                                      mm.Map(xx => xx.LastName, "last_name");
                                      mm.Map(xx => xx.Skill, "skill");
                                  });            
    }
}

But with this mapping I have such join condition on this_.employee_id = this_1_.ssn

I know that this question was asked before but I didn't find a good answer for it, just workaround to use View in database side instead of tables.

So what exactly is your problem? It's joining on ssn but you don't want it to? — Cole W
– Cole W, Commented Apr 29, 2011 at 18:16
@Cole W I want to join by ssn on both sides, but I have employee_id from one side and ssn from another. — Alexey G.
– Alexey G., Commented Apr 29, 2011 at 20:26

Vadim · Accepted Answer · 2011-04-29 19:45:25Z

3

You can try mapping sam_employees as an entity and use a References mapping with a join type fetch.

answered Apr 29, 2011 at 19:45

Vadim

18k5 gold badges41 silver badges62 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Alexey G. Over a year ago

Agree I can do it, but I think it's another workaround for this problem.

Vadim Over a year ago

I don't think you'll find a 'real' solution then, short of submitting a patch to NHibernate as you cannot specify a property to use in join mapping.

Alexey G. Over a year ago

Could you help me with this question stackoverflow.com/questions/5833259/… by using QueryOver. Thanks in advance.

Vadim Over a year ago

What kind of help are you looking for? You already accepted my answer on that question.

Collectives™ on Stack Overflow

Fluent NHibernate join tables in mapping not using primary key

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related