NHibernate QueryOver Subquery

Question

I've looked at the similar questions, but can't find a simple explanation. I could have missed it, but I promise I looked. Actually I can't even find the documentation other than a single blog post that glosses over everything rapidly and assumes you're familiar with other forms of NH.

Given a many-to-many between Program and Topic, where the latter is in a hierarchy of Topics, I want to retrieve all the Programs for a given Topic, possibly including its subtopics. Since a program may be listed under multiple sub-topics of a given parent topic, I need to use a subquery or deal with having to use distinct (and the simple approach of TransformUsing(Transformers.DistinctRootEntity) didn't work).

Raw SQL should be something like

SELECT ProgramId, Title, bar, baz, foo FROM Programs 
WHERE ProgramId IN 
 (SELECT ProgramId from Program_Topics WHERE TopicId IN (1, 2, ...))

The results are cast into a model type for transfer to the view. My initial attempt was this:

ProgramDTO pDTO = null;

/* topicIds is List<int> passed into function */

var query = Session.QueryOver<Program>()
.JoinQueryOver<Topic>(p => p.Topics)
.WhereRestrictionOn(pt => pt.Id).IsInG<int>(topicIds)     
.TransformUsing(Transformers.DistinctRootEntity)
.SelectList(list => list
        .Select(program => program.Id).WithAlias(() => pDTO.Id)
        .Select(program => program.Title).WithAlias(() => pDTO.Title)
        .Select(program => program.Location).WithAlias(() => pDTO.Location)
        .Select(program => program.Description).WithAlias(() => pDTO.Description)
)
.TransformUsing(Transformers.AliasToBean(typeof(ProgramDTO)));

return query.List<ProgramDTO>();    

Obviously this runs a join instead of a subquery, but I can't find an example of doing a subquery with a many-to-many like this.

public class Program : Entity {
    public virtual ISet<Topic> Topics { get; protected internal set; }
     ...
}

public class Topic : Entity {
    public virtual ISet<Program> Programs { get; protected internal set; }
    public virtual Topic ParentTopic { get; protected internal set; }
    ...
}

Deflective

This blog helped me to solve my NHibernate subquery: andrewwhitaker.com/blog/2014/10/24/… – Cavendish

Answer 1

Well, hashed at this some more, and while I don't like one part of the results, it does work:

var distinctProgIdsSubQuery = QueryOver.Of<Program>().
JoinQueryOver<Topic>(p => p.Topics).
WhereRestrictionOn(pt => pt.Id).IsIn(topicIds)
.Select(Projections.Distinct(Projections.Property<Program>(p => p.Id)));


ProgramDTO pDTO = null;
var progQuery = Session.QueryOver<Program>()
    .WithSubquery.WhereProperty(p => p.Id).In(distinctProgIdsSubQuery)
    .SelectList(list => list
        .Select(program => program.Id).WithAlias(() => pDTO.Id)
        .Select(...)
        )
    .TransformUsing(Transformers.AliasToBean(typeof(ProgramDTO)));


return progQuery.List<ProgramDTO>();

This produces

SELECT this_.ProgramId as y0_, ...
FROM Programs this_ 
WHERE this_.ProgramId in (
        SELECT distinct this_0_.ProgramId as y0_ 
        FROM
            Programs this_0_ 
        inner join
            Programs_Topics topics3_ 
                on this_0_.ProgramId=topics3_.ProgramId 
        inner join
            Topics topic1_ 
                on topics3_.TopicId=topic1_.TopicId 
        WHERE
            topic1_.TopicId in (
                @p1, @p2, ...
            )
    ) 

This may be a limitation of NH, but there's no need to join the Programs table in the subquery. I tried to write this from the other direction -- that is, to create a QueryOver.Of<Topic>(), but I could not figure out how to select the program IDs at the end -- select was only giving me the TopicIds, and even then the query was still joining all three tables.

I'm not sure if MS-SQL's query optimizer will avoid the useless join or not, but it would be nice if we didn't have to rely on it.

For now though, this works, and hopefully someone else has fewer headaches than I did trying to figure this out.

Deflective

This really is NOT the answer since you still create the join(s) you wanted to avoid... I chose to go with a hand-written HQL query instead where the problem is easily solved. – Totten

If you hand-write an HQL query you give up strong typing and refactoring, which is a major benefit to QueryOver. While it does create an extra JOIN, I expect a SQL query optimizer to see that you don't need the extra table by reading the JOIN clause and figuring out that it can select that field instead and get the same results. If nothing else, it will likely run the WHERE first and then join on an index, so it will be fast. – Deflective

I have to admit that my knowledge of the execution of SQL code by SQL Server and its possible and probable optimizations is sparse, so I prefer not to rely on it too much. But thanks for the insight :-) – Totten

