LINQ Query with .Where(f => names.Where(d => d != null).Contains(f.Name)) throws InvalidCastException starting version 5.3.2

[haefele]

Hello, I noticed another regression in the newer NHibernate versions.

We have quite a lot of places where some query-parameter list is filtered inside the .Where Expression like this:

var dave = session.Query<Person>()
                  .Where(f => names.Where(d => d != null).Contains(f.Name))
                  .FirstOrDefault();

Focus on the names.Where(d => d != null) part.

This used to work without any issues up until NHibernate version 5.2.7, but started returning NULL with version 5.3.0, and now throws a InvalidCastException starting from version 5.3.2 up to the current version 5.3.9

Full Exception with StackTrace

NHibernate.Exceptions.GenericADOException: could not execute query
[ select person0_.Id as id1_0_, person0_.Name as name2_0_ from dbo.[Person] person0_ where person0_.Name in (?) ORDER BY CURRENT_TIMESTAMP OFFSET 0 ROWS FETCH FIRST 1 ROWS ONLY ]
  Name:p1 - Value:System.Collections.Generic.List`1[System.String]
[SQL: select person0_.Id as id1_0_, person0_.Name as name2_0_ from dbo.[Person] person0_ where person0_.Name in (?) ORDER BY CURRENT_TIMESTAMP OFFSET 0 ROWS FETCH FIRST 1 ROWS ONLY] ---> System.InvalidCastException: Das Objekt des Typs "System.Collections.Generic.List`1[System.String]" kann nicht in Typ "System.String" umgewandelt werden.
   bei NHibernate.Type.AbstractStringType.Set(DbCommand cmd, Object value, Int32 index, ISessionImplementor session)
   bei NHibernate.Type.NullableType.NullSafeSet(DbCommand st, Object value, Int32 index, ISessionImplementor session)
   bei NHibernate.Param.NamedParameterSpecification.Bind(DbCommand command, IList`1 multiSqlQueryParametersList, Int32 singleSqlParametersOffset, IList`1 sqlQueryParametersList, QueryParameters queryParameters, ISessionImplementor session)
   bei NHibernate.Param.NamedParameterSpecification.Bind(DbCommand command, IList`1 sqlQueryParametersList, QueryParameters queryParameters, ISessionImplementor session)
   bei NHibernate.SqlCommand.SqlCommandImpl.Bind(DbCommand command, ISessionImplementor session)
   bei NHibernate.Loader.Loader.PrepareQueryCommand(QueryParameters queryParameters, Boolean scroll, ISessionImplementor session)
   bei NHibernate.Loader.Loader.DoQuery(ISessionImplementor session, QueryParameters queryParameters, Boolean returnProxies, IResultTransformer forcedResultTransformer, QueryCacheResultBuilder queryCacheResultBuilder)
   bei NHibernate.Loader.Loader.DoQueryAndInitializeNonLazyCollections(ISessionImplementor session, QueryParameters queryParameters, Boolean returnProxies, IResultTransformer forcedResultTransformer, QueryCacheResultBuilder queryCacheResultBuilder)
   bei NHibernate.Loader.Loader.DoList(ISessionImplementor session, QueryParameters queryParameters, IResultTransformer forcedResultTransformer, QueryCacheResultBuilder queryCacheResultBuilder)
   --- Ende der internen Ausnahmestapelüberwachung ---
   bei NHibernate.Loader.Loader.DoList(ISessionImplementor session, QueryParameters queryParameters, IResultTransformer forcedResultTransformer, QueryCacheResultBuilder queryCacheResultBuilder)
   bei NHibernate.Loader.Loader.ListIgnoreQueryCache(ISessionImplementor session, QueryParameters queryParameters)
   bei NHibernate.Loader.Loader.List(ISessionImplementor session, QueryParameters queryParameters, ISet`1 querySpaces)
   bei NHibernate.Loader.Hql.QueryLoader.List(ISessionImplementor session, QueryParameters queryParameters)
   bei NHibernate.Hql.Ast.ANTLR.QueryTranslatorImpl.List(ISessionImplementor session, QueryParameters queryParameters)
   bei NHibernate.Engine.Query.HQLQueryPlan.PerformList(QueryParameters queryParameters, ISessionImplementor session, IList results)
   bei NHibernate.Impl.SessionImpl.List(IQueryExpression queryExpression, QueryParameters queryParameters, IList results, Object filterConnection)
   bei NHibernate.Impl.SessionImpl.List(IQueryExpression queryExpression, QueryParameters queryParameters, IList results)
   bei NHibernate.Impl.AbstractSessionImpl.List(IQueryExpression queryExpression, QueryParameters parameters)
   bei NHibernate.Impl.AbstractQueryImpl2.List()
   bei NHibernate.Linq.DefaultQueryProvider.ExecuteQuery(NhLinqExpression nhLinqExpression, IQuery query, NhLinqExpression nhQuery)
   bei NHibernate.Linq.DefaultQueryProvider.ExecuteQuery(NhLinqExpression nhLinqExpression, IQuery query)
   bei NHibernate.Linq.DefaultQueryProvider.Execute(Expression expression)
   bei NHibernate.Linq.DefaultQueryProvider.Execute[TResult](Expression expression)
   bei System.Linq.Queryable.FirstOrDefault[TSource](IQueryable`1 source)
   bei NHibernateQueryTest.Program.Main(String[] args) in D:\Test Projects\NHibernateQueryTest\Program.cs:Zeile 62.

And here a little test case, that reproduces the issue

using FluentNHibernate.Cfg;
using FluentNHibernate.Cfg.Db;
using FluentNHibernate.Mapping;
using NHibernate.Tool.hbm2ddl;
using System.Collections.Generic;
using System.Diagnostics;
using System.Linq;

namespace NHibernateQueryTest
{
    class Program
    {
        static void Main(string[] args)
        {
            var factory = Fluently.Configure()
                .Database(MsSqlConfiguration.MsSql2012
                    .ConnectionString(f => f.Server("CS-UL-HAEFELED\\SQL2019").Database("Pets").Username("centron").Password("1"))
                    .DefaultSchema("dbo")
                    .ShowSql().FormatSql())
                .Mappings(f => f.FluentMappings.AddFromAssemblyOf<PersonMaps>())
                .ExposeConfiguration(f => new SchemaExport(f).Execute(true, true, false))
                .BuildSessionFactory();

            //Arrange
            using (var session = factory.OpenSession())
            using (var transaction = session.BeginTransaction())
            {
                var dave = new Person
                {
                    Name = "Dave",
                };
                session.Save(dave);

                transaction.Commit();
            }


            //Act
            using (var session = factory.OpenSession())
            {
                //Works
                var names = new List<string> { "Dave", "", null };
                var dave = session.Query<Person>()
                    .Where(f => names.Contains(f.Name))
                    .FirstOrDefault();

                //Works too
                var namesFiltered = names.Where(f => f != null).ToList();
                dave = session.Query<Person>()
                    .Where(f => namesFiltered.Contains(f.Name))
                    .FirstOrDefault();

                //Works as well
                var namesEnumerable = names.Where(f => f != null);
                dave = session.Query<Person>()
                    .Where(f => namesEnumerable.Contains(f.Name))
                    .FirstOrDefault();

                //Used to work in version 5.2.7 without any issues (and probably earlier versions too, but didn't test)
                //Returns null in version 5.3.0
                //Throws InvalidCastException starting version 5.3.2 (A instance of type List<string> can't be casted to System.String)
                dave = session.Query<Person>()
                    .Where(f => names.Where(d => d != null).Contains(f.Name))
                    .FirstOrDefault();

                //Assert
                Debug.Assert(dave != null);
            }
        }
    }

    public class Person
    {
        public virtual int Id { get; set; }
        public virtual string Name { get; set; }
    }

    public class PersonMaps : ClassMap<Person>
    {
        public PersonMaps()
        {
            this.Id(f => f.Id);
            this.Map(f => f.Name).Not.Nullable().Length(255);
        }
    }
}

[bahusoid]

.Where(f => names.Where(d => d != null).Contains(f.Name))

Are you sure that this Where part is actually executed in 5.2. According to #2276 I expect Where part to be ignored in 5.2. Did you actually check that NULL is not supplied in generated SQL as parameter inside IN statement in 5.2? (IN statement doesn't match NULL values so it gives proper results for your query anyway)

Can be reproduced by adding the following test to ParameterTests:

[Test]
public void UsingListWithWhereParameter()
{
	var ids = new [] {2, 1};
	AssertTotalParameters(
		db.Orders.Where(o => ids.Where(i => i == 1).Contains(o.OrderId)),
		1);
}

[haefele]

You are absolutely right, the names.Where(d => d != null) part was not evaluated at all. We used to use NHibernate version 5.1.3 before our recent upgrade, and using .ShowSql() with that version outputs this query:

select
    person0_.Id as id1_0_,
    person0_.Name as name2_0_
from
    dbo.[Person] person0_
where
    person0_.Name in (
        @p0 , @p1 , @p2
    )
ORDER BY
    CURRENT_TIMESTAMP OFFSET 0 ROWS FETCH FIRST 1 ROWS ONLY;
@p0 = 'Dave' [Type: String (4000:0:0)],
@p1 = '' [Type: String (4000:0:0)],
@p2 = NULL [Type: String (4000:0:0)]   <---------------- NULL is not filtered out

I guess this changes things. In the old version it just didn't work, in the new version an exception is thrown. Which seems like a reasonable improvement to me.

Thank you again for your assistance 😄

[haefele]

Reopening issue as it seems like you're already working on a fix.