CODEWITHSUNDEEP
c#sqlnhibernatequeryover
TTT
TTT

Reputation: 183

NHibernate count another entity with sub query?

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();

Upvotes: 3

Views: 1305

Answers (1)

Radim K&#246;hler
Radim K&#246;hler

Reputation: 123871

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();

Upvotes: 7

Related Questions