c# - 错误映射数据类型 Oracle 和 C#

Question

我有一个带有 ID NUMBER(18) 的表，我有一个带有属性的类 public Int64 ID { get;放; } 从 C# 到 Oracle 映射 ID。

我的表格定义

但是当我像这样获取最大 ID 时出现错误:

1. 运行查询

   SELECT MAX(ID) ID FROM MYTABLE

   系统抛出错误:

   "Object of type 'System.Decimal' cannot be converted to type 'System.Int64'."

2. 但是当我运行这样的查询时:

   SELECT ID FROM(                SELECT ID FROM MYTABLE ORDER BY ID DESC              ) WHERE ROWNUM =1

   没问题。

我不知道为什么 (1) 抛出错误，为什么 (2) 完成？

数据类型 (1) 和 (2) 有什么区别？

Answer 1

可能的场景 1:

我猜你的表中有 NULL 值(所以你的两个查询不等价):

SELECT MAX(ID) ID FROM T_00_RQMM  -- aggregate func ignore NULLS

但是:

SELECT ID FROM(SELECT ID FROM T_00_RQMM ORDER BY ID DESC) WHERE ROWNUM = 1;  
-- NULL is the max value

DBFiddle Demo

无论如何你应该尝试映射:

using System.Numerics;
...
public BigInteger ID { get; set; }

---

编辑:

可能的场景 2:

可能是数据类型的问题(然后使用显式CAST):

SELECT CAST(MAX(ID) AS NUMBER(18,0)) ID FROM T_00_RQMM 

DBFiddle Demo 2

完整演示:

CREATE TABLE T_00_RQMM (ID NUMBER(18,0));
INSERT INTO T_00_RQMM VALUES(NULL);
INSERT INTO T_00_RQMM VALUES(100);

CREATE TABLE t1 AS SELECT MAX(ID) ID FROM T_00_RQMM;

CREATE TABLE t2 AS SELECT ID FROM(SELECT ID FROM T_00_RQMM ORDER BY ID DESC) 
                                  WHERE ROWNUM = 1;

CREATE TABLE t3 AS SELECT CAST(MAX(ID) AS NUMBER(18,0)) ID FROM T_00_RQMM;


SELECT TABLE_NAME, COLUMN_NAME, DATA_PRECISION, DATA_SCALE
FROM all_tab_cols
where table_name in ('T1', 'T2', 'T3')
ORDER BY Table_name;

输出:

+------------+-------------+----------------+------------+
| TABLE_NAME | COLUMN_NAME | DATA_PRECISION | DATA_SCALE |
+------------+-------------+----------------+------------+
| T1         | ID          | null           | null       |
| T2         | ID          | 18             | 0          |
| T3         | ID          | 18             | 0          |
+------------+-------------+----------------+------------+