JoinAlias not working as expected after NHibernate update

[cjay99]

Hello,

we have encountered a problem with NHIbernate after updating form 5.1.1 to 5.2.5.
We are using JoinAlias and projection to gain extra performance at some points in the program.

I've created a test solution which is encapsulated from our program to show you the different behaviour between the two version (Attachment NHibernate_Test.zip, containing a VS Solution):
NHibernate_test.zip

Here's the Query which is causing the issue (Also in the test solution):

public static IList<WorkstepFeatureLiteRequestBean> PerformSelect(long? featureSetTemplateId, long? featureSetId)
{
	using (var session = _sessionFactory.OpenSession())
	{
		using (var transaction = session.BeginTransaction(IsolationLevel.ReadCommitted))
		{
			Workstep workstepAlias = null;
			Feature featureAlias = null;
			FeatureSet featureSetAlias = null;
			FeatureSetTemplate featureSetTemplateAlias = null;
			FeatureTemplate featureTemplateAlias = null;
			FeatureType featureTypeAlias = null;

			var selectedTypes = BaseData.FeatureTypes.Take(2).ToList();
			var query = session.QueryOver(() => workstepAlias);

			if (featureSetTemplateId.HasValue)
			{
				query = query
					.JoinAlias(x => x.FeatureSetTemplate, () => featureSetTemplateAlias)
					.And(() => featureSetTemplateAlias.Id == featureSetTemplateId.Value);
			}

			if (featureSetId.HasValue)
			{
				query = query
					.JoinAlias(x => x.FeatureSet, () => featureSetAlias)
					.And(() => featureSetAlias.Id == featureSetId);
			}
			
			if (!featureSetId.HasValue)
			{
				query = query.JoinAlias(x => x.FeatureSet, () => featureSetAlias);
			}
			
			if (!featureSetTemplateId.HasValue)
			{
				query = query.JoinAlias(x => x.FeatureSetTemplate, () => featureSetTemplateAlias);
			}
			
			WorkstepFeatureLiteRequestBean bean = null;
			var fullQuery = query
				.JoinQueryOver(x => x.FeatureSet.Features, () => featureAlias)
				.JoinQueryOver(x => x.FeatureTemplate, () => featureTemplateAlias)
				.JoinQueryOver(x => x.FeatureType, () => featureTypeAlias)
				.And(x => x.FeatureKey.IsIn(selectedTypes))
				.SelectList(x => x
					.Select(() => workstepAlias.Id).WithAlias(() => bean.WorkstepId)
					.Select(() => workstepAlias.Uuid).WithAlias(() => bean.WorkstepUuid)
					.Select(() => workstepAlias.Name).WithAlias(() => bean.WorkstepName)
					.Select(() => workstepAlias.JointIndex).WithAlias(() => bean.WorkstepJointIndex)
					.Select(() => featureSetTemplateAlias.Name).WithAlias(() => bean.FeatureSetTemplateName)
					.Select(() => featureSetAlias.Name).WithAlias(() => bean.FeatureSetName)
					.Select(() => featureAlias.Id).WithAlias(() => bean.FeatureId)
					.Select(() => featureAlias.Uuid).WithAlias(() => bean.FeatureUuid)
					.Select(() => featureTemplateAlias.Name).WithAlias(() => bean.FeatureName)
					.Select(() => featureTypeAlias.FeatureKey).WithAlias(() => bean.FeatureType)
				)
				.TransformUsing(Transformers.AliasToBean<WorkstepFeatureLiteRequestBean>());

			var filteredWorksteps = fullQuery.List<WorkstepFeatureLiteRequestBean>();
			transaction.Commit();
			return filteredWorksteps;
		}
	}
}

The SQL NHibernate generates looks like this in version 5.1.1 (which is fine and working):

SELECT this_.id                AS y0_,
       this_.uuid              AS y1_,
       this_.NAME              AS y2_,
       this_.jointindex        AS y3_,
       featureset2_.NAME       AS y4_,
       featureset1_.NAME       AS y5_,
       featureali3_.id         AS y6_,
       featureali3_.uuid       AS y7_,
       featuretem4_.NAME       AS y8_,
       featuretyp5_.featurekey AS y9_
