This is an example. I'm using NHibernate 2.1
I have in MSSQL2005 database 2 tables without any relation on server side. Tables: [MDPersons] and [MDPhoneType]. All types of fields in these tables are Varchar. Primary keys are: PersonID in [MDPersons] and PhoneTypeID in [MDPhoneType].
Code:
CREATE TABLE [dbo].[MDPersons](
[PersonName] [varchar](50) NULL,
[PersonPhone] [varchar](50) NULL,
[PhoneTypeID] [varchar](50) NULL,
[PersonID] [varchar](50) NOT NULL,
CONSTRAINT [PK_MDPersons_1] PRIMARY KEY CLUSTERED ([PersonID] ASC) WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]) ON [PRIMARY]
CREATE TABLE [dbo].[MDPhoneType](
[PhoneTypeID] [varchar](50) NOT NULL,
[PhoneType] [varchar](50) NULL,
CONSTRAINT [PK_MDPhoneType] PRIMARY KEY CLUSTERED ([PhoneTypeID] ASC) WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]) ON [PRIMARY]
In mapping file I create a "many-to-one" relation between these tables on fields: PhoneTypeID
Code:
<!-- PhoneType -->
<class name="PhoneType" table="MDPhoneType" dynamic-update="true" >
<id name="PhoneTypeID" column="PhoneTypeID" type="string">
<generator class="assigned" />
</id>
<property name="Phone_Type" column="PhoneType" type="string" />
</class>
<!-- Persons -->
<class name="Person" table="MDPersons" dynamic-update="true">
<id name="PersonID" type="string">
<generator class="assigned" />
</id>
<property name="PersonName" column="PersonName" type="string" />
<property name="PersonPhone" column="PersonPhone" type="string" />
[b]<many-to-one name="PhoneType" column="PhoneTypeID" class="PhoneType" not-found="ignore" fetch="join" />[/b]
</class>
Problem is that when in field PhoneTypeID of table [MDPersons] there are values which are not in field PhoneTypeID of table [MDPhoneType]
the NHibernate generates additional identical queries.
For example if I have code to retrieve all records from table [MDPersons]:
Code:
...
ICriteria crit = NHibernateSessionManager.Instance.GetSessionFrom(sessionFactoryConfigPath).CreateCriteria(typeof(Person));
return crit.List<Person>().ToList();
...
and there are 10 records in [MDPersons] with value 'AAA' in PhoneTypeID of table [MDPersons] which is not in the field PhoneTypeID of table [MDPhoneType]
then NHibernate generates a main query:
Code:
SELECT this_.PersonID as PersonID36_1_, this_.PersonName as PersonName36_1_, this_.PersonPhone as PersonPh3_36_1_, this_.PhoneTypeID as PhoneTyp4_36_1_,
phonetype2_.PhoneTypeID as PhoneTyp1_35_0_, phonetype2_.PhoneType as PhoneType35_0_
FROM MDPersons this_ left outer join MDPhoneType phonetype2_ on this_.PhoneTypeID=phonetype2_.PhoneTypeID
and 10 additional the identical queries for each copy of value 'AAA' from PhoneTypeID of table [MDPersons]
Code:
SELECT phonetype0_.PhoneTypeID as PhoneTyp1_35_0_, phonetype0_.PhoneType as PhoneType35_0_
FROM MDPhoneType phonetype0_ WHERE phonetype0_.PhoneTypeID=@p0;@p0 = 'AAA'
Question: Why it generates so many additional identical queries?
Login
FAQ
Search