Extra Select for every "outfiltered" Element

[ValResnick]

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();
}

[bahusoid]

This seems to work

Are you sure that loaded asset.Dokumente doesn't contain deleted documents? As from query it seems
that asset_to_dokument records are still loaded for deleted documents. And then you see lazy loading requests for all deleted Dokument records

It seems to work properly bridge tables for many-to-many association need to be inner joined (asset_to_dokument and Dokument)

[ValResnick]

You are right, the deleted documents are still loaded into the object model.
Is this my fault, or it is a bug?
I can't change the structure of the "bridge table".

[bahusoid]

It's a bug. And my inner join suggestion is obviously wrong (as it will filter asset records too).

From what I see it's hql/Query related. QueryOver/Criteria case seems to be working correctly:

s.EnableFilter("deletedFilter").SetParameter("deletedParam", false);
var assets = s.QueryOver<Asset>()
  .Fetch(SelectMode.Fetch, x => x.Dokumente)
  .TransformUsing(Transformers.DistinctRootEntity)
  .List<Asset>();