Transforming SQL query to NHibernate query

Question

I have these two tables

AppUser

PK: User_Id

UserPrinciples

User_ID (not the key in this table)

UserName

I need this query in NHibernate, using paging:

SELECT TOP(page_size)
FROM AppUser this_ left join
 (select UserPrinciples.*
 from  (select UserPrinciples.*,
         row_number() over (partition by UserName order by User_Id) as seqnum
        from UserPrinciples
        ) UserPrinciples
 where seqnum = 1) as b 

 on this_.User_Id=b.User_ID 

my relevant code right now is:

(AppUser represanted by IUser; UserPrinciples represanted by StoredCredentials)

public UserPagedQuery(ISessionProvider<ISession> sessionProvider)
{
    _sessionProvider = sessionProvider;
    _query = sessionProvider.Session.QueryOver<IUser>();

    var uc = _query.UnderlyingCriteria;
    var ap = uc.GetOrCreateAssociationPath("StoredCredentials");
    ap.AddOrder(Order.Asc("UserName"));
}

public IEnumerable<IUser> Execute()
{
    var pagedReceiving = _query.TransformUsing(Transformers.DistinctRootEntity).List<IUser>();
    return pagedReceiving;
}

but this code returns query something like that~:

SELECT TOP(page_size) FROM AppUser left outer join UserPrinciples 
on AppUser.User_Id=UserPrinciples.User_ID 
ORDER BY UserPrinciples.UserName asc

and this is not good enough, since i need only one record from each user_id, and with this query i can get more than one record, for some users, and this is the issue.

can anyone assist with that? not sure if any other details are needed

thanks and best regards

Answer 1

expressed as sql without the partition

SELECT TOP(page_size) up.*, au.* from
  (SELECT UserName, Min(User_Id) as User_Id FROM UserPrinciples GROUP BY UserName) as x
LEFT JOIN
  UserPrinciples up ON x.UserName = up.UserName AND x.User_Id = up.User_Id
LEFT JOIN
  AppUser au ON  au.User_Id = up.User_Id

which translated to linq should be something along the lines:

var y = from up1 in session.Query<Employment>()
        group up1 by up1.Name into g
        select new { Name = g.Key, UserId = g.Min(x => x.Company.Id) } into f
        join up2 in session.Query<Employment>() on new { f.Name, f.UserId } equals new { up2.Name, UserId = up2.Company.Id }
        select new SomeDto
        {
            ProfileName = up2.Name,
            UserId = up2.User.Id,
            Name = up2.User.Name,
        };

var results = y.ToList();