Create DTO with reference to domain object in NHibernate

Question

I'm writing a summary query with grouping and counting in my NHibernate 3.3.3.4000 project. I have domain objects Position and Tag, with a many-to-many relationship. One Position will have a handful of Tags, but over time there will be a large number of Positions for each tag. So the relationship is one-directional, where Position has a collection of Tags, but not vice-versa.

My query will count up the number of Positions with each Tag, for Positions connected to a certain AcademicTerm (a scalar reference in Position). The query below works:

    public IPartialResult<TagSummary> GetTagSummaries(string termCode, int skip, int take)
    {
        Tag tagAlias = null;
        AcademicTerm termAlias = null;
        TagSummary summary = null;

        var tagQuery = Session.QueryOver<Position>()
            .JoinAlias(p => p.Term, () => termAlias)
            .Where(() => termAlias.TermCode == termCode)
            .JoinQueryOver<Tag>(t => t.Tags, () => tagAlias)
            .SelectList(projections => projections
                .SelectGroup(p => tagAlias).WithAlias(() => summary.Tag)
                .SelectCount(p => p.ID).WithAlias(() => summary.PositionCount))     
            .TransformUsing(Transformers.AliasToBean<TagSummary>());

        var countQuery = tagQuery.ToRowCountQuery().FutureValue<int>();

        var resultQuery = tagQuery
            .OrderBy(t => t.Name).Asc
            .Skip(skip)
            .Take(take)
            .Future<TagSummary>();

        return new PartialResult<TagSummary>(resultQuery, countQuery.Value);

The result type is TagSummary:

public class TagSummary
{
    public string TagName { get; set; }
    public int PositionCount { get; set; }
}

What's in there is the Tag's Name property, but what I really want is the Tag itself. I can't figure out how to do that. I've got the tagAlias right there, but I don't know how to get it into my TagSummary. Do I have to select each individual property of Tag? I could select the Tag's ID value, and then perform another query, but that doesn't seem very good.

Update I just discovered that the count query won't work, because ToRowCountQuery will strip out the grouping. Now I'm trying to solve that one.

Carl Raymond · Accepted Answer · 2013-09-18T18:39:00.120

To answer my own question, I ended up creating a subclass of Tag called SummarizedTag, which adds a count property. Then I changed my query to select all the individual properties of Tag, and compute the count, then hydrated them into SummarizedTags. Because they're a subclass of Tag, it's a little cleaner to deal with them in views. I would have to update the query if I were to add more properties to Tag; that seems unavoidable.

To handle the paging with a grouped query, I had to build a second query "manually", rather than use ToRowCountQuery() on the first query.

public IPartialResult<SummarizedTag> GetSummarizedTags(string termCode, int skip, int take)
    {
        Logger.DebugFormat("GetSummarizedTags: termCode={0}, skip={1}, take={2}", termCode, skip, take);

        Tag tagAlias = null;
        AcademicTerm termAlias = null;
        SummarizedTag summary = null;

        // Get all tags attached to positions in specified term, with their use counts
        var tagQuery = Session.QueryOver<Position>()
            .Where(p => p.Status == PositionStatus.Published)
            .JoinAlias(p => p.Term, () => termAlias)
            .Where(() => termAlias.TermCode == termCode)
            .JoinQueryOver<Tag>(t => t.Tags, () => tagAlias)
            .SelectList(projections => projections
                .SelectGroup(() => tagAlias.ID).WithAlias(() => summary.ID)
                .SelectGroup(() => tagAlias.Name).WithAlias(() => summary.Name)
                .SelectGroup(() => tagAlias.Active).WithAlias(() => summary.Active)
                .SelectGroup(() => tagAlias.Description).WithAlias(() => summary.Description)
                .SelectGroup(() => tagAlias.UrlFormatName).WithAlias(() => summary.UrlFormatName)
                .SelectCountDistinct(p => p.ID).WithAlias(() => summary.Count))
            .TransformUsing(Transformers.AliasToBean<SummarizedTag>())
            .OrderByAlias(() => summary.Count).Desc
            .Skip(skip)
            .Take(take)
            .Future<SummarizedTag>();

        // Count tags used in positions in specified term.
        var countQuery = Session.QueryOver<Position>()
            .JoinAlias(p => p.Term, () => termAlias)
            .Where(() => termAlias.TermCode == termCode)
            .JoinQueryOver<Tag>(p => p.Tags, () => tagAlias)
            .Select(Projections.CountDistinct(() => tagAlias.ID))
            .FutureValue<int>();

        return new PartialResult<SummarizedTag>(tagQuery, countQuery.Value);
    }

Create DTO with reference to domain object in NHibernate

1 Answers1