使用 NHibernate 和 Oracle 时的强制转换

Question

比较 NHibernate 时的类型转换问题。 使用 NHibernate 和 Oracle 数据库使用 Telerik 动态自定义绑定时会出现此错误。

下面的类实现了 Telerik 自定义绑定，当与 NHibernate 和 Oracle 一起使用时，该类可以与实体框架和 MS SQL Server 2008 很好地配合使用数据库错误将 VARCHAR 强制转换为 NVARCHAR，该属性位于mapinga NHibernate NO 属性中，该属性指定 Oracle 数据库的类型。

public static class DynamicDataBinding
    {
        public static IQueryable<T> Binding<T>(this IQueryable<T> query, GridCommand cmd, out int total)
        {
            query = query.Filtering(cmd.FilterDescriptors);
            total = query.Count();
            return query.Sorting(cmd.GroupDescriptors, cmd.SortDescriptors).Paging(cmd.Page, cmd.PageSize);
        }

        private static IQueryable<T> Filtering<T>(this IQueryable<T> query, IList<IFilterDescriptor> filters)
        {
            return filters.Any() ? query.Where(ExpressionBuilder.Expression<T>(filters)) : query;
        }

        private static IQueryable<T> Paging<T>(this IQueryable<T> query, int currentPage, int pageSize)
        {
            return ((pageSize > 0 && currentPage > 0) ? query.Skip((currentPage - 1) * pageSize) : query).Take(pageSize);
        }

        private static IQueryable<T> Sorting<T>(this IQueryable<T> query, IEnumerable<GroupDescriptor> groups, IEnumerable<SortDescriptor> sorts)
        {
            query = groups.Any() ? groups.Reverse().Aggregate(query, (cur, group) => cur.AddSortExp(group.SortDirection, group.Member)) : query;
            return sorts.Any() ? sorts.Aggregate(query, (cur, sort) => cur.AddSortExp(sort.SortDirection, sort.Member)) : query;
        }

        private static IQueryable<T> AddSortExp<T>(this IQueryable<T> query, ListSortDirection sort, string member)
        {
            return (sort == ListSortDirection.Ascending) ? query.OrderBy(member) : query.OrderByDescending(member);
        }
    }

这是一个错误日志，Oracle 从中执行的 SQL！

