问题描述
我正在使用NHibernate 3.2.1.以下查询
I am using NHibernate 3.2.1. The following query
return session.Query<TmTranslation>()
.Where(x => x.TranslationUnit.Document.Job == job)
.OrderBy(x => x.Id)
.ToList();
产生此SQL:
select tmtranslation.id,
tmtranslation.text,
tmtranslation.fk_id_translation_unit
from "TRANSLATION" tmtranslation
inner join "TRANSLATION_UNIT" tmunit
on tmtranslation.fk_id_translation_unit = tmunit.id
inner join "TRANSLATION_UNIT" tmunit2
on tmtranslation.fk_id_translation_unit = tmunit2.id
inner join "DOCUMENT" tmdocument
on tmunit2.fk_id_document = tmdocument.id
where tmdocument.fk_id_job = 174
order by tmtranslation.id asc
我的映射:
public class TmTranslationMap : ClassMap<TmTranslation>
{
public TmTranslationMap()
{
Table("\"TRANSLATION\"");
LazyLoad();
Id(x => x.Id, "id").GeneratedBy.HiLo("hilo", "hilo_translation", "200");
Map(x => x.Text).Column("text");
References<TmTranslationUnit>(x => x.TranslationUnit, "fk_id_translation_unit").Cascade.None();
DynamicUpdate();
}
}
public class TmTranslationUnitMap: ClassMap<TmTranslationUnit>
{
public TmTranslationUnitMap()
{
Table("\"TRANSLATION_UNIT\"");
LazyLoad();
Id(x => x.Id, "id").GeneratedBy.HiLo("hilo", "hilo_translation_unit", "200");
HasMany(x => x.Translations).Inverse().KeyColumn("fk_id_translation_unit").Cascade.None();
References<TmDocument>(x => x.Document, "fk_id_document").Not.Nullable().Cascade.None();
}
}
public class TmDocumentMap : ClassMap<TmDocument>
{
public TmDocumentMap()
{
Table("\"DOCUMENT\"");
LazyLoad();
Id(x => x.Id, "id").GeneratedBy.HiLo("hilo", "hilo_document", "50");
References<TmJob>(x => x.Job, "fk_id_job").Not.Nullable();
HasMany(x => x.TranslationUnits).Inverse().KeyColumn("fk_id_document").Cascade.SaveUpdate();
}
}
如您所见,JOIN之一是无用的,只会使查询的运行速度变慢.有什么办法可以使查询不使用Linq产生不必要的JOIN?
As you can see, one of the JOINs is useless and only makes the query run slower. Is there any way I can make the query not produce unnecessary JOINs using Linq?
谢谢.
推荐答案
似乎LINQ to NHibernate提供程序在从目标表到源表的关联导航中遇到麻烦,并且每次遇到这样的关联时都会生成一个单独的联接:表示翻译-> TranslationUnit,一个表示TranslationUnit到文档.
It seems that the LINQ to NHibernate provider has trouble navigating associations from the target to the source table and generates a separate join each time it encounters such an association: one for Translation -> TranslationUnit and one for TranslationUnit to Document.
诀窍是帮助提供者在另一个方向上导航:文档-> TranlationUnit->这样的翻译:
The trick is help the provider navigate in the other direction: Document -> TranlationUnit -> Translation like this:
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();
生成的SQL查询为:
SELECT translatio2_.Id as Id1_, translatio2_.Text as Text1_, translatio2_.TmTranslationUnitId as TmTransl3_1_
FROM [Document] AS document0_ INNER JOIN
TmTranslationUnit AS translatio1_ ON document0_.Id = translatio1_.DocumentId INNER JOIN
TmTranslation AS translatio2_ ON translatio1_.Id = translatio2_.TmTranslationUnitId
WHERE (document0_.JobId = @p0)
ORDER BY translatio2_.Id
这篇关于Linq to NHibernate产生不必要的JOIN的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!