Reputation: 2882
I've this query :
var resQuery = (from pan in session.Query<Pan>()
orderby pan.AdnMonture.Marque.Nom select pan).ToList<Pan>();
The Pan object reference several other object :
<many-to-one name="TypeMonture" column="IDType_Monture" cascade="save-update" not-null="true" />
<many-to-one name="AgeMonture" column="IDAge_Monture" cascade="save-update" not-null="true" />
<many-to-one name="SexeMonture" column="IDSexe_Monture" cascade="save-update" not-null="true" />
<many-to-one name="NatureMonture" column="IDNature_Monture" cascade="save-update" not-null="true" />
<many-to-one name="MatiereVerre" column="IDMatiere_Verre" cascade="save-update" />
<many-to-one name="TypeCouleurVerre" column="IDType_Couleur_Verre" cascade="save-update" />
<many-to-one name="CouleurVerre" column="IDCouleur_Verre" cascade="save-update" />
<many-to-one name="ClasseVerre" column="IDClasse_Verre" cascade="save-update" />
<many-to-one name="MontageMonture" column="IDMontage_Monture" cascade="save-update" not-null="true" />
<many-to-one name="BaseMonture" column="IDBase_Monture" cascade="save-update" not-null="true" />
<many-to-one name="CharniereMonture" column="IDCharniere_Monture" cascade="save-update" />
<many-to-one name="BrancheFormeMonture" column="IDBranche_Forme_Monture" cascade="save-update" />
<many-to-one name="BrancheEpaisseurMonture" column="IDBranche_Epaisseur_Monture" cascade="save-update" />
<many-to-one name="TenonPositionMonture" column="IDTenon_Position_Monture" cascade="save-update" not-null="true" />
<many-to-one name="TenonTailleMonture" column="IDTenon_Taille_Monture" cascade="save-update" not-null="true" />
<many-to-one name="FormeMonture" column="IDForme_Monture" cascade="save-update" not-null="true" />
And I need sometimes to have the value of each items, ex :
foreach (var pan in resQuery)
{
var test = pan.TypeMonture.Name
// and more ...
}
I've sometimes about 100 result in that query, and I can have about 1k query for this single page. Is there any solution to avoid that ? (other than stored procedure)
Regards
Edit
The Mathieu solution worked but I simplified the case for the example. And I've this error :
A fetch request must be a simple member access expression of the kind o => o.Related; 'pan.Adn.Fabricant' is too complex.
Nom du paramètre : relatedObjectSelector
My Pan object reference a Adn object.
<many-to-one name="AdnMonture" column="IDADN_Mont" cascade="save-update" not-null="true" />
This Adn object reference all the data I presented before. (cf. the hbm.xml before)
Is there any other solution for this case ? Maybe with join ?
Edit 2
I tryed to use ThenFetch
return (from pan in session.Query<PanierMonture>()
.Fetch(pan => pan.AdnMonture)
.ThenFetch(adn => adn.Fabricant)
.ThenFetch(adn => adn.Reference)
.ThenFetch(adn => adn.Marque)
orderby pan.AdnMonture.Marque.Nom
select pan).ToList<PanierMonture>();
the first ThenFirst works But the second is blocked by intellisence. I use the Fabricant object. So I tryied that :
return (from pan in session.Query<PanierMonture>()
.Fetch(pan => pan.AdnMonture)
.ThenFetch(adn => adn.Fabricant)
.Then(adn => adn.Reference)
.Then(adn => adn.Marque)
orderby pan.AdnMonture.Marque.Nom
select pan).ToList<PanierMonture>();
But again it doesn't work. intellisense don't give me the adn object
I found this workaround :
return (from pan in session.Query<PanierMonture>()
.Fetch(pan => pan.AdnMonture)
.ThenFetch(adn => adn.Fabricant)
.Fetch(pan => pan.AdnMonture)
.ThenFetch(adn => adn.Marque)
.Fetch(pan => pan.AdnMonture)
.ThenFetch(adn => adn.Reference)
.Fetch(pan => pan.AdnMonture)
.ThenFetch(adn => adn.Coloris)
.Fetch(pan => pan.AdnMonture)
.ThenFetch(adn => adn.TailleMonture)
orderby pan.AdnMonture.Marque.Nom
select pan).ToList<PanierMonture>();
Is it correct ? The generated sql query is a bit strange :
select ..... from Panier_Monture paniermont0_ left outer join ADN_Monture adnmonture1_ on paniermont0_.IDADN_Mont=adnmonture1_.IDADN_Monture left outer join Fabricant fabricant2_ on adnmonture1_.IDFabricant=fabricant2_.IDFabricant left outer join ADN_Monture adnmonture3_ on paniermont0_.IDADN_Mont=adnmonture3_.IDADN_Monture left outer join Marque marque4_ on adnmonture3_.IDMarque=marque4_.IDMarque left outer join ADN_Monture adnmonture5_ on paniermont0_.IDADN_Mont=adnmonture5_.IDADN_Monture left outer join Reference reference6_ on adnmonture5_.IDReference=reference6_.IDReference left outer join ADN_Monture adnmonture7_ on paniermont0_.IDADN_Mont=adnmonture7_.IDADN_Monture left outer join Coloris coloris8_ on adnmonture7_.IDColoris=coloris8_.IDColoris left outer join ADN_Monture adnmonture9_ on paniermont0_.IDADN_Mont=adnmonture9_.IDADN_Monture left outer join Taille_Monture taillemont10_ on adnmonture9_.IDTaille=taillemont10_.IDTaille left outer join ADN_Monture adnmonture11_ on paniermont0_.IDADN_Mont=adnmonture11_.IDADN_Monture left outer join Marque marque12_ on adnmonture11_.IDMarque=marque12_.IDMarque order by marque12_.Nom asc
The table AdnMonture is repeated each times ? Is there a chance of problem ?
Regards and again thank you
Upvotes: 0
Views: 887
Reputation: 31202
See this answer : Eager load while using Linq in NHibernate 3
var resQuery = (from pan in session.Query<Pan>()
.Fetch(p => p.TypeMonture)
.Fetch(p => p.AgeMonture)
.Fetch(p => p.Adn ).ThenFetch( a => a.Fabricant )// .. etc
orderby pan.AdnMonture.Marque.om select pan).ToList<Pan>();
Now your linked entities will be fetched and you won't have SELECT N+1 problems
Upvotes: 4