NHibernate Join Query with only one instance of QueryOver and no mapping relation defined

Question

In my case, I only have a specific repository. Lets say 'StudentRepository'. This repository hides the ISession instance from me and only thing I have is the IQueryOver< Student,Student> instance. Consider below entities have a simplistic mapping with NHibernate.

class Student
{
    public int Id {get;set;}
    public string Number  {get;set;}
}

class Exam{
    public int Id {get;set;}
    public double Score {get;set;}
    public string StudentNumber {get;set;}
}

You are right, the basic way just add the real relation to Exam class like:

public Student Student {get; set;}

Unfortunately, that is not an option either

The problem: I need to query with some criteria like "Score>70" on Exam entity from studentRepository. How can I produce such a query with Nhibernate without knowing session and no relation defined on mapping. ?

Answer 1

So the main problem here as I see it: to make joins to unrelated entities with QueryOver it's required to have alias defined as variable for root query (your QueryOver for students). How to do such joins explained in NHibernate QueryOver to join unrelated entities

So if you can modify your repository class to allow to provide optional alias variable for this QueryOver it would be the best solution. Something like this (I assume you are using NHibernate 5.1 or higher) :

Student studentAlias = null;
var studentsQueryOver= yourRepository.GetQueryOver<Student>(studentAlias);

Exam examAlias = null;

var students = studentsQueryOver
.JoinEntityAlias(() => examAlias, () => examAlias.StudentNumber == studentAlias.Number)
.Where(s => examAlias.Score > 70)
.List();

If it's not an option you still can create joins to unrelated entities but you need to build them directly with underlying root Criteria. Something like this:

Exam examAlias = null;

studentsQueryOver.RootCriteria
.CreateEntityAlias(
        nameof(examAlias),
        Restrictions.EqProperty("examAlias.StudentNumber", studentsQueryOver.RootCriteria.Alias + ".Number"),
        JoinType.LeftOuterJoin,
        typeof(Exam).FullName);

var students = studentsQueryOver
.Where(s => examAlias.Score > 70)
.List();

And on NHibernate versions before 5.1 you can use subqueries:

var subQuery = QueryOver.Of<Exam>()
    .Where(e => e.Score > 70)
    .Select(e => e.StudentNumber);

subQuery.RootCriteria.Add(Restrictions.EqProperty("StudentNumber", studentsQueryOver.RootCriteria.Alias + ".Number"))

//Or if root query alias variable available simply
//subQuery.And(e => e.StudentNumber == studentAlias.Number)

var students = studentsQueryOver
    .WithSubquery.WhereExists(subQuery)
    .List();