Recently I wanted to retrieve data from a single field in one of my domain objects. This object had already been mapped using Castle ActiveRecord, so I didn’t really want to create another data access method to fetch this single field’s data. Here started my bungee jump down the rabbit hole to find sections of the answer on Hibernate, NHibernate and ActiveRecord sites.
The solution ends up looking rather similar to my previous solution on how to select distinct records using ActiveRecord, but has one or two key differences.
DetachedCriteria singleFieldCriteria = DetachedCriteria.For<Employee>()
.SetProjection(Projections.ProjectionList()
.Add(Projections.Distinct(Property.ForName("MonthOfBirth")), "MonthOfBirth"))
.SetResultTransformer(Transformers.AliasToBean<Employee>());
IList<Employee> singleFieldResults = Employee.FindAll(singleFieldCriteria);
return singleFieldResults.Select(employee => employee.MonthOfBirth);
In the above example I am selecting all distinct Months of Birth for the list of Employees in our database. I am only interest in fetching the month of birth field and not the rest of the employee’s fields.
To do this we create a distinct projection on the MonthOfBirth field.
Projections.Distinct(Property.ForName("MonthOfBirth"))
This time we specify a name for our projection.
Add(Projections.Distinct(Property.ForName("MonthOfBirth")), "MonthOfBirth"))
We also tell ActiveRecord/NHibernate that we want to transform our results back into our Employee object. This maps the named projection into the field with the name of the projection. (don’t ask me why the method you need is called AliasToBean – I smell Java...)
.SetResultTransformer(Transformers.AliasToBean<Employee>())
Finally we select out our results and use some Linq to get a list of the single field.