NHibernate 3 GA LINQ with new Fluent NHbernate binaries boolean conversion issue

I have been using the NHibernate betas with Fluent NHibernate. Every thing has been fine until I moved to the GA release with all binarie开发者_JS百科s coming from Fluent NHibernate http://fluentnhibernate.org/downloads

The problem is the Oracle database has char columns with a 'Y' or and 'N' for yes/no booleans. My model has bools.

public class ApplicationUser 
{
    public virtual string UserId { get; set; }
    public virtual string RoleId { get; set; }        
    public virtual string Prefix { get; set; }
    public virtual string FirstName { get; set; }
    public virtual string MiddleName { get; set; }
    public virtual string LastName { get; set; }
    public virtual string Suffix { get; set; }
    public virtual string Title { get; set; }
    public virtual string Company { get; set; }
    public virtual string UserName { get; set; }
    public virtual string RoleName { get; set; }
    public virtual string RoleGroup { get; set; }
    public virtual bool IsRoleActive { get; set; }
    public virtual bool IsUserActive { get; set; }

}

In my Fluent NH mappings I applied a CustomType to do the conversion. Everything worked perfectly.

public ApplicationUserMap()
{
    Schema("TEST");
    Table("V_APPLICATION_USER_ROLE");
    Id(x => x.UserId)
        .Column("USER_ID");
    Map(x => x.RoleId)
        .Column("ROLE_ID");
    Map(x => x.Prefix)               
        .Column("NAME_PRE");
    Map(x => x.FirstName)
        .Column("NAME_FIRST");
    Map(x => x.MiddleName)
        .Column("NAME_MI");
    Map(x => x.LastName)
        .Column("NAME_LAST");
    Map(x => x.Suffix)
        .Column("NAME_SUF");
    Map(x => x.Title)
        .Column("TITLE");
    Map(x => x.Company)
        .Column("COMPANY");
    Map(x => x.UserName)
        .Column("LOGIN_ID");
    Map(x => x.RoleName)
        .Column("ROLE_NAME");
    Map(x => x.RoleGroup)
        .Column("ROLE_GROUP");
    Map(x => x.IsRoleActive)
        .Column("IS_ROLE_ACTIVE").CustomType(typeof(BoolToYNType));
    Map(x => x.IsUserActive)
        .Column("IS_USER_ACTIVE").CustomType(typeof(BoolToYNType));

}

Here is the NH LINQ I use :

var user = (from u in this.Session.Query<ApplicationUser>()
            where u.UserName.ToUpper() == authenticationRequest.UserName.ToUpper().PadRight(12)
            && u.IsUserActive == true
            && u.IsRoleActive == true
            select new AuthenticatedUser
             {
              RoleId = u.RoleId,
              UserId = u.UserId,
              UserName = u.UserName
             SingleOrDefault();

This works fine and produces the following SQL

SELECT applicatio0_.ROLE_ID AS col_0_0_,
  applicatio0_.USER_ID      AS col_1_0_,
  applicatio0_.LOGIN_ID     AS col_2_0_
FROM TEST.V_APPLICATION_USER_ROLE applicatio0_
WHERE ((upper(applicatio0_.LOGIN_ID) IS NULL)
AND (:p0                             IS NULL)
OR upper(applicatio0_.LOGIN_ID)       =:p0)
AND
  CASE
    WHEN applicatio0_.IS_USER_ACTIVE='Y'
    THEN 1
    ELSE 0
  END=
  CASE
    WHEN :p1=1
    THEN 1
    ELSE 0
  END
AND
  CASE
    WHEN applicatio0_.IS_ROLE_ACTIVE='Y'
    THEN 1
    ELSE 0
  END=
  CASE
    WHEN :p2=1
    THEN 1
    ELSE 0
  END;
:p0 = 'XXXXX       ' [Type: String (0)],
:p1 = True [Type: Int32 (0)],
:p2 = True [Type: Int32 (0)]

However, when I apply the new NH and FNH binaries the following SQL is generated and I do not get the expected result.

SELECT applicatio0_.ROLE_ID AS col_0_0_,
  applicatio0_.USER_ID      AS col_1_0_,
  applicatio0_.LOGIN_ID     AS col_2_0_
FROM TEST.V_APPLICATION_USER_ROLE applicatio0_
WHERE upper(applicatio0_.LOGIN_ID)=:p0
AND
  CASE
    WHEN applicatio0_.IS_USER_ACTIVE='Y'
    THEN 'true'
    ELSE 'false'
  END=
  CASE
    WHEN :p1='true'
    THEN 'true'
    ELSE 'false'
  END
AND
  CASE
    WHEN applicatio0_.IS_ROLE_ACTIVE='Y'
    THEN 'true'
    ELSE 'false'
  END=
  CASE
    WHEN :p2='true'
    THEN 'true'
    ELSE 'false'
  END;
:p0 = 'XXXXX       ' [Type: String (0)],
:p1 = 'True' [Type: String (0)],
:p2 = 'True' [Type: String (0)]

Here is my converter, which always worked but I'll show it just for completeness

public class BoolToYNType : CharBooleanType
{
    /// <summary></summary>
    public BoolToYNType()
        : base(new AnsiStringFixedLengthSqlType(1))
    {
    }

    /// <summary></summary>
    protected override sealed string TrueString
    {
        get { return "Y"; }
    }

    /// <summary></summary>
    protected override sealed string FalseString
    {
        get { return "N"; }
    }

    /// <summary>
    /// 
    /// </summary>
    /// <param name="rs"></param>
    /// <param name="index"></param>
    /// <returns></returns>
    public override object Get(IDataReader rs, int index)
    {
        string code = Convert.ToString(rs[index]);
        if (code == null)
        {
            return false;
        }
        else
        {
            //return StringHelper.EqualsCaseInsensitive(code, TrueString);
            return code == TrueString ? true : false;
        }
    }

    /// <summary></summary>
    public override string Name
    {
        get { return "BoolToYN"; }
    }
}

I am not sure whether NH or FNH is doing this so I am posting it here. Is this a new feature or a bug?

Thanks Paul

OK, based on the query, the only reason I can see that it might fail is case sensitivity in the string comparison between 'True' and 'true'. Frankly, I don't get why the framework would switch from numerics for comparisons to strings.

EDIT: Sounds like this issue might have been fixed in most current code. See https://nhibernate.jira.com/browse/NH-2441

You don't need a custom type. NHibernate has YesNoType (NHibernateUtil.YesNo) out of the box, which does exactly what you want.