Search code examples
c#nhibernatenhibernate-criteria

Select distinct set of values from related table in NHibernate

Sorry I'm a newbie with NHibernate I hope I don't embarrass myself with this question.

I have 2 objects, a Log and a UserProfile, in Logs and UserProfiles tables respectively. Each Log object references one or none UserProfile objects.

I want an efficient way of collecting a distinct list of UserProfile.UserName strings from the Logs table, ordered alphabetically. With Linq this is fairly straightforward, but I want this done database-side. What would my 

public IEnumerable<string> GetUserNamesInLogs(){}

look like?

If I was writing this in SQL, I would do something like this:

select distinct
    u.UserName
from
    Logs as l
inner join
    UserProfiles as u
    on u.UserId = l.UserId;

I am looking for the equivalent in NHibernate. I think I don't want lazy loading for this (it seems like a performance drain) but I may not be clear on how the lazy loading works.

Solution

  • Utterly failing to get the durn thing to work the way I wanted it to with Criteria, I cheated:

    var result = _Session.CreateQuery("select distinct profile.UserName from Log as l inner join l.UserProfile as profile order by profile.UserName asc")
    .List<string>();

    This gave me the result I was looking for. Thanks to the people who helped though.