How can I do this query with NHibernate

How can I do this query with NHibernate

select top 10 count(distinct classedition.createdby_id) as editions, class.id,
       class.name, class.createdon, class.createdby_id
from class 
inner join classedition on class.id = classedition.class_id
group by class.id, class.name, class.createdon, class.createdby_id
order by editions desc, class.createdon desc

I'm using NHibernate 3. I tried to do it with the new Linq provi开发者_运维知识库der without success. I don't care about the way of doing it as long as it produce the exact sql query above. I would prefer writing a strongly typed query, without magic string if possible.

I'm new to NHibernate so this question may be simple.

Here is a little more info

I use Fluent NHibernate with AutoMappings. The C# classes are very simple:

public  class Class
{
    public virtual int Id { get; set; }
    public virtual string Name { get; set; }
    public virtual DateTime CreatedOn { get; set; }
}

public class ClassEdition
{
    public virtual int Id { get; set; }
    public virtual Class Class { get; set; }
    public virtual User CreatedBy { get; set; }
}

public class User
{
    public virtual int Id { get; set; }
    public virtual string Name { get; set; }
}

---

I finally resolved the problem by using a View.

I don't want to be rude, but it seems like the NHibernate community is more inclined to argue on the way I ask a question then responding to the question itself. See the comments for Diego Mijelshon's answer. I received the same reproaches (about using unit tests) on nhusers (Google Groups): http://groups.google.com/group/nhusers/browse_thread/thread/4c74269aefb918fc

---

HQL queries are strongly typed and object oriented.

var results = session.CreateQuery(@"
              select count(distinct e.CreatedBy), c.Id,
                     c.Name, c.CreatedOn, c.CreatedBy
              from ClassEditions e
              join e.Class c
              group by c.Id, c.Name, c.CreatedOn, c.CreatedBy
              order by 1 desc, c.CreatedOn desc
              ")
              .SetMaxResults(10)
              .List();

Your C# code is a string too. NH also has a query compiler.

In fact, if you put the query in a mapping file, you can even get intellisense and real-time error checking by installing the HQL Language Service for Visual Studio.

And a simple unit test that does nothing more than build the SessionFactory will tell you if anything broke because of a change. Not to mention modern refactoring tools (like Resharper) are able to rename identifiers in strings, bindings, or any kind of files without a problem.