I found a similar problem on the Hibernate Users forum, but since I am using NHibernate I wanted to make sure I posted this question in the correct forum. The other thread is located here:
http://forum.hibernate.org/viewtopic.php?t=984325&highlight=&sid=6269ef6851b40580197351e5ae1f6696
I have a similar problem, I was hoping somebody could help me with.
I have a table schema like so:
Code:
CREATE TABLE [dbo].[Entity](
[ID] [uniqueidentifier] NOT NULL,
[FKEY_ID] [uniqueidentifier] NOT NULL,
[Name] [nvarchar](50) NOT NULL,
CONSTRAINT [PK_Entity] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
CREATE TABLE [dbo].[JoinTable](
[ID] [uniqueidentifier] NOT NULL,
[FKEY_ID1] [uniqueidentifier] NOT NULL,
[FKEY_ID2] [uniqueidentifier] NOT NULL,
[Value] [nvarchar](50) NOT NULL,
CONSTRAINT [PK_Join] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
The schema is supposed to represent a many-to-many relationship between Entities. The JoinTable contains the relationship between Entities. Where FKEY_ID1 is the FKEY_ID (not the primary key) of the Parent Entity while FKEY_ID2 is the FKEY_ID (not the primary key) of the Child Entity.
I have the following domain objects:
Code:
public class Parent
{
public Guid Id;
public Guid FKeyId;
public string Name;
private IList<Child> _children;
public IList<Child> Children
{
get { return _children; }
set { _children = value; }
}
}
public class Child
{
public Guid Id;
public Guid FKeyId;
public string Name;
}
public class ParentChild
{
public Guid Id;
public Guid FKEY_ID1;
public Guid FKEY_ID2;
public string Value;
public Parent Parent;
public Child Child;
}
With the following mapping files:
Code:
<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2"
default-lazy="false"
assembly="NHibernateManyToMany"
namespace="NHibernateManyToMany"
default-access="field">
<class name="Parent" table="Entity">
<id name="Id" type="Guid" column="ID">
<generator class="guid"/>
</id>
<property name="FKeyId" column="FKEY_ID"/>
<property name="Name" column="Name"/>
<bag name="Children" access="property">
<key column="FKEY_ID1"/>
<one-to-many class="ParentChild"/>
</bag>
</class>
</hibernate-mapping>
<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2"
default-lazy="false"
assembly="NHibernateManyToMany"
namespace="NHibernateManyToMany"
default-access="field">
<class name="Child" table="Entity">
<id name="Id" type="Guid" column="ID">
<generator class="guid"/>
</id>
<property name="FKeyId" column="FKEY_ID"/>
<property name="Name" column="Name"/>
</class>
</hibernate-mapping>
<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2"
default-lazy="false"
assembly="NHibernateManyToMany"
namespace="NHibernateManyToMany"
default-access="field">
<class name="ParentChild" table="JoinTable">
<id name="Id" type="Guid" column="ID">
<generator class="guid"/>
</id>
<property name="FKEY_ID1" column="FKEY_ID1"/>
<property name="FKEY_ID2" column="FKEY_ID2"/>
<property name="Value" column="Value"/>
<many-to-one name="Parent" class="Parent" foreign-key="FKEY_ID">
<column name="FKEY_ID1" />
</many-to-one>
<many-to-one name="Child" class="Child" foreign-key="FKEY_ID">
<column name="FKEY_ID2" />
</many-to-one>
</class>
</hibernate-mapping>
A call to the following code:
Code:
Parent parent = session.Get<Parent>(new Guid("72EA439F-65DD-41EE-BBFA-82C211656EFF"));
Yields:
Code:
SELECT children0_.FKEY_ID1 as FKEY2___2_, children0_.ID as ID2_, hildren0_.ID as ID1_1_, children0_.FKEY_ID1 as FKEY2_1_1_, children0_.FKEY_ID2 as FKEY3_1_1_, children0_.Value as Value1_1_, child1_.ID as ID0_0_, child1_.FKEY_ID as FKEY2_0_0_, child1_.Name as Name0_0_
FROM JoinTable children0_ left outer join Entity child1_ on children0_.FKEY_ID2=child1_.ID/* Problem 1 */
WHERE children0_.FKEY_ID1='72ea439f-65dd-41ee-bbfa-82c211656eff'/* Problem 2 */
There are two problems with this SQL. Thus I am not creating the mapping properly.
1. The ID of the child1_ table should be FKEY_ID not ID. (i.e. =child1_.FKEY_ID) I would have thought that the foreign-key attribute within the ParentChild mapping file would have corrected this.
2. This ID in the where clause is the Primary key of the Parent. I want it to be the FKEY_ID of the Parent.
I would appreciate any help in this matter. The database we are currently trying to map to is very similar to the schema I am demonstrating here, however this example has been simplified.
Thanks in Advance
Login
FAQ
Search