Performing a query inside NHibernate entities

Question

I have a repository of PinballMachines, which returns a hydrated PinballMachine entity. It has a private property that is a list of games played on that machine.

A pinball machine can possibly have millions of games recorded against it. From a PinballMachine, I want to get high scorers for display, this is the top 10 game players.

public class PinballMachine
{
    private IList<Game> _games = new List<Game>();  

    public virtual int ID { get; protected set; }

    public virtual IEnumerable<Game> GetTop10Games()
    {
        return _games
            .AsQueryable()
            .OrderByDescending(g => g.Score)
            .Take(10)
            .ToList();
    }
}

public class Game
{
    public virtual Guid ID { get; protected set; }
    public virtual string Name { get; set; }
    public virtual int Score { get; set; }
    public virtual decimal AmountPaid { get; set; }
}

The PinballMachine's _games property is mapped as a Bag.

Bag<Game>("_games", m =>
{
    m.Key(k => k.Column("PinballMachineID"));
    m.Access(Accessor.Field);
    m.Cascade(Cascade.All);
}, r => r.OneToMany()); 

The following code works behaves properly, however, NHibernate performs a very naive predicate on the games table, and performing the sort and filter in-memory.

-- SLOW! 1,000,000 records

SELECT ...
FROM Games
WHERE PinballMachineID = 123

This is very suboptimal as the database is transmitting millions of records when all I need is 10.

Ideally, I want NHibernate to generate a query that looks like the following:

-- FAST! 10 records

SELECT TOP 10 ...
FROM Games
WHERE PinballMachineID = 123
ORDER BY Score DESC

Is it possible to configure my mapping so I can perform additional queries (on the database) on hydrated objects.

I am aware that I can use the NHibernate session to perform a linq query, but I want this logic to part of my entity instead.

Answer 1

It appears that as of NHibernate 5 the accepted answer "Unfortunately, NHibernate doesn't support that." is no longer correct:

Starting with NHibernate 5.0, queries can also be created from an entity collection, with the standard Linq extension AsQueryable available from System.Linq namespace.

IList<Cat> whiteKittens =
    cat.Kittens.AsQueryable()
        .Where(k => k.Color == "white")
        .ToList();

https://nhibernate.info/doc/nhibernate-reference/querylinq.html

Can't speak definitively about the rest of the answer and comments in 2019 vs. 2014, regarding the practicability of Persistence Ignorance with NHibernate ORM, though I'm trying really hard to make it work as much as possible...we'll see.

Answer 2

Unfortunately, NHibernate doesn't support that.

When you created the mapping for PinballMachine, you defined that one-to-many relationship on the ID column which fetches (lazily or eagerly) all the matching Game entities.

One thing that I would suggest is that the GetTop10Games looks like it should belong in a repository class, instead being a member of the entity. That's one of the reasons behind using a repository pattern - it encapsulates all the data access logic, and in turn even allows you to write specific performant queries when you really need them, every once in a while. That's (unfortunately or not) the problem with most ORM frameworks; you never know when a certain LINQ provider will perform poorly, or even fail to translate into SQL at all, so you want to keep your options open.

I would certainly make this method a member of IGameRepository or IPinballMachineRepository, and implement it something like:

public IList<Games> GetTopGamesForMachine(PinballMachine machine, int maxItems)
{
     return Session
         .Query<Games>()
         .Where(g => g.PinballMachine == machine)
         .OrderByDescending(g => g.Score)
         .Take(maxItems)
         .ToList();
}