NHibernate - Mapping a String Foreign Key

Question

The legacy database I've inherited contains the following tables:

Teams ( 
 TeamId INT PRIMARY KEY,
 Name VARCHAR(30)
)

Players (
 PlayerId INT PRIMARY KEY,
 Team VARCHAR(30)
)

The foreign key in the players table refers to the team name, rather than teamId.

I've attempted to map from Team to Players using a bag:

<bag name="Players">
    <key column="Team" foreign-key="Name" />
    <one-to-many class="DataTransfer.Player, DataTransfer" />
</bag>

But I get SqlException: Conversion failed when converting the varchar value 'Arsenal' to data type int

I've been able to use a bag to map string foreign keys in other areas, but in those cases the foreign key referred to the primary key of the parent table.

Edit: I'm using NHibernate 2.0.1

Hi, We are having the same problem, have you found any solution to this? — fredrik
– fredrik, Commented Apr 17, 2009 at 13:51
I ended up just creating a new column of type int for the foreign key and using that instead, never managed to get a string foreign key working. — Mr Plough
– Mr Plough, Commented Apr 18, 2009 at 10:29

Frederik Gheysels · Accepted Answer · 2009-01-20 19:11:01Z

3

I think the property-ref attribute exists to solve this problem.

<bag name="Players">
   <key column="Team" property-ref="Team" />
   <one-to-many class="Player" property-ref="Team" />
</bag>

answered Jan 20, 2009 at 19:11

Frederik Gheysels

57.2k12 gold badges103 silver badges156 bronze badges

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

2 Comments

Mr Plough Over a year ago

It looks like NHibernate doesn't support the property-ref attribute in the one-to-many tag, and adding property-ref to just the key tag doesn't work either unfortunately.

Illuminati Over a year ago

Having the property-ref in "key" is just enough. No need to have it in the many-to-many is not required.

Nathan Baulch · Accepted Answer · 2010-11-12 07:40:07Z

3

Now I'm not 100% sure if this will work but have you tried a many-to-one mapping relationship?

Maybe something like this:

<many-to-one name="Players" class="DataTransfer.Player, DataTransfer"
             column="Name" property-ref="Team" />

I believe that should work, according to the NHibernate manual property-ref is an attribute that is useed for mapping legacy data where a foreign key refers to a unique key of the associated table other than the primary key. This sounds like the situation you find yourself in.

edited Nov 12, 2010 at 7:40

Nathan Baulch

20.9k5 gold badges56 silver badges59 bronze badges

answered Jan 20, 2009 at 18:28

CalvinR

7441 gold badge5 silver badges11 bronze badges

1 Comment

Mr Plough Over a year ago

I gave this a try but I got the error: More than one row with the given identifier was found: Arsenal, for class: DataTransfer.Player. I should have mentioned that I'm mapping the Team -> Player relationship rather than the other way round - I've updated my original question.

Jim Petkus · Accepted Answer · 2009-01-20 18:34:06Z

0

I believe that you would need to use the property-ref attribute to define the field that you will be associating to. The foreign-key attribute is used to generate DDL to create the relationships (if you use that feature).

answered Jan 20, 2009 at 18:34

Jim Petkus

4,4981 gold badge28 silver badges19 bronze badges

1 Comment

Mr Plough Over a year ago

With this I get a slightly different error: Identifier type mismatch; Found:<System.Int32> Expected:<System.String>

Daniel Schilling · Accepted Answer · 2013-10-24 17:50:27Z

0

Just found this: https://nhibernate.jira.com/browse/NH-1272, seems this is a bug in NHibernate, and it is fixed in 2.1.0Alpha1.

I have tried it in NHibernate 2.1.0Alpha2, and it works!

(property-ref should only be in the map tag)

edited Oct 24, 2013 at 17:50

Daniel Schilling

5,00531 silver badges62 bronze badges

answered Apr 20, 2009 at 10:06

fredrik

13.6k4 gold badges37 silver badges54 bronze badges

Collectives™ on Stack Overflow

NHibernate - Mapping a String Foreign Key

4 Answers 4

2 Comments

1 Comment

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

1 Comment

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related