How to formulate pagination of items by players in NHibernate?

Question

SQL

Good day to all, comrades! Faced such a problem. There is a table of players, there is a table of items. Each player can have any number of items. You need to display all items for a given number of players. I've compiled a SQL query, but I can't overtake it into a NHibernate query.
SQL query:

select * from 
(select * from player_info order by login ASC limit 100 offset 0)  as plr
left join items itm
   on plr.id = itm.user_id;

NHibernate query that doesn't work:

var getBatchOfPlayers = QueryOver.Of<PlayerInfoEntity>()
    .Where(entity => entity.IsSimulation == player.IsSimulation && entity.Role.IsIn(roles))
    .OrderBy(entity => entity.Login).Asc
    .Select(entity => entity.Login)
    .Skip(startOffset).Take(limit);

res = session.QueryOver<ItemsEntity>()
    .WithSubquery.WhereProperty(entity => entity.Player.Login).In(getBatchOfPlayers)
    .Cacheable().CacheMode(CacheMode.Normal)
    .List().GroupBy(x => x.Player.Login)
    .Select(ConvertSpecial)
    .ToList();

Stackoverflow has already climbed all over and did not find what you need. I would be grateful for any hints on a solution.

Answer 1

In general, we will leave it for posterity, maybe someday it will help someone. After tweaking the query a bit, we get the following result:

var getBatchOfPlayers = QueryOver.Of<PlayerInfoEntity>()
    .Where(entity => entity.IsSimulation == player.IsSimulation && entity.Role.IsIn(roles))
    .OrderBy(entity => entity.Login).Asc
    .Select(entity => entity.Id) // Исправили Login на Id
    .Skip(startOffset).Take(limit);

res = session.QueryOver<ItemsEntity>()
    .WithSubquery.WhereProperty(entity => entity.Player.Id).In(getBatchOfPlayers) // Исправили Login на Id
    .Cacheable().CacheMode(CacheMode.Normal)
    .List().GroupBy(x => x.Player.Login)
    .Select(ConvertSpecial)
    .ToList();