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. ?

Possible duplicate of NHibernate QueryOver to join unrelated entities — Roman Artiukhin
– Roman Artiukhin, Commented Nov 1, 2018 at 19:54
In this example there is no available option to use session directly. the session is unknown here. Only think available is spesific QueryOver<Student,Student> instance. — Ozan ERTÜRK
– Ozan ERTÜRK, Commented Nov 1, 2018 at 20:21

Roman Artiukhin · Accepted Answer · 2018-11-02 07:47:20Z

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();

edited Nov 2, 2018 at 7:47

answered Nov 1, 2018 at 21:26

Roman Artiukhin

2,3871 gold badge12 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Ozan ERTÜRK Over a year ago

Realy thanks, but I can not figure it out how can i access 'JoinEntityAlias' method. I couldn't find it.

Roman Artiukhin Over a year ago

It''s available since NHibernate 5.1

Ozan ERTÜRK Over a year ago

It seems that I am using Nhibernate 4.0.0.0 withal I can not update my nhibernateVersion. And your answer is correct. Thank you.

Collectives™ on Stack Overflow

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

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related