Kia ora, quick question about joins in NHibernate.
In a project I am working on we have a (fairly) complex domain model with quite a bit of inheritance. Long story short it seems that to many joins causes NHibernate to 'give up' when loading, as shown by the following.
This NHibernate generated SQL loads entities as expected
Code:
SELECT lineitems0_.TransactionID as Transact1_2_,
lineitems0_.LineItemID as LineItemID2_,
lineitem1_.LineItemID as LineItemID23_0_,
lineitem1_.Version as Version23_0_,
lineitem1_.Code as Code23_0_,
lineitem1_.Description as Descript4_23_0_,
lineitem1_.Amount as Amount23_0_,
lineitem1_.SalesTax as SalesTax23_0_,
lineitem1_.PrePaidedAmount as PrePaide7_23_0_,
lineitem1_.InvoicedAmount as Invoiced8_23_0_,
lineitem1_.Quantity as Quantity23_0_,
lineitem1_.IsDeleted as IsDeleted23_0_,
lineitem1_.LastUpdated as LastUpd11_23_0_,
lineitem1_.LastUpdatedBy as LastUpd12_23_0_,
lineitem1_.Created as Created23_0_,
lineitem1_.CreatedBy as CreatedBy23_0_,
lineitem1_.OrderID as OrderID23_0_,
lineitem1_.CurrencyID as CurrencyID23_0_,
lineitem1_1_.ProductID as ProductID24_0_,
lineitem1_2_.PinCode as PinCode25_0_,
lineitem1_2_.Status as Status25_0_,
lineitem1_2_.ProfileID as ProfileID25_0_,
lineitem1_2_.SessionID as SessionID25_0_,
lineitem1_2_.AssessmentID as Assessme6_25_0_,
case
when lineitem1_1_.LineItemID is not null then 1
when lineitem1_2_.LineItemID is not null then 2
when lineitem1_.LineItemID is not null then 0
end as clazz_0_,
assessment2_.ProductID as ProductID10_1_,
assessment2_1_.Version as Version10_1_,
assessment2_1_.Code as Code10_1_,
assessment2_1_.Name as Name10_1_,
assessment2_1_.Description as Descript5_10_1_,
assessment2_1_.Visible as Visible10_1_,
assessment2_1_.IsDeleted as IsDeleted10_1_,
assessment2_1_.LastUpdated as LastUpda8_10_1_,
assessment2_1_.LastUpdatedBy as LastUpda9_10_1_,
assessment2_1_.Created as Created10_1_,
assessment2_1_.CreatedBy as CreatedBy10_1_,
assessment2_1_.SponsorID as SponsorID10_1_,
assessment2_1_.DisciplineID as Discipl13_10_1_,
assessment2_1_.DomainID as DomainID10_1_,
assessment2_.Duration as Duration11_1_,
assessment2_.MediaTypeID as MediaTyp3_11_1_,
assessment2_2_.AssessmentSystemID as Assessme2_12_1_,
assessment2_3_.RequiredAssessorCount as Required2_13_1_,
assessment2_3_.RequiresModeration as Requires3_13_1_,
case
when assessment2_2_.AssessmentID is not null then 2
when assessment2_3_.AssessmentID is not null then 3
when assessment2_.ProductID is not null then 1
end as clazz_1_
FROM TransactionToLineItem lineitems0_
left outer join [LineItem] lineitem1_
on lineitems0_.LineItemID = lineitem1_.LineItemID
left outer join SaleItemLineItem lineitem1_1_
on lineitem1_.LineItemID = lineitem1_1_.LineItemID
left outer join Booking lineitem1_2_
on lineitem1_.LineItemID = lineitem1_2_.LineItemID
left outer join Assessment assessment2_
on lineitem1_2_.AssessmentID = assessment2_.ProductID
left outer join [Product] assessment2_1_
on assessment2_.ProductID = assessment2_1_.ProductID
left outer join Exam assessment2_2_
on assessment2_.ProductID = assessment2_2_.AssessmentID
left outer join PracticalAssessment assessment2_3_
on assessment2_.ProductID = assessment2_3_.AssessmentID
WHERE lineitem1_.IsDeleted = 0
and assessment2_1_.IsDeleted = 0
AND lineitems0_.TransactionID = '1bb17b28-f9f2-4ab8-a741-9d6601100195' /* @p0 */
However, this NHibernate generated SQL
does not load entities at all.
Code:
SELECT lineitems0_.TransactionID as Transact1_3_,
lineitems0_.LineItemID as LineItemID3_,
lineitem1_.LineItemID as LineItemID23_0_,
lineitem1_.Version as Version23_0_,
lineitem1_.Code as Code23_0_,
lineitem1_.Description as Descript4_23_0_,
lineitem1_.Amount as Amount23_0_,
lineitem1_.SalesTax as SalesTax23_0_,
lineitem1_.PrePaidedAmount as PrePaide7_23_0_,
lineitem1_.InvoicedAmount as Invoiced8_23_0_,
lineitem1_.Quantity as Quantity23_0_,
lineitem1_.IsDeleted as IsDeleted23_0_,
lineitem1_.LastUpdated as LastUpd11_23_0_,
lineitem1_.LastUpdatedBy as LastUpd12_23_0_,
lineitem1_.Created as Created23_0_,
lineitem1_.CreatedBy as CreatedBy23_0_,
lineitem1_.OrderID as OrderID23_0_,
lineitem1_.CurrencyID as CurrencyID23_0_,
lineitem1_1_.ProductID as ProductID24_0_,
lineitem1_2_.PinCode as PinCode25_0_,
lineitem1_2_.Status as Status25_0_,
lineitem1_2_.ProfileID as ProfileID25_0_,
lineitem1_2_.SessionID as SessionID25_0_,
lineitem1_2_.AssessmentID as Assessme6_25_0_,
case
when lineitem1_1_.LineItemID is not null then 1
when lineitem1_2_.LineItemID is not null then 2
when lineitem1_.LineItemID is not null then 0
end as clazz_0_,
saleitem2_.ProductID as ProductID10_1_,
saleitem2_1_.Version as Version10_1_,
saleitem2_1_.Code as Code10_1_,
saleitem2_1_.Name as Name10_1_,
saleitem2_1_.Description as Descript5_10_1_,
saleitem2_1_.Visible as Visible10_1_,
saleitem2_1_.IsDeleted as IsDeleted10_1_,
saleitem2_1_.LastUpdated as LastUpda8_10_1_,
saleitem2_1_.LastUpdatedBy as LastUpda9_10_1_,
saleitem2_1_.Created as Created10_1_,
saleitem2_1_.CreatedBy as CreatedBy10_1_,
saleitem2_1_.SponsorID as SponsorID10_1_,
saleitem2_1_.DisciplineID as Discipl13_10_1_,
saleitem2_1_.DomainID as DomainID10_1_,
saleitem2_.AvailableForDownload as Availabl2_15_1_,
assessment3_.ProductID as ProductID10_2_,
assessment3_1_.Version as Version10_2_,
assessment3_1_.Code as Code10_2_,
assessment3_1_.Name as Name10_2_,
assessment3_1_.Description as Descript5_10_2_,
assessment3_1_.Visible as Visible10_2_,
assessment3_1_.IsDeleted as IsDeleted10_2_,
assessment3_1_.LastUpdated as LastUpda8_10_2_,
assessment3_1_.LastUpdatedBy as LastUpda9_10_2_,
assessment3_1_.Created as Created10_2_,
assessment3_1_.CreatedBy as CreatedBy10_2_,
assessment3_1_.SponsorID as SponsorID10_2_,
assessment3_1_.DisciplineID as Discipl13_10_2_,
assessment3_1_.DomainID as DomainID10_2_,
assessment3_.Duration as Duration11_2_,
assessment3_.MediaTypeID as MediaTyp3_11_2_,
assessment3_2_.AssessmentSystemID as Assessme2_12_2_,
assessment3_3_.RequiredAssessorCount as Required2_13_2_,
assessment3_3_.RequiresModeration as Requires3_13_2_,
case
when assessment3_2_.AssessmentID is not null then 2
when assessment3_3_.AssessmentID is not null then 3
when assessment3_.ProductID is not null then 1
end as clazz_2_
FROM TransactionToLineItem lineitems0_
left outer join [LineItem] lineitem1_
on lineitems0_.LineItemID = lineitem1_.LineItemID
left outer join SaleItemLineItem lineitem1_1_
on lineitem1_.LineItemID = lineitem1_1_.LineItemID
left outer join Booking lineitem1_2_
on lineitem1_.LineItemID = lineitem1_2_.LineItemID
left outer join SaleItem saleitem2_
on lineitem1_1_.ProductID = saleitem2_.ProductID
left outer join [Product] saleitem2_1_
on saleitem2_.ProductID = saleitem2_1_.ProductID
left outer join Assessment assessment3_
on lineitem1_2_.AssessmentID = assessment3_.ProductID
left outer join [Product] assessment3_1_
on assessment3_.ProductID = assessment3_1_.ProductID
left outer join Exam assessment3_2_
on assessment3_.ProductID = assessment3_2_.AssessmentID
left outer join PracticalAssessment assessment3_3_
on assessment3_.ProductID = assessment3_3_.AssessmentID
WHERE lineitem1_.IsDeleted = 0
and saleitem2_1_.IsDeleted = 0
and assessment3_1_.IsDeleted = 0
AND lineitems0_.TransactionID = '1bb17b28-f9f2-4ab8-a741-9d6601100195' /* @p0 */
The only difference between the two is the latter (non-working one) has an extra eager load on a property referencing the complex entity Product.
Basically I would like to know if this is a known 'feature', a bug or if I'm just doing it wrong. If it is a bug will it be resolved before NH2.0?
I really need to be able to eagerly load two properties which reference my complex entity (Product) which has a lot of inheritance. One eager load is fine, but if I add the second the joins NHibernate generates around it seem to cause it to give up when loading.
Also, I realize extreme joining takes a hit on performance, so do not need a lecture on that. Open to suggestions, I can provide my domain model if this helps.
Thanks in advance.
Login
FAQ
Search