NHibernate 3 GA LINQ 与新 Fluent NHbernate 二进制文件布尔转换问题

Question

我一直在使用 NHibernate beta 和 Fluent NHibernate。一切都很好，直到我转到 GA 版本，所有二进制文件都来自 Fluent NHibernate http://fluentnhibernate.org/downloads< /a>

问题是 Oracle 数据库的 char 列带有“Y”或“N”表示是/否布尔值。我的模型有布尔值。

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; }

}

在我的 Fluent NH 映射中，我应用了 CustomType 来进行转换。一切都很顺利。

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));

}

这是我使用的 NH LINQ：

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();

这工作正常并生成以下 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)]

但是，当我应用新的 NH 和 FNH 二进制文件时，会生成以下 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)=: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)]

这是我的转换器，它一直有效，但为了完整性我将展示它，

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"; }
    }
}

我不确定 NH 或 FNH 是否正在这样做，所以我将其发布在这里。这是新功能还是错误？

谢谢 保罗

Answer 1

好的，根据查询，我看到它可能失败的唯一原因是“True”和“true”之间的字符串比较中的大小写敏感性。坦率地说，我不明白为什么框架会从数字转换为字符串比较。

编辑：听起来这个问题可能已在大多数当前代码中得到解决。请参阅https://nhibernate.jira.com/browse/NH-2441

Answer 2

您不需要自定义类型。 NHibernate 具有开箱即用的 YesNoType (NHibernateUtil.YesNo)，它完全可以满足您的需求。