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; }
...
}
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.