NHibernate - Help with ICriteria

Question

I'm trying to convert the following SQL query to an ICriteria in NHibernate.

SELECT DISTINCT m.typeId, t.typeName
FROM Models m, Types t
WHERE m.qualifier=? AND m.typeId IS NOT NULL AND m.typeId = t.typeId

These are both mapped in NHibernate in classes called Models and ModelType. The ICriteria.List should return a list of type ModelType.

Thanks

Answer 1

There are good news and bad news. You can create a criteria that will return a list of ModelTypes. However, they will not be session managed. The reason is, that a distinct query can only return a projection and projections are always unmanaged.

The query below will generate a similar query to the above as it does an inner join between the two entities and returns a distinct set based on those 2 columns. The result transformer should be set to generate some type that can be set via properties. You can probably return a list of ModelTypes, but just know that they won't be managed by the session.

Session.CreateCriteria<Model>()
   .CreateAlias("Type", "t")
   .Add(Restrictions.Eq("Qualifier", myQualifier)
   .SetProjection(Projections.Distinct(
       Projections.ProjectionList()
          .Add(Projections.Alias(Projections.Property("t.Id"), "Id"))
          .Add(Projections.Alias(Projections.Property("t.TypeName"), "TypeName"))))
   .SetResultTransformer(Transformers.AliasToBean<ModelType>())