i have searched the web and couldnt find a satisfying answer.
I am attempting to write a QueryOver/CreateCriteria query to get a field from 1 table after joining 3 tables.
The SQL query itself is simple, however i wasnt able to convert that into the above format.
The query:
select LOC_ID from
VISITOR_VISIT vv
join VISIT v on vv.VISIT_ID = v.VISIT_ID
join VISITOR_LAY_ENTRY_POINTS vlep on vlep.VLEP_ID = v.VEP_ID
where vv.VIS_ID = PARAMETER
// Join aliases for ease of getting access to all parts of the query
VisitorVisit visitorVisitAlias = null;
Visit visitAlias = null;
VisitorLayEntryPoints = visitorLayEntryPointsAlias = null;
IList<int> locationIds =
session.QueryOver<VisitorVisit>()
.JoinAlias(() => visitorVisitAlias.VisitId, () => visitAlias)
.JoinAlias(() => visitAlias.VepId, () => visitorLayEntryPointsAlias)
// Depends on where your LocId is
.Select(() => visitorVisit.LocId)
// I assumed your LocId is an int, switch to string if it's a string
.List<int>();
Of course all of this only works if you have the right associations set up in your Mapping configuration (XML or Fluent NHibernate) which is where you should define the two join conditions.
LOC_IDis from. Also you may want to consider reading about projections if you require just one column to be returned. nhibernate.info/doc/nhibernate-reference/…