CODEWITHSUNDEEP
c#nhibernate
Tomer Something
Tomer Something

Reputation: 780

NHibernate join multiple tables

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

Upvotes: 3

Views: 3323

Answers (1)

Andy Kong
Andy Kong

Reputation: 300

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

  1. Many-To-One (Object Reference): http://nhibernate.info/doc/nhibernate-reference/mapping.html#mapping-declaration-manytoone
  2. Many-To-Many or One-To-Many (Collection Reference): http://nhibernate.info/doc/nhibernate-reference/collections.html#collections-ofvalues

Upvotes: 2

Related Questions