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:
Code:
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):
Code:
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:
Code:
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 know how I can get the SQL I need? Should I use something else instead of QueryOver or am I using it wrong?
Login
FAQ
Search