流利NHibernate的：左外部连接...和查询 [英] Fluent NHibernate: LEFT OUTER JOIN ... AND Query

问题描述

我有使用QueryOver的下一个Fluent NHibernate查询：

  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）
  

下一个查询：

pre $ SELECT * FROM任务left外连接Tasks.SubjectId上的主题= Subjects.IdAsignatura
左外连接SubjectsId = Languages.SubjectId
WHERE Tasks.UserId = xxx
和（Languages.LanguageId = yyy或Languages.LanguageId = null）


我想避免使用LEFT OUTER JOIN ... AND这样的最后一行：

  SELECT * FROM任务left外连接上的主题Tasks.SubjectId = Subjects.IdAsignatura 
左外连接上的语言Subjects.SubjectId = Languages.SubjectId AND Languages.LanguageId = yyy 
 WHERE Tasks.UserId = xxx 
  

有没有办法执行LEFT OUTER JOIN。和Fluent NHibernate查询？

解决方案

有一个CreateAlias重载：

  // /< param name =path> Lambda表达式返回关联路径< / param> 
 ///< param name =alias> Lambda表达式返回别名参考< /参数> 
 ///< param name =joinType>连接类型< / param> 
 ///< param name =withClause> SQL on子句的附加条件< / param> 
 ///<返回> 
 ///条件实例
 ///< / returns> 
 IQueryOver< TRoot，TSubType> JoinAlias< U>（表达式< Func>路径
，表达式< Func>别名，JoinType joinType 
，ICriterion withClause）; 
  

这意味着我们可以将WHRE的限制传递给ON子句

  ///而不是这个
 .JoinAlias（（）=> subject.Localizations，（）=> localization，JoinType.LeftOuterJoin） $（）=> localization.Language.Id == languageId 
 || localization.Language.Id == null）; 
 
 ///我们可以使用：
 .JoinAlias（（）=> subject.Localizations，（）=> localization，JoinType.LeftOuterJoin 
 // 4th使用AND运算符
，Restrictions.Where（（）=> localization.Language.Id == languageId）
）
  pre> 
 
 
  withClause 可以注入到SELECT的ON部分，但总是使用AND运算符。所以它只能应用更多的限制，而不是基本的映射关系。  
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. 

                        
这篇关于流利NHibernate的：左外部连接...和查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！