NHibernate count another entity with sub query?

Question

I have two entities which are:

User
{
    UserGuid,
    Address,
    .
    .
    .
    EmailCount // This is not a column in the database, 
               // I just wanna get the count number from the UserEmail table
               // and map the value to this property
}

UserEmail
{
    UserGuid,
    Timestamp
}

The issue is how can I get the email count with a sub query in NHibernate?

By far I have this, but it does not work. Any idea?

User userEntity = null;

var subQuery = QueryOver.Of<UserEmail>()
    .Where(ue => ue.UserGuid == userEntity.UserGuid)
    .ToRowCountQuery();                       

return _session.StatefulSession.QueryOver(() => userEntity)
          .WithSubquery.WhereValue("EmailCount").Eq(subQuery)
          .List();

Radim Köhler · Accepted Answer · 2014-07-01 16:09:34Z

This would be the way how to use subquery to get an inlined count with a QueryOver syntax:

User userEntity = null;

var subQuery = QueryOver
    .Of<UserEmail>()
    .Where(ue => ue.UserGuid == userEntity.UserGuid)
    .ToRowCountQuery(); 

var list = _session.StatefulSession
    .QueryOver<User>(() => userEntity)
    .SelectList(projections => projections
        .Select(x => x.UserGuid)
             .WithAlias(() => userEntity.UserGuid)
        .Select(x => x.Address)
             .WithAlias(() => userEntity.Address)
        // any property to be selected
        ... 
        // INLINE COUNT
        // our subquery placed into play
        .SelectSubQuery(subQuery)
             // this will populate virtual/not mapped EmailCount
             .WithAlias(() => userEntity.EmailCount) 
    )
    .TransformUsing(Transformers.AliasToBean<User>())
    //.Take(10).Skip(100) // paging
    .List();

Collectives™ on Stack Overflow

NHibernate count another entity with sub query?

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related