nhibernate queryover with complex join over non-related entities

Question

So I've spent the last few hours looking for an answer and I can't seem to find anything that makes sense.

public class Game
{
   public virtual Guid ID { get; set; }
   public virtual ResultStructure Structure { get; set; }
   public virtual List<Result> Results { get; set; }
}

public class Result
{
  public virtual Player Player { get; set; }
  public virtual int Position { get; set; }
}

public class ResultStructure
{
  public virtual Guid ID { get; set; }
  public virtual List<ResultOutcomes> Outcomes { get; set;}
}

public class ResultOutcomes
{
  public virtual int Position { get; set; }
  public virtual int Points { get; set; }
}

public class PlayerSummary
{
  public virtual Player Player { get; set; }
  public virtual int Points { get; set; }
}

What I'm trying to do is get a list of players and the points they've earned across a multitude of different games (there are multiple entities above game that contain lists of games). So the end result of the query would be List<PlayerSummary> The SQL I'm looking for would look something like this:

SELECT p.*, Sum(rs.points) FROM result r
  JOIN player p on r.playerid = p.id
  JOIN game g on r.gameid = g.id
  JOIN resultstructure rs on g.resultstructureid = rs.id
  JOIN resultoutcomes ro on rs.id = ro.resultstructureid AND ro.position = r.position

Note, I will also need to do some querying/summing against the structure entity which is why it's included.

I'm trying to do this with NHibernate, using the TypeSafe stuff, and my plan is for the application to be database agnostic, so I can't use direct SQL (currently it's using Postgres, but I may move to SQL server at some point).

I don't particularly want to use the "HQL" stuff where you use those magic strings, so I'm trying to use Linq or QueryOver/Query.

Can anyone point me in the right direction?

Answer 1

It seems that the above is possible in my situation as there is a relationship, it's just not direct.

You can use JoinAlias.

the basic difference is that using JoinAlias, you can join multiple tables to same base table, where as with JoinQueryOver it takes a linear progression through the tables joining each to the previous table only.

so the query looks like this.

Result resultAlias = null;
ResultOutcome outcomeAlias = null;
ResultStructure structureAlias = null;

var results = Session.QueryOver(() => resultAlias) // Assigns resultAlias so it can be used further in the query.
   .Inner.JoinQueryOver(x => x.Game) // returns a QueryOver Game so you can do a where on the game object, or join further up the chain.
   .Inner.JoinAlias(x => x.ResultStructure, () => structureAlias) // joins on the Structure table but returns the QueryOver for the Game, not the structure.
   .Inner.JoinAlias(() => structureAlias.Outcomes, () => outcomeAlias) // same again for the outcomes
   .Where(() => resultAlias.Position == outcomeAlias.Position)
   .Select(
        Projections.Group(() => resultAlias.Player),
        Projections.Sum(() => outcomeAlias.Points)
   );

That should give people the idea. The downside to this is that the restriction on "Position" doesn't happen on the Join, but rather in the Where clause. I'm happy to hear from anyone who has an option for doing that as this would force the database query planner down a specific route.

Still working on the transformations and the ordering, but that's got me a lot further.