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?