Issue with bidirectional ono-to-one associations to joined-subclass tables with WHERE condition in base class

[Liwoj]

Hi

I'm, using NH 5.2.7 on .NET 4.8

Base class:

/// <summary>
/// Base class for all ratings
/// </summary>
public abstract class InternalRatingBase : IRatingBase, IDeletableEntity, IJournalTarget
{
   public virtual int Id { get; set; }

   /// <summary>
   /// <inheritdoc cref="IDeletableEntity.IsDeleted"/>
   /// </summary>
   public virtual bool IsDeleted { get; set; }
}

Base class mapping:

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" assembly="Cra.VubScoringEngine" namespace="Cra.VubScoringEngine.Domain.Rating">
	<class name="InternalRatingBase" table="RatingBase" where="IsDeleted = 0" batch-size="20" polymorphism="explicit">
		<id name="Id" type="Int32" unsaved-value="0">
			<column name="Id" sql-type="int" not-null="true"/>
			<generator class="native" />
		</id>

		<property name="IsDeleted" type="Boolean">
			<column name="IsDeleted" sql-type="bit" not-null="true"/>
		</property>
	</class>
</hibernate-mapping>

Then I have few subclasses like this:

public class CompositeRatingFinalisation : InternalRatingBase
{
	public virtual string CreatedBy { get;  set; }

	public virtual bool IsExtension { get; set; }

        /// <summary>
	/// bidirectional ono-to-one association
	/// </summary>
	public virtual CompositeRating CompositeRating { get; set; }
}

Subclass mapping:

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" assembly="Cra.VubScoringEngine" namespace="Cra.VubScoringEngine.Entities">
	<joined-subclass name="CompositeRatingFinalisation" table="CompositeRatingFinalisation" batch-size="20" extends="Cra.VubScoringEngine.Domain.Rating.InternalRatingBase">
		<key column="Id"/>

		<!-- CompositeRatingFinalisation properties -->

                <property name="CreatedBy" type="String" update="false">
			<column name="CreatedBy" sql-type="nvarchar(50)" length="50" not-null="true"/>
		</property>

		<property name="IsExtension" type="Boolean" update="false">
			<column name="IsExtension" sql-type="bit" not-null="true"/>
		</property>

		<many-to-one name="CompositeRating" class="CompositeRating" update="false" foreign-key="FK_CompositeRatingFinalisation_CompositeRatingId_CompositeRating_Id" unique="true">
			<column name="CompositeRatingId" sql-type="int" not-null="true"/>
		</many-to-one>

<!-- leaving out properties for simplicity-->
		
	</joined-subclass>
</hibernate-mapping>

Note: Reality is bit more complex as in fact the CompositeRatingFinalisation subclass is not derived from InternalRatingBase directly but there are few middleman abstract base classes (none of them are mapped - all their properties are always mapped in leaf non-abstract child - for example CompositeRatingFinalisation here). I'm omitting this part to make my example more clear

Now take a look at the CompositeRatingFinalisation.CompositeRating association. This is bidirectional one-to-one association mapped like this:

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" assembly="Cra.VubScoringEngine" namespace="Cra.VubScoringEngine.Entities">
	<class name="CompositeRating" table="CompositeRating" batch-size="20" where="IsDeleted = 0">
		<id name="Id" type="Int32" unsaved-value="0">
			<column name="Id" sql-type="int" not-null="true"/>
			<generator class="native" />
		</id>

		<property name="CreatedBy" update="false">
			<column name="CreatedBy" length="50" sql-type="nvarchar(50)" not-null="true"/>
		</property>
	
		<property name="IsDeleted">
			<column name="IsDeleted" sql-type="bit" not-null="true"/>
		</property>

<!-- leaving out properties for simplicity-->

		<!-- Generates wrong SQL query for IsDeleted base class field
			- can be fixed by using fetch="select" but then batch fetching do not work either
		-->
		<one-to-one name="Finalisation" class="CompositeRatingFinalisation" property-ref="CompositeRating" fetch="join" />

<!--		<bag name="Finalisations" inverse="true" fetch="join">-->
<!--			<key column="CompositeRatingId"/>-->
<!--			<one-to-many class="CompositeRatingFinalisation"/>-->
<!--		</bag>-->

		<bag name="WorkflowLog" inverse="true" batch-size="10">
			<key column="CompositeRatingId"/>
			<one-to-many class="CompositeRatingWorkflowLog"/>
		</bag>
	</class>
</hibernate-mapping>

Now when I query the NH for CompositeRating entity, the wrong query is generated:

SELECT this_.Id as id1_54_1_, 
	this_.CreatedBy as createdby7_54_1_, 
	this_.IsDeleted as isdeleted15_54_1_, 
	compositer2_.Id as id1_31_0_, 
	compositer2_1_.IsDeleted as isdeleted2_31_0_, 
	compositer2_.CompositeRatingId as compositeratingid11_57_0_, 
	compositer2_.CreatedBy as createdby13_57_0_
FROM dbo.CompositeRating this_ 
left outer join dbo.CompositeRatingFinalisation compositer2_ 
	on this_.Id=compositer2_.CompositeRatingId and (compositer2_1_.IsDeleted = 0)  
left outer join dbo.RatingBase compositer2_1_ 
	on compositer2_.Id=compositer2_1_.Id 
WHERE ( this_.IsDeleted = 0) AND (this_.CisId = @p0 and not (this_.State = @p1)) ORDER BY this_.Id desc

As you can see the WHERE condition from the base InternalRatingBase class (and (compositer2_1_.IsDeleted = 0)) is included in the JOIN clause of the association fetch which of course does not work. It should be included in the WHERE part of the select.

If I change fetch="join" to fetch="select", it works but NH does not batch the association loading which leads to N+1 problem and is not optimal.

Only workaround I found until now is to use one-to-many instead (commented in mapping) which fixes the issue but is not optimal either as I must "simulate" the one-to-one nature of the association in the classes (for example public virtual CompositeRatingFinalisation Finalisation => Finalisations.FirstOrDefault();) - it is not pretty

Thx for ideas!

[bahusoid]

I'm, using NH 5.2.7 on .NET 4.8

Did you try with latest 5.4.2? Duplicate of #1209

It should be included in the WHERE part of the select.

Nope. It shouldn't.

[Liwoj]

Oops! My bad not founding that one. Thanks for pointing in the right direction!