NHibernate 3.1.0.4000 QueryOver SQL Optimisation

Question

I have Entity 'Content'. Each Content has a 'Placement' property. Placement has a many-to-many relationship width 'AdType' entity (Placement has IList<\AdType> property mapped).

I need to load all Placements that are used at least in one Content and associated width specified AdType.

My DAL function looks like this:

    public IList<Placement> Load(AdType adType)
    {
        return NHibernateSession.QueryOver<Content>()
            .JoinQueryOver(content => content.Placement)
            .JoinQueryOver<AdType>(placement => placement.AdTypes)
            .Where(_adType => _adType.Id == adType.Id)
            .Select(x => x.Placement).List<Placement>();
    }

This works fine but when I look to the SQL log i see:

SELECT this_.PlacementId as y0_ FROM AdManager.dbo.[Content] this_ inner join AdManager.dbo.[Placement] placement1_ on this_.PlacementId=placement1_.PlacementId inner join AdManager.dbo.AdTypeToPlacement adtypes5_ on placement1_.PlacementId=adtypes5_.PlacementId inner join AdManager.dbo.[AdType] adtype2_ on adtypes5_.AdTypeId=adtype2_.AdTypeId WHERE adtype2_.AdTypeId = @p0

SELECT placement0_.PlacementId as Placemen1_26_0_, placement0_.Name as Name26_0_ FROM AdManager.dbo.[Placement] placement0_ WHERE placement0_.PlacementId=@p0

SELECT placement0_.PlacementId as Placemen1_26_0_, placement0_.Name as Name26_0_ FROM AdManager.dbo.[Placement] placement0_ WHERE placement0_.PlacementId=@p0

This means that NHibernate takes all placements Id in first query and then queries all fields from Placement table by Id.

My question is: Does enyone know how to modify QueryOver method to force NHibernate load data in one query?

does adding .Fetch(x => x.Placement).Eager before the Select help? — Firo

Firo Firo · Accepted Answer · 2011-08-17T14:33:50

it seems NHibernate does think there might be something in the where which maybe filters out data which is needed tro initialize the placement. You can go with a subquery:

public IList<Placement> Load(AdType adType)
{
    var subquery = QueryOver.For<Content>()
        .JoinQueryOver(content => content.Placement)
        .JoinQueryOver<AdType>(placement => placement.AdTypes)
        .Where(_adType => _adType.Id == adType.Id)
        .Select(x => x.Id);

    return NHibernateSession.QueryOver<Content>()
        .WithSubquery.Where(content => content.Id).IsIn(subquery))
        //.Fetch(x => x.Placement).Eager   try with and without
        .Select(x => x.Placement).List<Placement>();
}

or SQL (has the disadvantage that it just fills the new Placement but doest track it)

public IList<Placement> Load(AdType adType)
{
    return NHibernateSession.CreateSQLQuery("SELECT p.Name as Name, ... FROM content c join placement p...")
        .SetResultTransformer(Transformers.AliastoBean<Placement>())
        .List<Placement>();
}

NHibernate 3.1.0.4000 QueryOver SQL Optimisation

1 Answers