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?

Publish

This is the exact situation where you should use HQL. "Magic strings" does not mean what you think it means. – Spelling

I want the type safe stuff really, I do understand the HQL ish, and I appreciated that it will probably do what I need, but I'd like to keep everything the same, and I'm using Query in most parts. – Publish

@DiegoMijelshon I've added my solution, could elaborate as to why I should use HQL over the solution I've come up with? – Publish

there are several problems with your approach. First, you are using QueryOver, not Query. The latter does not require explicit joins. Second, HQL will give you a much cleaner query (less code than your SQL), so "keep everything the same" is not a good reason. Third, HQL is type safe; you are confusing type safety with intellisense. – Spelling

The code I've used comes up with as clean of a query as I could have wrote in SQL (barr the issue with the position select being in the Where clause). I was under the impression that TypeSafe was to do with making sure that entity name changes are flagged at compile time, which, as far as I can tell, it doesn't in HQL. – Publish

I'm afraid you got the wrong impression in both accounts. Type safety has nothing to do with compile time, and your HQL should be way shorter than either SQL or QueryOver (no joins) – Spelling

Type safety I've obviously misunderstood, and I'll edit the question... I am very intrigued though as to how you get NHibernate can generate better SQL than handrolled, and how you could do the above example without generating joining the tables, could you provide a HQL example so I can compare the syntax it generates? no worries if not as I'm fairly happy with my solution – Publish

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.

Publish

Since it's all inner joins, it shouldn't matter where the extra join criteria is placed...right? – Mickey

well as they're not all joins to "Result", it does matter from a coding perspective. The JoinAlias maybe doesn't matter, you couldn't put the ResultStructure JoinAlias before JoinQueryOver as it would try to join against Result instead of Game. – Publish