I have a standrd Contact table which contains names, addresses,
emails, telephones etc. It's a pretty big, general purpose table that
get's used to store loads of different entities:
CREATE TABLE Contact
(
Contact_Ref INTEGER
PRIMARY KEY CLUSTERED,
Title INTEGER,
Initials NVARCHAR(4),
Surname NVARCHAR(30),
Etc...
Each Contact row has a ContactLink row which is basically links a
specific type of Contact, such as a Sales Rep to another entity:
CREATE TABLE ContactLink
(
cnta_det_ref INTEGER PRIMARY KEY CLUSTERED,
contact_ref INTEGER NOT NULL, <--- Foreign key to link to
the
Contact table
reltype INTEGER NULL <-- Type of entity e.g. sales
rep,
employer etc
CONSTRAINT FK_ContactDt_ContactHd
FOREIGN KEY (Contact_Ref)
REFERENCES Contact (Contact_Ref)
);
Basically the problem is that the Contact table is very wide so to
make searching more efficient I have a ContactInfoDAO which is a lite
object with just a few attibutes. To restrict results to retrieve only
sales reps I have to include the link to the ContactLink table as
well.
My problem is that when I check the sql, I am still bringing back the
*entire* contact row. Here are the HBMs:
The ContactInfo:
<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2"
namespace="Coldharbour.Framework.Model"
assembly="Coldharbour.Framework.Model">
<class name="Coldharbour.Framework.Model.ConactInfoDAO,
Coldharbour.Framework.Model" table="Contact" lazy="false">
<id name="ContactRef" column="Contact_Ref">
<generator class="assigned" />
</id>
<property name="Forename" column="Forenames" />
<property name="Surname" column="Surname" />
<property name="IsDeleted" column="IsDeleted" />
<!-- One-To-Many Associations-->
<bag name="ContactLinks" lazy="true" cascade="all-delete-orphan"
inverse="true" >
<key column="contact_ref"></key>
<one-to-many class="ContactLinkDAO"></one-to-many>
</bag> </class>
</hibernate-mapping>
The ContactLink Mapping:
<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2"
namespace="Coldharbour.Framework.Model"
assembly="Coldharbour.Framework.Model">
<class name="Coldharbour.Framework.Model.ContactLinkDAO,
Coldharbour.Framework.Model" table="Contact" lazy="true">
<id name="CntaDetRef" column="cnta_det_ref">
<generator class="assigned" />
</id>
<property name="RefType" column="ref_type" />
<property name="Reference" column="reference" />
<property name="ContSubstype" column="cont_substype" />
<property name="Reltype" column="reltype" />
<!-- Many-To-One Associations-->
<many-to-one name="Contact" column="contact_ref"
class="ContactDAO" inverse="false" />
</class>
</hibernate-mapping>
I think that problem is that the ContactLink has an association to the
full-fat ContactDAO object. However I have lazy load set to TRUE.
This is the SQL that get's generated:
SELECT top 100 this_.Contact_Ref as Contact1_3_2_,
this_.Forenames as Forenames3_2_,
this_.Surname as Surname3_2_,
this_.IsDeleted as IsDeleted3_2_,
links1_.cnta_det_ref as cnta1_5_0_,
links1_.ref_type as ref2_5_0_,
links1_.reference as reference5_0_,
links1_.cont_substype as cont4_5_0_,
links1_.reltype as reltype5_0_,
links1_.contact_ref as contact6_5_0_,
contactdao4_.Contact_Ref as Contact1_3_1_,
contactdao4_.Title as Title3_1_,
contactdao4_.Initials as Initials3_1_,
contactdao4_.Surname as Surname3_1_,
contactdao4_.Forenames as Forenames3_1_,
contactdao4_.Address1 as Address6_3_1_,
contactdao4_.Address2 as Address7_3_1_,
contactdao4_.Address3 as Address8_3_1_,
contactdao4_.Address4 as Address9_3_1_,
contactdao4_.Postcode as Postcode3_1_,
contactdao4_.Tel_No1 as Tel11_3_1_,
contactdao4_.Tel_No2 as Tel12_3_1_,
contactdao4_.Tel_No3 as Tel13_3_1_,
contactdao4_.Account_No as Account14_3_1_,
contactdao4_.Analysis as Analysis3_1_,
contactdao4_.Invoice_Type as Invoice16_3_1_,
contactdao4_.Gen_Comments as Gen17_3_1_,
contactdao4_.BkSortCode as BkSortCode3_1_,
contactdao4_.BkAccNo as BkAccNo3_1_,
contactdao4_.RecptGroup as RecptGroup3_1_,
contactdao4_.FsType as FsType3_1_,
contactdao4_.InvPrint as InvPrint3_1_,
contactdao4_.InvDoc as InvDoc3_1_,
contactdao4_.Email as Email3_1_,
contactdao4_.Other_Memo as Other25_3_1_,
contactdao4_.Birth_Date as Birth26_3_1_,
contactdao4_.Sex as Sex3_1_,
contactdao4_.Picture as Picture3_1_,
contactdao4_.VerbPass as VerbPass3_1_,
contactdao4_.PayDays as PayDays3_1_,
contactdao4_.Salut_Inv as Salut31_3_1_,
contactdao4_.PayDom as PayDom3_1_,
contactdao4_.PayDomPrev as PayDomPrev3_1_,
contactdao4_.BankAnal as BankAnal3_1_,
contactdao4_.DebtAnal as DebtAnal3_1_,
contactdao4_.DebtCC as DebtCC3_1_,
contactdao4_.InvBackDoc as InvBackDoc3_1_,
contactdao4_.ShowAccBal as ShowAccBal3_1_,
contactdao4_.IsDeleted as IsDeleted3_1_,
contactdao4_.CreatedBy as CreatedBy3_1_,
contactdao4_.CreatedDate as Created41_3_1_,
contactdao4_.UpdatedBy as UpdatedBy3_1_,
contactdao4_.UpdatedDate as Updated43_3_1_
FROM Contact this_
inner join ContactLink links1_
on this_.Contact_Ref = links1_.contact_ref
left outer join Contact contactdao4_
on links1_.contact_ref = contactdao4_.Contact_Ref
WHERE links1_.ref_type = 654 /* @p0 */
and this_.IsDeleted = 0 /* @p1 */
--
You received this message because you are subscribed to the Google Groups
"nhusers" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/nhusers?hl=en.
