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?
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.
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments