Invalid SQL is generated for string Enum used in conditional LINQ

[bahusoid]

It's an old issue we discussed in #2499 (comment) and related to #707

Test cases for WhereTests.cs:

//Fails for all dialects 
[Test]
public void CanUseStringEnumInConditional()
{
	using var log = new SqlLogSpy();
	var query = db.Users
			            .Where(user => (user.Enum1 == EnumStoredAsString.Small
				                   ? EnumStoredAsString.Small
				                   : EnumStoredAsString.Large) == user.Enum1 )
			.Select(x => x.Enum1);

	Assert.That(query.Count(), Is.GreaterThan(0));
}

//Fails for SQLite 
[Test]
public void CanUseStringEnumInConditional2()
{
	using var log = new SqlLogSpy();
	var query = db.Users
			            .Where(user => (user.Enum1 == EnumStoredAsString.Small
				                   ? user.Enum1
				                   : EnumStoredAsString.Large) == user.Enum1 )
			.Select(x => x.Enum1);

	Assert.That(query.Count(), Is.GreaterThan(0));
}

Example of invalid SQL for CanUseStringEnumInConditional2 test on SQLite:

    select
        cast(count(user0_.Enum1) as INTEGER) as col_0_0_ 
    from
        Users user0_ 
    where
        cast(case 
            when user0_.Enum1=@p0 then user0_.Enum1 
            else @p1 
        end as INTEGER)=user0_.Enum1; -- cast to INTEGER is invalid as user0_.Enum1 is a string column

The problem goes away by removing the following transparent cast and returning case directly :

nhibernate-core/src/NHibernate/Linq/Visitors/HqlGeneratorExpressionVisitor.cs

Line 633 in 8e22b70

: _hqlTreeBuilder.TransparentCast(@case, expression.Type);

It's not a suggested fix - just a place to look at.