NHibernate Group By parent without an N + 1 query?

Question

I have two tables that have parent-child relationships. I want to count the records of a child table, grouping them by parent and collecting the results. So I want to see how many times each parent object is referenced in a child table.

So, if my parent table is Cats:

| Id | Name     |
|  1 | Bob      |
|  2 | Garfield |

and the child table is CatSkills:

| Id | Cat_Id | Skill        |
|  1 |      1 | Land on feet |
|  2 |      2 | Eat lasagne  |
|  3 |      2 | Escape diets |

I want to get the following:

| Id | Name     | count of skills |
|  1 | Bob      |               1 | 
|  2 | Garfield |               2 |

I tried with NHibernate LINQ, the query seems correct, but I get an exception "function not supported".

I tried with NHibernate QueryOver, there I get N + 1 problem:

var q = Session.QueryOver<CatSkill>()
    .Fetch(s => s.Cat).Eager
    .Select(Projections.ProjectionList()
        .Add(Projections.Group<CatSkill>(s => s.Cat))
        .Add(Projections.RowCount()))
        .List<object[]>();

The above query works, but it will retrieve all the parent records in separate queries.

SQL , SELECT GROUP BY.

- , ? !

, Radim, :

// a private class, just to make the query work
class CatDto : Cat
{
    public int Count { get; set; }
}

// the actual query code
Cat parent = null;
CatSkill child = null;
CatDto dto = null;

// this is in fact a subselect, which will be injected into parent SELECT
var subQuery = QueryOver.Of<CatSkill>(() => child)
    .Where(() => child.Cat.ID == parent.ID)
    .Select(Projections.RowCount());

// this is another subquery to filter out cats without skills
var skillFilterSubQuery = QueryOver.Of<CatSkill>(() => child)
    .Where(() => child.Cat.ID == parent.ID /* && more criteria on child table here... */)
    .Select(p => p.Cat);

// the alias here is essential, because it is used in the subselect
var query = session.QueryOver<Cat>(() => parent);

// I only want cats with skills
query = query.WithSubquery.WhereExists(skillFilterSubQuery);

query.SelectList(l => l
    .Select(p => p.ID).WithAlias(() => dto.ID)
    .Select(p => p.Name).WithAlias(() => dto.Name)
    // annoying part: I have to repeat the property mapping for all needed properties of parent...

    // see the parent.Count property
    .Select(Projections.SubQuery(subQuery)).WithAlias(() => dto.Count));

query.TransformUsing(Transformers.AliasToBean<CatDto>());

return query.List<CatDto>();

, N + 1, (Cat ) DTO.

, .Select(s => s), Exception, "".

Answer 1

Cat - .

Cat parent = null;
CatSkills child = null;

// this is in fact a subselect, which will be injected into parent SELECT
var subQuery = QueryOver.Of<CatSkills>(() => child)
    .Where(() => child.Cat.ID == parent.ID)
    .Select(Projections.RowCount());

// the alias here is essential, because it is used in the subselect
var query = session.QueryOver<Cat>(() => parent);

query.SelectList(l => l
    .Select(p => p.ID).WithAlias(() => parent.ID)
    .Select(p => p.Name).WithAlias(() => parent.Name)
    // see the parent.Count property
    .Select(Projections.SubQuery(subQuery)).WithAlias(() => parent.Count)
    );
query.TransformUsing(Transformers.AliasToBean<Cat>());

, ,

public virtual int Count { get; set ;}

NHiberante. #, CatDTO ( , Cat entity - Count)