Closed
Description
We use the LINQ Provider with the FetchMany extension to reduce the roundtrips to the database and the amount of data. Unfortunately it is not possible to filter the fetched association like FetchMany(x => x.MyAssociation.Where(...)) , so we tried to use the Filter in the Mapping files. This seems to work, but the problem is, that for every out filtered element, there is a select statement in the log file. Is it really necessary to create a extra select there? We use the Version 5.2.1.
This is the created select statement:
select
asset0_.pdoid as pdoid1_0_0_,
dokument2_.pdoid as pdoid1_0_1_,
asset0_.IsDeleted as isdeleted2_0_0_,
dokument2_.IsDeleted as isdeleted2_0_1_,
dokumente1_.AssetId as assetid1_4_0__,
dokumente1_.DokumentId as dokumentid2_4_0__
from
Asset asset0_
left outer join
asset_to_dokument dokumente1_
on asset0_.pdoid=dokumente1_.AssetId
left outer join
Dokument dokument2_
on dokumente1_.DokumentId=dokument2_.pdoid
and dokument2_.IsDeleted = @p0
where
asset0_.IsDeleted = @p0;
@p0 = False [Type: Boolean (0:0:0)]
And for every deleted "Dokument" (for example 1000 elements) there is a statement like this:
SELECT
dokument0_.pdoid as pdoid1_0_0_,
dokument0_.IsDeleted as isdeleted2_0_0_
FROM
Dokument dokument0_
WHERE
dokument0_.pdoid=@p0;
@p0 = 32783 [Type: Int64 (0:0:0)]
This is my model:
public class Base
{
public virtual long Key { get; set; }
public virtual bool IsDeleted { get; set; }
}
public class Asset : Base
{
public virtual ISet<Dokument> Dokumente { get; set; }
}
public class Dokument : Base
{
public virtual ISet<Asset> Assets { get; set; }
}
This is my mapping:
<class name="NHibernate.DomainModel.Base, NHibernate.DomainModel" abstract="true" dynamic-update="true" lazy="true">
<id name="Key" column="pdoid" unsaved-value="0" >
<generator class="hilo" />
</id>
<property name="IsDeleted" column="IsDeleted" />
<filter name="deletedFilter" condition="IsDeleted = :deletedParam"/>
</class>
<union-subclass name="NHibernate.DomainModel.Asset, NHibernate.DomainModel" table="Asset" lazy="true" extends="NHibernate.DomainModel.Base">
<set name="Dokumente" table="asset_to_dokument" inverse="true" lazy="true">
<key column="AssetId"/>
<many-to-many class="NHibernate.DomainModel.Dokument, NHibernate.DomainModel" column="DokumentId">
<filter name="deletedFilter" condition="IsDeleted = :deletedParam"/>
</many-to-many>
</set>
</union-subclass>
<union-subclass name="NHibernate.DomainModel.Dokument, NHibernate.DomainModel" table="Dokument" lazy="true" extends="NHibernate.DomainModel.Base">
<set name="Assets" table="asset_to_dokument" inverse="false" lazy="true">
<key column="DokumentId"/>
<many-to-many class="NHibernate.DomainModel.Asset, NHibernate.DomainModel" column="AssetId"/>
</set>
</union-subclass>
<filter-def name="deletedFilter">
<filter-param name="deletedParam" type="bool"/>
</filter-def>
This is my LINQ Statement:
using (ISession s = OpenSession())
using (ITransaction t = s.BeginTransaction())
{
s.EnableFilter("deletedFilter").SetParameter("deletedParam", false);
var assets = s.Query<Asset>()
.FetchMany(x => x.Dokumente)
.ToList();
}