NH-3778 - Crash when performing a Linq query on a one-to-one mapped reference

[nhibernate-bot]

Craig Fowler created an issue — 17th April 2015, 13:35:19:

NHibernate fails to add the correct params to a SQL parameterised query when performing a Linq query upon an entity type which is mapped one-to-one. This leads to a crash from the corresponding ADO driver when NHibernate attempts to execute the query. A full reproduction solution is attached but in short, here's the problem:

var person = session.Load<Person>(1);

var results = session.Query<Employee>()
  .Where(x => x.Person == person)
  .ToList();

That query - where the "Person" and "Employee" classes are mapped with a one-to-one relationship - crashes with the following exception:

[ERROR] FATAL UNHANDLED EXCEPTION: NHibernate.Exceptions.GenericADOException: could not execute query
[ select employee0*.Id as Id1_0_, person1_.Id as Id0_1_, employee0_.StartDate as StartDate1_0_, person1_.Name as Name0_1_ from Employee employee0_ left outer join Person person1_ on employee0_.Id=person1_.Id where employee0*.Id=@p0 ]
  Name:p1 - Value:NHibernateBug.Person
[SQL: select employee0*.Id as Id1_0_, person1_.Id as Id0_1_, employee0_.StartDate as StartDate1_0_, person1_.Name as Name0_1_ from Employee employee0_ left outer join Person person1_ on employee0_.Id=person1_.Id where employee0*.Id=@p0] ---> Mono.Data.Sqlite.SqliteException: SQLite error
Insufficient parameters supplied to the command
  at Mono.Data.Sqlite.SqliteStatement.BindParameter (Int32 index, Mono.Data.Sqlite.SqliteParameter param) <0x00000> in <filename unknown>:0 
  at Mono.Data.Sqlite.SqliteStatement.BindParameters () <0x00000> in <filename unknown>:0 
  at Mono.Data.Sqlite.SqliteCommand.BuildNextCommand () <0x00000> in <filename unknown>:0 
  --- End of inner exception stack trace ---
  at NHibernate.Loader.Loader.DoList (ISessionImplementor session, NHibernate.Engine.QueryParameters queryParameters, IResultTransformer forcedResultTransformer) <0x00000> in <filename unknown>:0 
  at NHibernate.Loader.Loader.DoList (ISessionImplementor session, NHibernate.Engine.QueryParameters queryParameters) <0x00000> in <filename unknown>:0 
  at NHibernate.Loader.Loader.ListIgnoreQueryCache (ISessionImplementor session, NHibernate.Engine.QueryParameters queryParameters) <0x00000> in <filename unknown>:0 
  at NHibernate.Loader.Loader.List (ISessionImplementor session, NHibernate.Engine.QueryParameters queryParameters, ISet`1 querySpaces, NHibernate.Type.IType<] resultTypes) [0x00000> in <filename unknown>:0 
  at NHibernate.Loader.Hql.QueryLoader.List (ISessionImplementor session, NHibernate.Engine.QueryParameters queryParameters) <0x00000> in <filename unknown>:0 
  at NHibernate.Hql.Ast.ANTLR.QueryTranslatorImpl.List (ISessionImplementor session, NHibernate.Engine.QueryParameters queryParameters) <0x00000> in <filename unknown>:0 
  at NHibernate.Engine.Query.HQLQueryPlan.PerformList (NHibernate.Engine.QueryParameters queryParameters, ISessionImplementor session, IList results) <0x00000> in <filename unknown>:0 
  at NHibernate.Impl.SessionImpl.List (IQueryExpression queryExpression, NHibernate.Engine.QueryParameters queryParameters, IList results) <0x00000> in <filename unknown>:0

The reproduction case attached uses a SQLite database, but I have also been able to reproduce it using an MS-SQL database.

Note that I wrote the reproduction case project using Mono, and thus it depends upon Mono.Data.Sqlite at present. If you wish to execute the reproduction case on Windows/.NET, remove the reference to Mono.Data.Sqlite and download/install Sqlite from nuget.

To try the reproduction case (after making any changes for Sqlite), compile and execute the solution (there is only one project). The ReproductionCase class contains the logic which triggers this crash. The EntryPoint class just sets up and configures the session factory and db instance (including creating/exporting the schema).

Thanks

---

Craig Fowler added a comment — 17th April 2015, 14:06:25:

Oops, I forgot to mention that this issue also affects NH 3.3.3 (which is how we initially found it).

I am unsure if 3.x is still being supported with bugfixes but it would be nice if any fix were also backported as we are yet to make the upgrade to 4.0.

---

Victor Govorov added a comment — 1st June 2016, 19:50:12:

Person class doesn't have an equality operator implementation. In your case (in LinqToObject scenario) is better to compare objects via Objects.Equals method

var results = session.Query<Employee>()
  .Where(x => Equals(x.Person, person))
  .ToList();

But Person.Equals method implementation may be quite complex and may not be converted to sql query. So in NH case the following code is more preferred and it produces the correct sql query

var results = session.Query<Employee>()
  .Where(x => x.Person.Id == person.Id)
  .ToList();

As my mind this is not a bug and issue may be closed.

---

Craig Fowler added a comment — 1st June 2016, 20:12:27:

I understand that. However, in a many-to-one mapping scenario you can write equality operations just like that which are converted to SQL correctly without crashing. There is no need in that instance either to write an equality method implementation or operator overload. NH will correctly interpret it as a SQL equality comparison between the PK/FK values as applicable. NH only fails like this when the entities are mapped one-to-one.

If it is intentional that this does not work for one-to-one mappings and it cannot be implemented, surely a NotSupportedException would be preferable to producing and attempting to execute invalid SQL?