CODEWITHSUNDEEP
nhibernatelinq-to-nhibernate
oursgris
oursgris

Reputation: 2882

nhibernate query optimisation

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

Answers (1)

mathieu
mathieu

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

Related Questions