FROM   nh_ja_workstep this_
       INNER JOIN nh_ja_featuresettemplate featureset2_
               ON this_.featuresettemplate_id = featureset2_.id
       INNER JOIN nh_ja_featureset featureset1_
               ON this_.featureset_id = featureset1_.id
       INNER JOIN nh_ja_feature featureali3_
               ON featureset1_.id = featureali3_.featureset_id
       INNER JOIN nh_ja_featuretemplate featuretem4_
               ON featureali3_.featuretemplate_id = featuretem4_.id
       INNER JOIN nh_ja_featuretype featuretyp5_
               ON featuretem4_.featuretype_id = featuretyp5_.id
WHERE  featuretyp5_.featurekey IN ( @p0, @p1 )  

But in version 5.2.5 the produced statement looks like this (which is not working):

SELECT this_.id                AS y0_,
       this_.uuid              AS y1_,
       this_.NAME              AS y2_,
       this_.jointindex        AS y3_,
       featureset2_.NAME       AS y4_,
       featureset1_.NAME       AS y5_,
       featureali3_.id         AS y6_,
       featureali3_.uuid       AS y7_,
       featuretem4_.NAME       AS y8_,
       featuretyp5_.featurekey AS y9_
FROM   nh_ja_workstep this_
       INNER JOIN nh_ja_featuresettemplate featureset2_
               ON this_.featuresettemplate_id = featureset2_.id
       INNER JOIN nh_ja_featureset featureset1_
               ON this_.featureset_id = featureset1_.id
WHERE  featuretyp5_.featurekey IN ( @p0, @p1 ) 

Serveral joins are missing in the produced statement and cannot be executed by the database (Tested on MSSQL 14 & Oracle 12c).

[cjay99]

Hello,

we have found a solution for our problem. We changed "x.FeatureSet.Features" in following LINQ expression:

.JoinQueryOver(x => x.FeatureSet.Features, () => featureAlias)

to the existing "featureSetAlias"

.JoinQueryOver(() => featureSetAlias.Features, () => featureAlias)

and it's working fine as in version 5.1.1. Is this behaviour intended?

Thanks in advance,
Christian

[bahusoid]

Looks like another regression after #287

[BhaaLseN]

This also seems to affect the Criteria API when using a projection with something that isn't from the main object (a few hopefully obvious calls omitted, like key mappings, table properties etc.):

// classes
public class ObjectWithComponentList
{
  public Guid Id { get; set; }
  public ICollection<TheComponent> ComponentListProperty { get; set; }
}
public class TheComponent
{
  public int IrrelevantMember { get; set; }
  public TheReferencedObject SomeReference { get; set; }
}
public class TheReferencedObject 
{
  public string MoreIrrelevantMembers { get; set; }
}

// mapping
mapper.Class<ObjectWithComponentList>(m =>
{
  m.Bag(b => b.ComponentListProperty,
        bm => /* omitted */,
        bm => bm.Component(comp =>
        {
          comp.Property(x => x.IrrelevantMember);
          comp.ManyToOne(x => x.SomeReference);
        }));
});

