Unnecessary cast in sql with Linq are causing performance issues

[gokhanabatay]

Hi,
With NH 5.3.2 update Equals method is not working with linq throws "System.NotSupportedException : Boolean Equals(System.Object)" exception.
So we changed to == operator but if field is nullable short its now adding extra cast in sql, earlier it was not with Equals method as expected.

https://github.com/gokhanabatay/NHibernateBugTest/blob/master/NHibernateBugTest/CustomTypeSelectFailsTest.cs
Test Case:
NullableShortFieldIsNullConditionTest

select customer0_.GUID as guid1_1_, customer0_.NAME as name2_1_ from CUSTOMER customer0_ where exists (select addresses1_.GUID from ADDRESS addresses1_ where customer0_.GUID=addresses1_.CUSTOMER_GUID and cast(addresses1_.ADDRESS_TYPE as int4)=?)

v5.2 working as expected test case: NullableShortFieldIsNullConditionTestV5_2

[hazzik]

@gokhanabatay what's the difference to #2483?

[maca88]

Test case NullableShortFieldIsNullConditionTestV5_2 does not work with 5.2.7 nor with 5.1.7, the exception thrown:

System.NotSupportedException : Boolean Equals(System.Object)

Which is expected as bool.Equals(object) is not in the supported method list of EqualsGenerator class. I am not sure how it is working for you in 5.2. Can you provide the exact version of 5.2.x that you've used?

In test NullableShortFieldIsNullConditionTest the comparison is between int and short, so the cast is expected. The problem is that even if the parameter is converted to short:

short value = 2;
var query = session.Query<Customer>().Where(x => x.Addresses.Any(y => y.AddressType.Value == value));

the cast is still added, because short does not have its own equals operator so both sides are converted to int by the compiler producing the unwanted cast. I've made #2491, which solves the issue when both sides are short or types that do not have their own operator.

[gokhanabatay]

@maca88 sorry for missing Value property in test case it's working below code as desired with NHibernate 5.2.7 and NHibernate 5.3.2

y.AddressType.Value.Equals(2) not adding unnecessary cast in sql.
As you mentioned "cast" issue exists if we use == operator in short and long types.

UpperRange and LowerRange are long properties if we use <= or >= adding extra cast in query, I think both operator and methods needs to work same as Compare methods.

ISession.Query<BinBkmList>().Where(x => x.UpperRange.CompareTo(upperRange) == 1 && x.LowerRange.CompareTo(lowerRange) == -1).FirstOrDefault()

SQL:

select customer0_.GUID as guid1_1_, customer0_.NAME as name2_1_ from CUSTOMER customer0_ 
where exists (select addresses1_.GUID from ADDRESS addresses1_ 
where customer0_.GUID=addresses1_.CUSTOMER_GUID and addresses1_.ADDRESS_TYPE=?)

public void NullableShortFieldIsNullConditionTestV5_2(short mbrId)
{
    CurrentSession.MbrId = mbrId;
    //Sets Current Session
    using (ISession session = SessionProvider.ISessionFactory
                                .WithOptions()
                                .Interceptor(new ContextInterceptor())
                                .OpenSession())
    {
        using (ITransaction transaction = session.BeginTransaction())
        {
            var query = session.Query<Customer>().Where(x => x.Addresses.Any(y => y.AddressType.Value.Equals(2)));

            string querySqlString = GetGeneratedSql(query, session);

            Assert.IsTrue(!querySqlString.Contains("cast("));
        }
    }
}

I just dowgraded test project to 5.2.7 and below test cases are working but when we upgrade 5.3.2 these are getting below exception which will be fixed with #2488
Get_DateCustomType_NullableDateLetUsage_FailsWithEqualsOperator -> Npgsql.PostgresException : 42883: operator does not exist: integer = timestamp without time zone
Get_DateCustomType_NullableDateValueEqualsV2_IncorrectCast -> Npgsql.PostgresException : 42883: operator does not exist: integer = timestamp without time zone

If unnecessary cast is fixed #2491 then we can finally upgrade to 5.3.2.