If you can come up with a solution that uses QueryOver or Linq-to-Nhibernate and doesn't wind up with the extra JOIN, I would accept that answer :) But HQL is just not an option for me. – Deflective

Answer 2

You need to create a dettached query containing the Id's and then use this sub query with the main query.

I have pasted an example here so you will need to replace the relevant bits with your class names etc.

First the set up (you can ignore this bit):-

public class TestDto {
  public long Id { get; set; }
  public string Name { get; set; }
}
...
TestDto dto = null;
var ids = new List<int> { 1,2,5,7 };

Now the dettached query:-

var idSubQuery = QueryOver.Of<CmsRegionContent>()
  .WhereRestrictionOn(w => w.Id).IsIn(ids)
  .Select(Projections.Distinct(Projections.Property<CmsPage>(s => s.Id)));

And the final bit is to put it all together:-

var query = Session.QueryOver<CmsPage>()
    .JoinQueryOver<CmsRegionContent>(l => l.CmsRegionContentList)
    .WithSubquery
    .WhereProperty(m => m.Id)
    .In(idSubQuery)
    .SelectList(list => list
                            .Select(p => p.Id).WithAlias(() => dto.Id)
                            .Select(p => p.PageName).WithAlias(() => dto.Name)
                )
                .TransformUsing(Transformers.AliasToBean(typeof(TestDto)));

var model = query.List<TestDto>();

This will create the following SQL:-

SELECT
     this_.Id as y0_,
     this_.PageName as y1_ 
FROM cmspage this_ inner join cmsregioncontent cmsregionc1_ 
  on this_.Id=cmsregionc1_.PageId 
WHERE cmsregionc1_.Id in (
    SELECT
         distinct this_0_.Id as y0_ 
    FROM cmsregioncontent this_0_ 
    WHERE this_0_.Id in (
        1 /* ?p0 */,
         2 /* ?p1 */,
         5 /* ?p2 */,
         7 /* ?p3 */)
    )

Hopefully you will be able to follow this with your class/property names.

Passivism

I was able to adapt this, but notice that this still joins to the relationship table, therefore it returns multiple rows per base entity. Compare SELECT * FROM cmspage WHERE id IN (SELECT ... cmsPageId FROM cmsregioncontent WHERE regionid IN ( ... )) to what you have there. The version given here is really not much different than just doing an Inner Join. – Deflective

Actually after checking, NH is still generating Programs INNER JOIN ProgramsTopics INNER JOIN Topics. var topicSubQuery = QueryOver.Of<Topic>().WhereRestrictionOn(pt => pt.Id).IsIn(topicIds).Select(Projections.Distinct(Projections.Property<Program>(p => p.Id))); var progQuery = Session.QueryOver<Program>().Where(p => p.Status != ProgramStatus.Archived).JoinQueryOver<Topic>(p => p.Topics).WithSubquery.WhereProperty(pt => pt.Id).In(topicSubQuery).SelectList(list => list /* ...snip... */) .TransformUsing(Transformers.AliasToBean(typeof(ProgramDTO))); – Deflective