tags:

views:

58

answers:

0

I have this SQL here that needs to be transfered to NHibernate Criteria API:

select nn.* from NetworkNumber nn 
inner join
(select number, max(Version) ver
from NetworkNumber 
group by number) b on b.number=nn.number
and b.ver=nn.version

If anyone has a good knowledge of it, I'd appreciate a lot. In the meantime - I'll keep trying and post back results (if any)..

Edit: So far I've got this:

var results = StagingSessionFactory.OpenSession()
            .CreateCriteria<NetworkNumber>()
            .SetProjection(
                Projections.ProjectionList()
                    .Add(Projections.Property("Number"), "Number")
                    .Add(Projections.Max("Version"), "Version")
                    .Add(Projections.GroupProperty("Number"), "Number")
                )
            .SetResultTransformer(Transformers.AliasToBean(typeof(NetworkNumber)))        
            .List<NetworkNumber>();

Which gives me the right Version/Number of the NetworkNumber, but I need the rest of the fields (Id, etc). Perhaps I am missing just a little something?