NHibernate Projection Help

Question

I had a problem creating a projection for my nhibernate detachedcriteria object.

I have a Spa class that is associated with a table address.

The address has a City field, which is a string.

public class Spa : IAggregateRoot { [BelongsTo("AddressID", Cascade = CascadeEnum.All)] public Address Address { get; set; } } 

My ultimate goal is to get a separate list of city names.

If I could get all the resorts with different cities, I would be happy too.

All my attempts were in vain and did not find useful messages.

So far I have tried:

 DetachedCriteria query = DetachedCriteria.For<Spa>() .CreateAlias("Address", "A") query.SetProjection( Projections.Distinct(Projections.ProjectionList() .Add(Projections.Alias(Projections.Property("Address"), "A")))); var Spas = ActiveRecordMediator<Spa>.FindAll(query); 

I know this is wrong, just try to find something to start with.

Any help would be greatly appreciated. Any simple projection tutorials that don't seem to find anything right there would also be appreciated.

I also tried, but got a throw error looking at it:

 DetachedCriteria query = DetachedCriteria.For<Spa>() .CreateAlias("Address", "A") .SetProjection(Projections.Distinct(Projections.Property("A.City"))); 

Answer 1

It seems to me that your question has two parts.

1. What should my DetachedCriteria look like?

If you are not performing any other aggregates, GROUP BY should provide the same results as DISTINCT . This is the query I would use:

 var query = DetachedCriteria.For<Spa>() .CreateAlias("Address", "A") .SetProjection(Projections.GroupProperty("A.City")); 

2. How to execute it using Castle ActiveRecord?

I have never used ActiveRecord, but based on method signatures I would expect something like this:

 var cities = ActiveRecordMediator<string>.FindAll(query); 

If you have access to an NHibernate session, you can also execute it as follows:

 var cities = query.GetExecutableCriteria(session).List<string>();