// query
var myEntries = session
  .CreateCriteria<ObjectWithComponentList>()
  .CreateAlias("ComponentListProperty", "entry")
  .CreateAlias("entry.SomeReference", "theReference")
  .Add(Restrictions.IdEq(objectWithListId)
  .SetProjection(Projections.ProjectionList()
    .Add(Projections.Entity<TheReferencedObject>("theReference"))
  .List<TheReferencedObject>()

Works just fine with 5.1.5; first broken release is 5.2.0.

Projections seem to work just fine though when not referring to aliases (ie. anything that needs to be joined due to calling CreateAlias; the generated query always correctly includes the m : n table; but not the ones on the n-side (in this case: TheReferencedObject)

[bahusoid]

@BhaaLseN Isn't it #2175? Your issue should already be fixed with f1a3b97 in 5.2.x branch.

[BhaaLseN]

Good point - we're using 5.2.5 from NuGet, which doesn't seem to have that fix yet.
I'll give that one a try.

Edit: Yep, that one fixes it, thanks for pointing me to it!

[robdalsanto]

Hi,

We have encountered what is likely the same issue between 5.1.7 and 5.2.0 (actually we went from 5.1.3 to 5.2.7, but I narrowed the break/regression down to coming from 5.2.0). In our case, a fairly complicated ICriteria was previously generating SQL with Oracle that looked like:

SELECT distinct this_.EXPENDITURE_ID as y0_ FROM EXPENDITURE this_ WHERE ((this_.CODE = :cp0 or this_.CODE = :cp1 or this_.CODE = :cp2) and this_.EXPENDITURE_ID in (SELECT f1_.EXPENDITURE_ID as y0_ FROM FORECAST_DISTRIBUTION this_0_ inner join FORECAST f1_ on this_0_.FORECAST_ID=f1_.FORECAST_ID inner join SCENARIO_FORECAST sf2_ on f1_.FORECAST_ID=sf2_.FORECAST_ID WHERE sf2_.SCENARIO_ID = :cp3 and f1_.EXPENDITURE_ID = this_.EXPENDITURE_ID and this_0_.DISTRIBUTION_ID in (SELECT this_0_0_.DISTRIBUTION_ID as y0_ FROM DISTRIBUTION_H this_0_0_ WHERE f1_.EXPENDITURE_SNAPSHOT_DATE >= this_0_0_.CREATED_DATE and f1_.EXPENDITURE_SNAPSHOT_DATE < this_0_0_.EXPIRY_DATE and this_0_0_.NAME like :cp4 escape '|')));

but with 5.2.0 the generated SQL looks like:

SELECT distinct this_.EXPENDITURE_ID as y0_ FROM EXPENDITURE this_ WHERE ((this_.CODE = :cp0 or this_.CODE = :cp1 or this_.CODE = :cp2) and this_.EXPENDITURE_ID in (SELECT f1_.EXPENDITURE_ID as y0_ FROM FORECAST_DISTRIBUTION this_0_ inner join FORECAST f1_ on this_0_.FORECAST_ID=f1_.FORECAST_ID WHERE sf2_.SCENARIO_ID = :cp3 and f1_.EXPENDITURE_ID = this_.EXPENDITURE_ID and this_0_.DISTRIBUTION_ID in (SELECT this_0_0_.DISTRIBUTION_ID as y0_ FROM DISTRIBUTION_H this_0_0_ WHERE f1_.EXPENDITURE_SNAPSHOT_DATE >= this_0_0_.CREATED_DATE and f1_.EXPENDITURE_SNAPSHOT_DATE < this_0_0_.EXPIRY_DATE and this_0_0_.NAME like :cp4 escape '|')));

Clearly the second won't execute with the missing inner join.
I know that in 5.2.* this query can probably be re-written without the sub-query (we needed to join to the same table more than once in some cases) following the change for NH-2016, but it'd be nice to tackle that separately from upgrading our use of the NHibernate NuGet package.

Is anyone looking at this or a related/duplicate issue ?
Thanks,
RobDS

[bahusoid]

@robdalsanto It's hard to say if it's the same issue without provided Criteria/QueryOver.

But if it's the same issue (which is criteria alias related) you can fix your query instead. Just make sure all aliased criterias are used via alias (see #2173 (comment))

[robdalsanto]

@bahusoid Thank you so much ! Sure enough it had to do with the path in the alias.
Under 5.1.x and earlier, CreateAlias using:

• ("fd.Forecast", "f") and
• ("fd.Forecast.ScenarioForecastsByForecast", "sf")

worked fine, but had to be changed under 5.2.x to:

• ("fd.Forecast", "f") and
• ("f.ScenarioForecastsByForecast", "sf")

We can now move ahead upgrading to 5.2.*, I think.

I don't know if this is an intentional change to make NHibernate less capable of resolving aliases, but it does seem a little unfortunate.