nhibernate query optimisation

Question

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

Community · Accepted Answer · 2017-05-23 11:47:53Z

4

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

edited May 23, 2017 at 11:47

CommunityBot

11 silver badge

answered May 24, 2011 at 12:52

mathieu

31.3k4 gold badges67 silver badges91 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

oursgris Over a year ago

thank you mathieu for your answer mathieu, it worked with the case I presented but not in the real case. I edited the question.

Collectives™ on Stack Overflow

nhibernate query optimisation

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related