mardi 28 juin 2016

QueryOver - adding additional criteria to a Join

I'm quite new to NHibernate and QueryOver and I can't get NHibernate to generate the SQL I need.

I need to make a join and have an extra criteria on so I avoid getting to much data from the table I'm joining with.

The SQL I receive from QueryOver is:

SELECT * FROM adresse this_ 
left outer join r580_test.afvigelse remarkalia1_ on this_.id=remarkalia1_.adrid 
left outer join r580_test.afvigelseklagepunkter remarkcomp5_ on remarkalia1_.id=remarkcomp5_.afvigelseid 
left outer join r580_test.klagepunkter complainta2_ on remarkcomp5_.klagepunktid=complainta2_.id 
WHERE  this_.id = 16633 and remarkalia1_.dato between '2009-03-13 00:00:00' and '02-03-2012 16:34:35'

What I would like is this(the where date between has been moved to the end for the first left outer join):

SELECT * FROM adresse this_ 
left outer join r580_test.afvigelse remarkalia1_ on this_.id=remarkalia1_.adrid and remarkalia1_.dato between '2009-03-13 00:00:00' and '02-03-2012 16:34:35'
left outer join r580_test.afvigelseklagepunkter remarkcomp5_ on remarkalia1_.id=remarkcomp5_.afvigelseid 
left outer join r580_test.klagepunkter complainta2_ on remarkcomp5_.klagepunktid=complainta2_.id 
WHERE  this_.id = 16633

My QueryOver looks like this:

adr = session.QueryOver<Address>()
    .Where(x => x.Id == 16633)
    .JoinQueryOver<Remark>(y => y.Remarks).Where(y => y.Created > DateTime.Now.AddDays(-14))
    .JoinAlias(y => y.RemarkComplaint, () => complaintAlias, JoinType.LeftOuterJoin)
    .SingleOrDefault();

Anyone got an idea about how to fix this?

Aucun commentaire:

Enregistrer un commentaire