12:04:40.125 [11] DEBUG NHibernate.Engine.Query.QueryPlanCache - located HQL query plan in cache (.Count[X4Data.Entity.IIndicatorsHistory](.Where[X4Data.Entity.IIndicatorsHistory](.Where[X4Data.Entity.IIndicatorsHistory](.Where[X4Data.Entity.IIndicatorsHistory](NHibernate.Linq.NhQueryable`1[X4Data.Entity.IIndicatorsHistory], Quote((x, ) => (Equal(x.DeviceId, p1))), ), Quote((x, ) => (Equal(x.IndicatorId, p2))), ), Quote((item, ) => (String.op_Equality(Coalesce(NotEqual(item, NULLp3) ? item.Value : NULLp4, EmptyList).ToLower(), p6))), ), ))
12:04:40.125 [11] DEBUG NHibernate.Engine.Query.HQLQueryPlan - find: .Count[X4Data.Entity.IIndicatorsHistory](.Where[X4Data.Entity.IIndicatorsHistory](.Where[X4Data.Entity.IIndicatorsHistory](.Where[X4Data.Entity.IIndicatorsHistory](NHibernate.Linq.NhQueryable`1[X4Data.Entity.IIndicatorsHistory], Quote((x, ) => (Equal(x.DeviceId, p1))), ), Quote((x, ) => (Equal(x.IndicatorId, p2))), ), Quote((item, ) => (String.op_Equality(Coalesce(NotEqual(item, NULLp3) ? item.Value : NULLp4, EmptyList).ToLower(), p6))), ), )
12:04:40.125 [11] DEBUG NHibernate.Engine.QueryParameters - named parameters: {'p1'='72', 'p2'='1', 'p4'=null, 'p5'='', 'p6'='poweroff'}
12:04:40.126 [11] DEBUG NHibernate.AdoNet.AbstractBatcher - Opened new IDbCommand, open IDbCommands: 1
12:04:40.126 [11] DEBUG NHibernate.AdoNet.AbstractBatcher - Building an IDbCommand object for the SqlString: select cast(count(*) as NUMBER(10,0)) as col_0_0_ from WEBMONITOR.V_INDICATORS_HISTORY indicators0_ where indicators0_.DEVICE_ID=? and indicators0_.INDICATOR_ID=? and lower(nvl(cast(case when indicators0_.ID is not null or indicators0_.INDICATOR_ID is not null then indicators0_.VALUE else ? end as NVARCHAR2(255)), ?))=?
12:04:40.126 [11] DEBUG NHibernate.Type.Int64Type - binding '72' to parameter: 0
12:04:40.126 [11] DEBUG NHibernate.Type.Int64Type - binding '1' to parameter: 1
12:04:40.126 [11] DEBUG NHibernate.Type.StringType - binding '' to parameter: 3
12:04:40.126 [11] DEBUG NHibernate.Type.StringType - binding 'poweroff' to parameter: 4
12:04:40.126 [11] INFO  NHibernate.Loader.Loader - select cast(count(*) as NUMBER(10,0)) as col_0_0_ from WEBMONITOR.V_INDICATORS_HISTORY indicators0_ where indicators0_.DEVICE_ID=:p0 and indicators0_.INDICATOR_ID=:p1 and lower(nvl(cast(case when indicators0_.ID is not null or indicators0_.INDICATOR_ID is not null then indicators0_.VALUE else :p2 end as NVARCHAR2(255)), :p3))=:p4
12:04:40.126 [11] DEBUG NHibernate.SQL - select cast(count(*) as NUMBER(10,0)) as col_0_0_ from WEBMONITOR.V_INDICATORS_HISTORY indicators0_ where indicators0_.DEVICE_ID=:p0 and indicators0_.INDICATOR_ID=:p1 and lower(nvl(cast(case when indicators0_.ID is not null or indicators0_.INDICATOR_ID is not null then indicators0_.VALUE else :p2 end as NVARCHAR2(255)), :p3))=:p4;:p0 = 72 [Type: Int64 (0)], :p1 = 1 [Type: Int64 (0)], :p2 = NULL [Type: String (0)], :p3 = '' [Type: String (0)], :p4 = 'poweroff' [Type: String (8)]
12:04:40.126 [11] DEBUG NHibernate.Connection.DriverConnectionProvider - Obtaining IDbConnection from Driver
12:04:40.139 [11] ERROR NHibernate.AdoNet.AbstractBatcher - Could not execute query: select cast(count(*) as NUMBER(10,0)) as col_0_0_ from WEBMONITOR.V_INDICATORS_HISTORY indicators0_ where indicators0_.DEVICE_ID=:p0 and indicators0_.INDICATOR_ID=:p1 and lower(nvl(cast(case when indicators0_.ID is not null or indicators0_.INDICATOR_ID is not null then indicators0_.VALUE else :p2 end as NVARCHAR2(255)), :p3))=:p4
System.Data.OracleClient.OracleException (0x80131938): ORA-12704: character set mismatch

   at System.Data.OracleClient.OracleConnection.CheckError(OciErrorHandle errorHandle, Int32 rc)
   at System.Data.OracleClient.OracleCommand.Execute(OciStatementHandle statementHandle, CommandBehavior behavior, Boolean needRowid, OciRowidDescriptor& rowidDescriptor, ArrayList& resultParameterOrdinals)
   at System.Data.OracleClient.OracleCommand.Execute(OciStatementHandle statementHandle, CommandBehavior behavior, ArrayList& resultParameterOrdinals)
   at System.Data.OracleClient.OracleCommand.ExecuteReader(CommandBehavior behavior)
   at System.Data.OracleClient.OracleCommand.ExecuteDbDataReader(CommandBehavior behavior)
   at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader()
   at NHibernate.AdoNet.AbstractBatcher.ExecuteReader(IDbCommand cmd)

该映射是我用于绑定的！

<?xml version="1.0" encoding="utf-8"?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" assembly="X4Data" namespace="X4Data.Entity" schema="WEBMONITOR">
  <class name="IndicatorsHistory" table="V_INDICATORS_HISTORY" lazy="true" >
    <composite-id>
      <key-property name="Id" column="ID" type="long"></key-property>
      <key-property name="IndicatorId" column="INDICATOR_ID" type="long"></key-property>
    </composite-id>

    <property name="Time" column="START_TIME" />
    <many-to-one class="CriticalState" lazy="false" name="CriticalState">
      <column name="CRITICAL_STATE_ID" />
    </many-to-one>
    <property name="DeviceId" column="DEVICE_ID" />
    <property name="Value" column="VALUE" />
    <property name="LocalizationKey" column="LOCALIZATION_KEY" />
    <property name="LocalizationValue" formula="(select GENERAL.RS_LOCALIZE(V_INDICATORS_HISTORY.LOCALIZATION_KEY, :CultureFilter.CultureId) FROM V_INDICATORS_HISTORY WHERE V_INDICATORS_HISTORY.INDICATOR_ID = INDICATOR_ID AND V_INDICATORS_HISTORY.ID = ID)" />
  </class>
</hibernate-mapping>

映射绑定演示文稿遵循格式 V_INDICATORS_HISTORY

SELECT
    uis.id,
    uis.indicator_id,
    ins.device_id,
    ins.start_time,
    uis.critical_state_id,
    uis.value,
    (SELECT localization_key
    FROM WEBMONITOR.indicator_value_codes ivc
    WHERE ivc.value = uis.value
    ) AS localization_key
FROM WEBMONITOR.integral_states ins,
    WEBMONITOR.unit_states unts,
    WEBMONITOR.unit_indicator_states uis
WHERE unts.integral_states_id = ins.id
AND uis.unit_state_id = unts.id

请帮忙，这非常紧急，谁应该知道如何解决 NHibernate 转换中的问题。

Answer 1

映射为 varchar 此处讨论 ：

<property name="SomeName" />
    <column sql-type="varchar" />
</property>