在 NHibernate 中使用 Linq 时出现重复和不必要的连接

Question

基本上我在这个 linq-to-nhibernate-products-unnecessary 中遇到了 Linq 提供程序的相同问题-joins

List<Competitions> dtoCompetitions;
dtoCompetitions = (from compset in session.Query<FWBCompetitionSet>()
                    where compset.HeadLine == true 
                    && compset.A.B.CurrentSeason == true
                   select (new Competitions
                           {
                                       CompetitionSetID = compset.CompetitionSetID,
                                       Name = compset.Name,
                                       Description = compset.Description,
                                       Area = compset.Area,
                                       Type = compset.Type,
                                       CurrentSeason = compset.A.B.CurrentSeason,
                                       StartDate = compset.StartDate
                            }
                    )).ToList();

这会导致其生成的 SQL 中出现重复的连接

SELECT fwbcompeti0_.competitionsetid AS col_0_0_,
   fwbcompeti0_.name             AS col_1_0_,
   fwbcompeti0_.DESCRIPTION      AS col_2_0_,
   fwbcompeti0_.area             AS col_3_0_,
   fwbcompeti0_.TYPE             AS col_4_0_,
   fwbseason3_.currentseason     AS col_5_0_,
   fwbcompeti0_.startdate        AS col_6_0_
FROM   fwbcompetitionset fwbcompeti0_
       INNER JOIN A fwbcompeti1_
         ON fwbcompeti0_.competitionseasonid = fwbcompeti1_.competitionseasonid
       INNER JOIN A fwbcompeti2_
         ON fwbcompeti0_.competitionseasonid = fwbcompeti2_.competitionseasonid
       INNER JOIN B fwbseason3_
         ON fwbcompeti2_.seasonid = fwbseason3_.seasonid
WHERE  fwbcompeti0_.headline = @p0
       AND fwbseason3_.currentseason = @p1  

，请注意这些连接，它们完全重复，并且还会影响我的 SQL Server 的性能。

       INNER JOIN A fwbcompeti1_
         ON fwbcompeti0_.competitionseasonid = fwbcompeti1_.competitionseasonid
       INNER JOIN A fwbcompeti2_
         ON fwbcompeti0_.competitionseasonid = fwbcompeti2_.competitionseasonid

Update1

在NHibernate 3.2中，这个LiNQ bug仍然有效，而且我找不到简单合理的Linq解决方案。 所以我使用 QueryOver + JoinAlias + TransformUsing 完成这项工作，对我来说非常完美。

FWBCompetitionSet compset = null;
FWBCompetitionSeason compseason = null;
FWBSeason season = null;
IList<Competitions> dtoCompetitions;
dtoCompetitions = session.QueryOver<FWBCompetitionSet>(() => compset)
.JoinAlias(() => compset.FWBCompetitionSeason, () => compseason)
.JoinAlias(() => compseason.FWBSeason, () => season)
.Where(() => compset.HeadLine == true)  
.And(() => season.CurrentSeason == true)
.SelectList(
list => list
.Select(c => c.CompetitionSetID).WithAlias(() => compset.CompetitionSetID)
.Select(c => c.Name).WithAlias(() => compset.Name)
.Select(c => c.Description).WithAlias(() => compset.Description)
.Select(c => c.Area).WithAlias(() => compset.Area)
.Select(c => c.Type).WithAlias(() => compset.Type)
.Select(c => season.CurrentSeason).WithAlias(() => season.CurrentSeason)
.Select(c => c.StartDate).WithAlias(() => compset.StartDate)
)
.TransformUsing(Transformers.AliasToBean<Competitions>())
.List<Competitions>();

Answer 1

又一个编辑：

我想我终于知道发生了什么事。 LINQ to NHibernate 提供程序似乎无法将关联从目标表导航到源表，并且每次遇到此类关联时都会生成单独的联接。

由于您没有提供映射，因此我使用了 linq-to-nhibernate- 中的映射产生不必要的连接。该模型有一个文档，其中包含一个作业和许多翻译单元。每个TranslationUnit有许多翻译实体。

当您尝试根据作业查找翻译时，您将以相反的顺序遍历关联，并且 LINQ 提供程序会生成多个联接：一个用于翻译 -> 另一个联接。 TranslationUnit 和一个用于 TranslationUnit to Document 的。

此查询将生成冗余联接：

session.Query<TmTranslation>()
          .Where(x => x.TranslationUnit.Document.Job == job)
          .OrderBy(x => x.Id)
          .ToList();

如果将导航顺序反转为 Document ->翻译单元->翻译后，您会得到一个不会产生任何冗余连接的查询：

var items=(from doc in session.Query<Document>()
        from tu in doc.TranslationUnits
            from translation in tu.Translations
       where doc.Job ==job                        
       orderby translation.Id
       select translation).ToList();

鉴于这种怪异，QueryOver 似乎是一个更好的选择。

先前编辑：

我怀疑罪魁祸首是compset.ABCurrentSeason。第一个连接表 (fwbcompeti1_) 返回 A.B，而接下来的两个表（fwbcompeti2_ 和 fwbseason3_）用于返回 A.B。 LINQ to NHibernate 提供程序似乎没有猜测 A 没有在其他地方使用，并且无法将其从生成的语句中删除。

尝试通过将 select 中的 CurrentSeason = compset.ABCurrentSeason 替换为 CurrentSeason = true 来帮助优化器，因为您的 where 语句仅返回 CurrentSeason == true 的项目。

编辑：我的意思是像这样更改查询：

List<Competitions> dtoCompetitions;
dtoCompetitions = (from compset in session.Query<FWBCompetitionSet>()
                   where compset.HeadLine == true 
                   && compset.A.B.CurrentSeason == true
                   select (new Competitions
                       {
                                   CompetitionSetID = compset.CompetitionSetID,
                                   Name = compset.Name,
                                   Description = compset.Description,
                                   Area = compset.Area,
                                   Type = compset.Type,
                                   CurrentSeason = true,
                                   StartDate = compset.StartDate
                        }
                )).ToList();

我只需将值 compset.ABCurrentSeason 替换为 true