CODEWITHSUNDEEP
c#nhibernatefluent-nhibernate
Xavier Egea
Xavier Egea

Reputation: 4763

Fluent NHibernate: LEFT OUTER JOIN ... AND Query

I have the next Fluent NHibernate query using QueryOver:

IQueryOver<Task> query = session
    .QueryOver<Task>()
    .JoinAlias(x => x.Subject, () => subject, JoinType.LeftOuterJoin)
    .JoinAlias(() => subject.Localizations, () => localization, JoinType.LeftOuterJoin)
    .Where(() => localization.Language.Id == languageId || localization.Language.Id == null);
    .Where(x => x.UserId == userId)

This performs the next query:

SELECT * FROM Tasks left outer join Subjects on Tasks.SubjectId = Subjects.IdAsignatura 
left outer join Languages on Subjects.SubjectId=Languages.SubjectId
WHERE Tasks.UserId = xxx 
and (Languages.LanguageId = yyy or Languages.LanguageId = is null)

I would like to avoid the last line using "LEFT OUTER JOIN ... AND" like this:

SELECT * FROM Tasks left outer join Subjects on Tasks.SubjectId = Subjects.IdAsignatura 
left outer join Languages on Subjects.SubjectId=Languages.SubjectId AND Languages.LanguageId = yyy
WHERE Tasks.UserId = xxx

Is there a way to perform "LEFT OUTER JOIN ... AND" Fluent NHibernate queries?

Upvotes: 1

Views: 3853

Answers (1)

Radim K&#246;hler
Radim K&#246;hler

Reputation: 123861

There is one overload of the CreateAlias:

/// <param name="path">Lambda expression returning association path</param>
/// <param name="alias">Lambda expression returning alias reference</param>
/// <param name="joinType">Type of join</param>
/// <param name="withClause">Additional criterion for the SQL on clause</param>
/// <returns>
/// criteria instance
/// </returns>
IQueryOver<TRoot, TSubType> JoinAlias<U>(Expression<Func<U>> path
     , Expression<Func<U>> alias, JoinType joinType
     , ICriterion withClause);

That means, that we can pass the Restriction from WHRE into ON clause

/// instead of this
.JoinAlias(() => subject.Localizations, () => localization, JoinType.LeftOuterJoin)
.Where(() => localization.Language.Id == languageId 
          || localization.Language.Id == null);

/// We can use this:
.JoinAlias(() => subject.Localizations, () => localization, JoinType.LeftOuterJoin
            // the 4th param is appended to ON clause with AND operator
            , Restrictions.Where(() => localization.Language.Id == languageId)
)

The withClause could be injected into the ON part of our SELECT, but always with AND operator. So it can only apply more restrictions than the basic mapped relation.

Upvotes: 4

Related Questions